[SOLVED] Macro to run sql on open document event

[cwdavi1]

I've spent over a day searching for solutions and trying things I've found. I'm lost.

I have a simple search form using a filter table, an adaptation of an example I found here. My problem is that when the form is closed, the last parameter stored in the filter remains. Then on starting the form again the search is automatically run based on that old entry. I wrote a macro to clear the filter table at startup. It works fine when run as tools->macros->organize macros but not on opening the form. I assigned it on Form Properties-Events-When Loading. I later tried assigning it using tools-customize-Events and assigned it to the open document event.

Code: Select all

REM Clear old entry from Filter table at startup
Sub Clear_Filter_Table
   REM make sure you're connected to the database
   if IsNull(ThisComponent.CurrentController.ActiveConnection) then
       ThisComponent.CurrentController.connect
   endif
   Dim oStatement As Object
   oStatement = ThisComponent.CurrentController.ActiveConnection.createStatement()
   oStatement.execute("update ""Filter"" set ""F1"" = NULL where ""FilterID"" = 0;")
End Sub

When the form is opened I got the error object var not set on line 4 (if isNull...)

I saved the macro in the odt file but that didn't help. I then found other examples and variations for the connection that didn't work. Screwed my notes up or I'd list them.

I will greatly appreciate any help. I'm now quitting to watch the Florida Auburn game. I'm a Gator.

Thank you.

[cwdavi1]

Partially solved, searched some more and found:

Sub Clear_Filter_Table
Dim oStatement As Object
oStatement = ThisComponent.DrawPage.Forms.getByIndex(0).ActiveConnection.CreateStatement()
oStatement.execute("update ""Filter"" set ""F1"" = NULL where ""FilterID"" = 0;")
End Sub

Which works when triggered by an event on the form. Unfortunately assigning it to "when loading" event, it runs too late, the parameter field has already been populated by the Form Properties SQL command SELECT * FROM "Filter". I tried "when unloading" to run when the form was closed but that didn't trigger at all when I closed by clicking X on the top right of the window.

I tried the tools-customize' open document' event but that runs when the db file is opened, not the form. 'New Document' wasn't triggered at all when I opened either the file or the form. ' Load a sub' component triggered when I did almost anything, even just opening a table to look at it. It triggered when I opened the form but again too late, the parameter field had already been filled. 'Closed a subcomponent' triggered the macro too late, DrawPage was undefined.

I'm giving up for the night. Can anyone suggest a better way to do what I need: clearing the old search parameter from the filter table on opening the form, or on closing? I put this much effort into a macro because this is partially a learning exercise for me.

Thank you.

[gkick]

Hi,

Had a similar issue, my work around was to have a Clear button in the filter form which calls a couple of routines as below. Maybe you can adjust it to your specific needs. Mind you I am a newbie as well.

Code: Select all

Sub ClearSearch()
rem this part works, need to refresh the table then refresh the form
oConn = ThisDatabaseDocument.DataSource.getConnection("","") 
SQL = "UPDATE ""tblFindContacts"" SET ""SearchItem"" = Null "
oQuery = oConn.createStatement()
oQuery.executeQuery(SQL)
End Sub

Sub reloadAllTables
    Dim Forms : Forms = ThisComponent.DrawPage.Forms
    Dim i%
    For i = 0 To Forms.getCount()-1
        Forms.getByIndex(i).reload()
    Next
End Sub

cheers

[Villeroy]

Try without stupid Basic code.
You know how to create a filter form with an distinct criteria record.
Create another filter form as parent of your filter form. This form has its own ID and only blank values otherwise. When the form document loads, your filter form inherits the blank values or whatever you write as default values. The form does not need to have any visible controls.

Code: Select all

MasterFilter: SELECT * FROM "Filter" WHERE "ID"=1 [pre-set filter (blank) values]
|_>FilterForm:  SELECT * FROM "Filter" WHERE "ID"=0 [child form of MasterFilter with all relevant fields linked]
   |_>Data: The actual data table filtered by FilterForm

[F3K Total]

Hello Villeroy,
that sounds very interessting.
But i did not understand how it's done using two one-row filter forms.
Can you provide a sample file?
R

[Villeroy]

I used one of my example documents and added a new filter record #2 to table "Filter".

I made a copy of form document "Filter Form 1" named "Filter Form 1.1"

In that form I added the master form linked to record #2, dragged the filtering "MainForm" onto the master and linked the fields without adding any controls. This first try failed because the filtering (and now filtered) "MainForm" does not have any record.

