Ask Your Question

Fill out a SQL query parameter by running a Event Macro "Fill Parameters" on Subform

asked 2018-05-06 15:23:23 +0200

Sagi Nadav gravatar image

updated 2018-05-07 18:22:11 +0200

Hello everyone!

I try for more than a week to fill out a SQL query parameter by running a event macro named "Fill Parameters" in the subform by searching for a way to fill the parameter under the Parameters object using MRI without success.

Searches for guides and code samples on Google and the relevant forums also found nothing.

Have I missed something or am I looking in the wrong place? Thanks in advance!

Sql query:

SELECT * FROM "Table1" WHERE "Record-Id" = :Record-Id

Macro code to display a parameters event object in Python and in Oo Basic:

def ParametersInspect (oEvent):
   ctx = XSCRIPTONTONT.getComponentContext ();
   mri (ctx, oEvent.Parameters);

Sub ParametersInspect (oEvent As Object)
    Globalscope.BasicLibraries.LoadLibrary( "MRILib" )

    oMRI = CreateUnoService( "mytools.Mri" )
    oMRI.inspect( oEvent.Parameters)
End Sub
edit retag flag offensive close merge delete



Please do not cross post (as you did here -> Best way to run Macro on LibreOffice or OpenOffice SubForm “Fill parameters” Event ) without noting this information in your question. If it is answered elsewhere, you could be wasting peoples time.

Ratslinger gravatar imageRatslinger ( 2018-05-06 17:40:27 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2018-05-06 17:37:14 +0200

Ratslinger gravatar image

updated 2018-05-07 09:02:37 +0200


Although have never used 'Fill Parameters' event (and looking at currently), cannot comprehend why you need a parameter on a query to be used on a sub form. It seems this is better accomplished by using a table filter thus eliminating coding of any type. It is always preferable to avoid macros where possible.

For a table filter sample please see this post -> Need to Filter a table with two fields for key words.

If this does not fit your needs, please further explain your process & reasoning (amend original question by editing).


Did about as much research on this as I care to. Frankly, I can't see expending the time necessary for what is actually accomplished. There are multiple ways to do this easier: table filter (no macros); move info into filter using macro; run query from macro substituting the parameter in the macro. This last one can be done starting with:

oDatabaseFile = ThisComponent.Parent.CurrentController.DataSource
oQuery = oDatabaseFile.getQueryDefinitions()
stQuery = oQuery.getByName("MyQuery").Command
stQuery = Replace(stQuery, ":my_id","?")

then fill in the parameter ("?") and move the completed field into the form which would be set for SQL as Data source (not Query or Table). Have used this and filtering quite often.

But back to the original question. Using MRI & this post -> See Parameters section Here, I found addDatabaseParameterListener which with a couple of links brought me to -> Parameters on the AOO wiki. There are a number of links to follow on the page & it also appears using Basic may be out of the question (you refer to Python anyway). But, again, after 30 or so more minutes I felt it was a lot more effort than it deserved. You're welcome to follow it through.

Edit 2 on 2018-05-06:

The above edit would be to set your own event/listener.

OK. Really spent too much time on this (another couple of hours). There is a way to set the parameter but it seems to work erratically. First, the routine is called multiple times depending upon where it is called from (Opening form or new record selected). The method to set the parameter is found here -> interface XParameters. With that, and the parameter based on a string, this is the routine used:

Sub ParamCheck(oEvent)
If oEvent.Source.Count = 0 Then Exit Sub
End Sub

The problem was it only worked occasionally. No rhyme or reason. No changes made. Just would stop working & then very hard to get working again. Don't know what triggers it.

Have now tried in Python and it seems to be working. Attached is a sample. The sample only displays Employees with a name of "Mary" in the sub form. Python macro is embedded in document. Did not want to create an elaborate test. This is left for you to accomplish.

Sample -> SelectCompanyByFilter2.odb

Please mark your cross post accordingly.


If this answers your question please tick ... (more)

edit flag offensive delete link more


Good answer as always. The code says Created on Tue Jun 13 14:40:23 2017 -- did you really write it last year?

Jim K gravatar imageJim K ( 2018-05-07 15:04:50 +0200 )edit

@Jim K Thank always for kind words & support here & on other post.

No. The solution & code were discovered yesterday 2018-05-06. It was an old modified .odb that was used for the test & sample.

The code above in basic actually works (as well as the tested Python). During one of the tests I actually deleted the searched for record & therefore it appeared to be erratic behavior. The solution is so simple. Actually same as Filling for Stored Procedure. Originally thought it to be different.

Ratslinger gravatar imageRatslinger ( 2018-05-07 16:16:14 +0200 )edit

Yesterday, after the above problem, I did not get a reasonable solution (here and as you saw in other places) and it became very urgent for a project that I was working on for a client of the company in which I work, I got a permit and budget to hire an expert programmer on LibreOffice Macro who solved the problem in less than an hour (!)

Sagi Nadav gravatar imageSagi Nadav ( 2018-05-07 19:03:36 +0200 )edit

@Ratslinger If you will, I have no problem publishing a code snippet that solves the problem in a separate answer, So if you want more time to think about a solution that meets the problem you welcome to comment below, thanks!

Sagi Nadav gravatar imageSagi Nadav ( 2018-05-07 19:05:21 +0200 )edit

@Sagi Nadav You must not have read my answer. Problem solved yesterday and noted in answer & comment. Basic code is in answer & Python code is in the provided sample.

Not clear as to why you waited a day to specify you already had an answer.

Doesn't matter. Provided the answer and multiple other solutions above.

Ratslinger gravatar imageRatslinger ( 2018-05-07 19:25:15 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-05-06 15:23:23 +0200

Seen: 279 times

Last updated: May 07 '18