[SOLVED] Macro to run sql on open document event

Creating a macro - Writing a Script - Using the API

[SOLVED] Macro to run sql on open document event

Postby cwdavi1 » Sat Oct 05, 2019 9:50 pm

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   Expand viewCollapse view
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.
Last edited by robleyd on Mon Oct 07, 2019 3:56 am, edited 2 times in total.
Reason: Add green tick
Libreoffice 6.3 on Windows 10 Pro
cwdavi1
 
Posts: 14
Joined: Fri Sep 13, 2019 9:47 pm

Re: help with macro to run sql on open document event

Postby cwdavi1 » Sun Oct 06, 2019 7:02 am

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.
Libreoffice 6.3 on Windows 10 Pro
cwdavi1
 
Posts: 14
Joined: Fri Sep 13, 2019 9:47 pm

Re: help with macro to run sql on open document event

Postby gkick » Sun Oct 06, 2019 7:24 am

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   Expand viewCollapse view
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
Attachments
resetfilter.JPG
resetfilter.JPG (19.23 KiB) Viewed 450 times
Libre Office 6.2 on Windows 10 HSQL 2.5 backend
gkick
 
Posts: 120
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: help with macro to run sql on open document event

Postby Villeroy » Sun Oct 06, 2019 12:10 pm

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   Expand viewCollapse view
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
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27309
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: help with macro to run sql on open document event

Postby F3K Total » Sun Oct 06, 2019 12:54 pm

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
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 7 OOo, AOO, LOLinux Mint OOo, AOO, LO
F3K Total
Volunteer
 
Posts: 935
Joined: Fri Dec 16, 2011 8:20 pm

Re: help with macro to run sql on open document event

Postby Villeroy » Sun Oct 06, 2019 1:31 pm

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.
Attachments
DummyPersons3.odb
(58.44 KiB) Downloaded 13 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27309
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: help with macro to run sql on open document event

Postby Villeroy » Sun Oct 06, 2019 3:13 pm

This seems to work better:
Code: Select all   Expand viewCollapse view
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".
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27309
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: help with macro to run sql on open document event

Postby UnklDonald418 » Sun Oct 06, 2019 6:32 pm

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   Expand viewCollapse view
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   Expand viewCollapse view
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
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.6 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1242
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: help with macro to run sql on open document event

Postby F3K Total » Sun Oct 06, 2019 6:42 pm

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   Expand viewCollapse view
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
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 7 OOo, AOO, LOLinux Mint OOo, AOO, LO
F3K Total
Volunteer
 
Posts: 935
Joined: Fri Dec 16, 2011 8:20 pm

Re: Macro to run sql on open document event

Postby cwdavi1 » Mon Oct 07, 2019 2:48 am

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.
Libreoffice 6.3 on Windows 10 Pro
cwdavi1
 
Posts: 14
Joined: Fri Sep 13, 2019 9:47 pm

Re: [SOLVED] Macro to run sql on open document event

Postby cwdavi1 » Mon Oct 07, 2019 3:34 am

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.
Libreoffice 6.3 on Windows 10 Pro
cwdavi1
 
Posts: 14
Joined: Fri Sep 13, 2019 9:47 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 4 guests