Hi there folks,
Here are a couple of links which might get you started.
The first thread contains links to forum pages that detail various macros for filtering to records. It also has the procedure to go straight to a certain record (which might be better for Doug).
http://www.oooforum.org/forum/viewtopic.phtml?t=76753
The second thread has a bit of a discussion about timing issues which can occur when you are trying to do these sorts of things. If you open a form using a macro you need to wait for it to finish loading before you try to copy stuff accross to it or else you might get errors. It also has a sub to straight away create a new record after the form has been loaded...
http://www.oooforum.org/forum/viewtopic.phtml?t=77391
As for copying things into a second table prior to the second form being opened, you could do this in your code using SQL before you launch the other form. Here is a macro I have written which demonstrates how to use the SELECT, INSERT and UPDATE syntax with your basic code. The macro's purpose is to update an "error log" table with the error name (and count) which is passed into the macro from other sub's. I have the error log table displayed in another form in a tablegrid and it has a button for doing the reload (for simplicity) (just need to set the "action" in the buttons properties to reload). If you needed the macro to do the reload of the other form, then you just need to get reference to the other form. I'll include that bit of code towards the end...
Code: Select all
Rem... Will record the last instance of an error in a table with the error count
Sub ErrorCall (Error_Name as String, Error_Count as integer)
'msgbox "ErrorCall"
dim oForm as object
dim Result
dim strSQL As String
dim oCreateStatement as object
dim oCreateStatement2 as object
dim aNum as integer
oForm = thisComponent.Drawpage.Forms(0)
Rem... This sub requires there to be at least one error in the table for things to work. This first bit just checks for that and adds in one if necessary
strSQL="SELECT ""Error_Name"", ""ID"", ""Error_Date_Stamp"", ""Error_Time_Stamp"", ""Error_Count"" FROM ""Error_Table"" ORDER BY ""ID"" DESC "
oCreateStatement = oForm.ActiveConnection.CreateStatement
Result = oCreateStatement.executeQuery(strSQL)
If Result.next() then Rem.. This actually does a result.next() so if there is no record yet the else bit inserts one.
Rem... There is already a first error in table so do nothing
Else
strSQL="INSERT INTO ""Error_Table"" ( ""Error_Name"" ) VALUES ( '" & "First Error" & "' )"
oCreateStatement3 = oForm.ActiveConnection.CreateStatement
oCreateStatement3.executeUpdate(strSQL)
End if
If Error_Name <> Result.GetString(1) then
strSQL="INSERT INTO ""Error_Table"" ( ""Error_Name"", ""Error_Count"" ) VALUES ( '" & Error_Name & "', '" & Error_Count & "' )"
oCreateStatement2 = oForm.ActiveConnection.CreateStatement
oCreateStatement2.executeUpdate(strSQL)
Else
Rem.. Get the current date from the hsql database engine
oStatement = oForm.ActiveConnection.CreateStatement
oStatement.EscapeProcessing = False
oResultSet = oStatement.executequery( "CALL CURRENT_DATE" )
'oResultSet = oStatement.executequery( "CALL CURRENT_TIMESTAMP" ) Rem.. If you were after the current timestamp then this way
oResultSet.next
If Result.GetString(1) = Error_Name AND CdateToIso(Result.GetString(3)) = CdateToIso(oResultSet.getString( 1 )) then
Error_Count = Error_Count + Result.GetInt(5)
strSQL = "UPDATE ""Error_Table"" SET ""Error_Name"" = '" & Error_Name & "', ""Error_Count"" = '" & Error_Count & "' WHERE ""ID"" = '" & Result.GetInt(2) & "' "
oStatement2 = oForm.ActiveConnection.CreateStatement
oStatement2.executeUpdate(strSQL)
Else
' msgbox "Error is different or Date is different"
End if
End if
Rem... To reload a particular form you would do something like this...
Rem... The second form is found by searching from thiscomponent for the form name
oForm2 = thisComponent.Parent.FormDocuments
if oForm2.HasByName("Form2Name") then
oForm2 = oForm2.getByName("Form2Name")
if not IsNull(oForm2.Component) then
oForm2 = oForm2.Component.DrawPage.Forms.getByName("MainForm")
oForm2.Reload()
End if
End if
End sub
If the syntax of the SQL statement looks a bit confusing then think of it this way. What is happening is you are creating a "string" out of various shorter strings which are joined with the & symbol. Also remember that to pass the " symbol in a string you need to double it to "". So something like this SQL string
Code: Select all
"UPDATE ""Error_Table"" SET ""Error_Name"" = '" & Error_Name & "', ""Error_Count"" = '" & Error_Count & "' WHERE ""ID"" = '" & Result.GetInt(2) & "' "
is actually made out of these strings which are strung together by the & symbol...
Code: Select all
"UPDATE ""Error_Table"" SET ""Error_Name"" = '"
Error_Name
"', ""Error_Count"" = '"
Error_Count
"' WHERE ""ID"" = '"
Result.GetInt(2)
"' "
Where Error_Name and Error_Count are string variables
Couple of quick tips for Vanja.... When you are getting your form, you might find this code better to use...
Code: Select all
oForm = thisComponent.Parent.FormDocuments
if oForm.HasByName("FormName") then
oForm = oForm.getByName("FormName")
if not IsNull(oForm.Component) then
oForm = oForm.Component.DrawPage.Forms.getByName("MainForm")
Rem... Either put your code here or after the second end if
End if
End if
Also, rather than finding things by their index number. Your code will read better if you find things by their name... for example rather than using...
You might use...
Code: Select all
index_surname=oform.getstring(oForm.FindColumn("SurnameColumn"))
And the syntax for updating that field this way would be
Code: Select all
oform.UpdateString( oForm.FindColumn("SurnameColumn"), "TheStringSurnameValue" )
Also a good thing to remember is that when you are using the above lines of code you are only reading or changing the form's RowSet. This means you are not reading or changing the data in the actual database table directly. The RowSet is like a copy of the row of data from the database table. You can manipulate the data in the rowset and it is stored back in the database table after you do a oForm.Updaterow() (i.e a save). Refreshing the rowset in your form occurs when ever you change to a new row in your form, or use the code oForm.Refreshrow()
For Doug... You have the choice of at least three ways to ensure your subsequently opened forms only relate to that particulat MRN. Information on all should be found if you follow the links in the threads I have linked to up the top.
1/ By using the form filters
2/ By going straight to the appropriate record by using a sub similar to the "findFirst_Own_data_source" sub which is in one of the threads. Also have a look at the doco by C.Benitez which I had linked to there.
3/ Rather than using a global variable you may prefer to create a "Dummy table" which is a table that holds only one row of data. You store your MRN value in that and use an "UPDATE" SQL statement when ever you need to change it. In the forms you create you could base the MainForm on this "Dummy table" and create a "SubForm" to the mainform which displays your medical record. If you set up the link/join between the Mainform and Subform then only the records with that MRN would be displayed. It would be similar to the "Dropdown Record Switcher" detailed in this thread...
http://www.oooforum.org/forum/viewtopic.phtml?t=72134
Cheers
Voo
