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

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

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.

Hello,

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).

Edit:

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
oEvent.Source.setString(1,"Bill")
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.

AND:

If this answers your question please tick the :heavy_check_mark: (upper left area of answer). It helps others to know there was an accepted answer.

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

@jimk 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.

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 (!)

@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!

@SagiNadav 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.