I made that form insertable and insert only. Now it failed because it could not add any primary key.

I changed the ID type to auto-incrementing integer.

Now we have a new empty filter record in the "MainForm" when the form loads. The values inherit from row #2 which is blank.

It may be a good idea to use separated filter tables for the auto-incrementing filter IDs and for the static ones.

[Villeroy]

This seems to work better:

Code: Select all

Sub ClearColumns_FormLoad(e)
	frm = e.Source
	cols = frm.getColumns()
	hc = frm.getByName("ClearColumns")
	sHV = hc.HiddenValue
	a() = split(sHV, ";")
	for each s in a()
		col = cols.getByName(s)
		col.updateNull()
	next
	frm.updateRow() 'save form
	frm.reload() 'reload incl. subforms
End Sub

Hidden controls are made for macro configuration. Right-click form in form navigator and choose Add>"Hidden control".
Add a hwithidden control "ClearColumns". The value is a semicolon separated list of table columns to be cleared. In my example the value would be C1;D1;D2.
Bind the form's loading event to the above macro.
This macro works with any form but different values in hidden control "ClearColumns".

[UnklDonald418]

I believe your original approach using the statement service invoked by the When loading event on a form would work with a couple of tweaks.

In his book "Database Programming with OpenOffice.org Base & Basic", Roberto Benitez has a nice function that delivers the ActiveConnection when passed a form event.

Code: Select all

REM  *****  BASIC  *****

Function ThisConnection(Event As Object) As Object
On Error Goto HandleErr
  Dim obj As Object
  Dim FormImpName As String
  FormImpName="com.sun.star.comp.forms.ODatabaseForm"
    On Error Goto GetObject
    If IsNull(obj) Then
   	obj=Event.Source.Model
    Else
   	Exit Function
   End If
	While True
		If obj.ImplementationName=FormImpName Then REM FOUND THE FORM
			ThisConnection=obj.ActiveConnection REM get the active conn
			Exit Function
		End If
		obj=obj.Parent REM TRY TO GET PARENT
	Wend
	Exit Function
   GetObject:
   	On Error Goto HandleErr
	obj=Event.Source
	Resume Next
   HandleErr:
      REM some error occurred
	Exit Function
End Function

Unfortunately assigning it to "when loading" event, it runs too late

Once the statement has been executed simply reloading the form will resolve that issue. So, using the function ThisConnction() your original macro then becomes

Code: Select all

REM Clear old entry from Filter table at startup
Sub Clear_Filter_Table (oEv as Object)

Dim oStatement As Object
Dim oConn as Object

oConn = ThisConnection(oEv)
oStatement = oConn.createStatement()
oStatement.execute("update ""Filter"" set ""F1"" = NULL where ""FilterID"" = 0;")

oEv.Source.reload()

End Sub

[F3K Total]

Another proposal:
The following code clears all nullable columns of the filterform, means all, except the pk-column. No need for a hiddencontrol containing the columnnames.

Code: Select all

Sub ClearAllNullableColumns_FormLoad(e)
   frm = e.Source
   cols = frm.getColumns
   for each s in cols.ElementNames
      col = cols.getByName(s)
      if  col.IsNullable then col.updateNull
   next
   frm.updateRow() 'save form
   frm.reload() 'reload incl. subforms
End Sub

Bind the form's loading event to the above macro.
R

[cwdavi1]

Thank you to all. Villeroy, I had trouble figuring out your 1st suggestion. I tried the second one but had trouble with the macro you wrote. On entry it had access to the col for the second query, selecting the data to be displayed, but not the col F1 in the filter table I was trying to clear. On my last attempt suddenly I couldn't enter anything into a form or even a parameter for a simple query. I gave up on base and then found that it's windows itself, not base. I rebooted and had to wait for win updates to be applied (I'm typing this on my phone). That just finished so now I'm going to see if everything works then give up on base for awhile.

Thank you again.

[cwdavi1]

Somehow my keyboard USB connector got pulled out of the port.

While checking that everything was ok I decided to try changing where I had the hidden control macro trigger on the loading event. That was it, at least after I changed the property of the F1 field to allow null. The hidden control works and the filter field is cleared. When the form is opened the old parameter and the data from the search briefly appear before being blanked out. Much much later I'll do some more testing to see if my concern that started this requires anything else. I thought that if the last search run, with parameter still in the filter table, was run again when the form is opened that it could cause a completely unnecessary delay before a new search could be run. If that is a valid concern then I'll just move the macro trigger to a form closing event. None of the data queries will run if the F1 parameter is null.

Thank you again.