Ask Your Question

Searching for data [BH40-Base Handbook p.200]

asked 2020-01-15 02:28:28 +0100

bbater gravatar image

Operating system: Windows 10 Pro Version of LibreOffice: Version: (x64) File format: ODB Database engine: Firebird.

Using a 'FilterExample' table as described on page 198 works well with a ListBox control which has a 'Type of list contents' property which can be set to 'Sql' with the query entered in property 'List content'.

The advice on searching, rather than filtering, on page 200 however, fails to say how to mimic this construct using text entered into a control which is not of ListBox type. A TextBox control does not have a 'Type of list contents' property which can be set to 'Sql', so my question is:

In order to search on text entered into a TextBox, where does one enter the SQL query when a TextBox control has no 'Type of list contents' property?

FYI, the valid SQL query in question is:

SELECT FamilyName, Client_ID FROM "Client" WHERE LOWER("FamilyName") LIKE NULLIF( ( SELECT '%' || LOWER("SearchTerm") ||'%' FROM "SearchTable" ), LOWER("FamilyName") )

All advice appreciated!

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2020-01-15 03:37:35 +0100

Ratslinger gravatar image

updated 2020-01-17 01:50:38 +0100


The text box is not associated with any SQL itself. It's main purpose, unlike the list box, is to enter partial information. This is again stored in the filter table and then used by the query. The query is the basis of the sub form. A push button with the 'Action' set to 'Refresh form' run the query and displays the information.

A sample of this can be found in this post -> Need to Filter a table with two fields for key words. Please keep in mind this is a crude Q&D sample.

For a test enter hor in the text box and press the button to see the result.

Edit 2020-01-16:

Same sample as in link except for Firebird ----- BookSearch.odb

edit flag offensive delete link more


Thanks Ratslinger. I think I asked the wrong question. I didn't have the query as the data source for the subform, but now that's rectified, it's still not working. The problem seems to be that the partial string value entered into the TextBox (the search term) is not being saved to the filter table. So the question is, what event or change of state of the TextBox can be used to trigger saving of the value to the filter table before the query is run?

BTW, I can't run the example you mentioned because a) it's based on HSQLDB (I'm using Firebird) and it says it can't find a 64-bit JRE, which is odd, because I reinstalled it last night just to be sure. And my own database seems happy with the installed JRE.

bbater gravatar imagebbater ( 2020-01-17 00:17:19 +0100 )edit

Firebird does not need JRE so you probably have the wrong JRE installed or the settings in 'Tools-Options-LibreOffice-Advanced` set incorrectly.

You should have a main form and a second form (or sub form). The main form has the text box and the sub form the query results. The push button with the action set to Reload form should be on the sub form.

Edit: Added same Base file sample using Firebird to answer.

Ratslinger gravatar imageRatslinger ( 2020-01-17 01:03:46 +0100 )edit

I've re-installed JRE with an explicitly-stated 64-bit version in Control Panel > Programs. Your example using HSQLDB now works. I have a Main and subform as you describe and my Filter Table now updates when I enter a search term. However, the [Search] button on the subform refreshes only the corresponding control in the subform with the partial string entered as the search term. None of the other fields are refreshed. I will continue working on it. But one question: my subform itself contains several further subforms. Could this be part of the problem? Thanks for your help so far, I'll get back if I need to.

bbater gravatar imagebbater ( 2020-01-17 02:25:16 +0100 )edit

answered 2020-01-17 03:00:04 +0100

bbater gravatar image

updated 2020-01-17 03:04:54 +0100

Problem solved. Further to the above, in Ratslinger's example, I noted that there was no linkage entered between Mainform and Subform. My subform was not refreshing because I had explicitly set the Mainform > Subform linkage (Master field / Slave field) to my FilterForm 'SearchTerm' > Subform (corresponding column), which I understood to be necessary between any Mainform/Subform pair. Deleting this linkage and leaving it blank solved the problem. The first-level Subform refreshes, as do its multiple second-level Subforms. So, thanks again, Ratslinger.

BTW, my query on which the first Subform is based is not as stated above, but actually:

SELECT * FROM "Client" WHERE LOWER("FamilyName") LIKE NULLIF( ( SELECT '%' || LOWER("SearchTerm") ||'%' FROM "SearchTable" ), LOWER("FamilyName") )

edit flag offensive delete link more


Just to clarify, in the sample the two could have been main forms each instead of a main and a sub.

Ratslinger gravatar imageRatslinger ( 2020-01-17 03:11:47 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-01-15 02:28:28 +0100

Seen: 61 times

Last updated: Jan 17 '20