[Solved] Problem populating a dropdown list from a Query

[Tryhard]

Hello everybody!
This is what I am trying to accomplish: I have a table "Clients" with more than 2000 values on it, and a table "Invoice", correctly related to "Clients".
When on the "Invoice" form, the first thing I have to do is to enter the "Client". To avoid misspelling it must be through a listbox, but since I have more than 2000 clients, this is not practical. After some research I found a way to use a search box with Wildcards. So, if I type "Vand", I can narrow down to fewer clients. For example, it returns, instead of 2000 values, only "Vandeley Industries", "Lowvand Services" and "Vando Equipments".
So my first step was to create a query like this:

Code: Select all

SELECT * FROM "Clients" WHERE ((UPPER("ClientName") LIKE '%' || UPPER (:var_CL) || '%' ) OR (:var_CL IS NULL))

This Query was properly saved as "SearchClient".
Then I created a table with basically one VARCHART Field. I used this table to create a new form, where the user can input a search term (like "Vand"). Plus a refresh button.
Then I created a subform based on the Query "SearchClient", with the slave field being 'var_CL'. My wish was to include a listbox in it, whose values would be those of the Query, therefore I would be able to select from fewer clients. But when I do this, I get the following error message:

Assert failed: S0000 Direct execute with param count > 0java.lang.Exception
at org.hsqldb.Trace.getStackTrace(Unknown Source)
at org.hsqldb.Trace.doAssert(Unknown Source)
at org.hsqldb.DatabaseCommandInterpreter.executePart(Unknown Source)
at org.hsqldb.DatabaseCommandInterpreter.execute(Unknown Source)
at org.hsqldb.Session.sqlExecuteDirectNoPreChecks(Unknown Source)
at org.hsqldb.Session.execute(Unknown Source)
at org.hsqldb.jdbc.jdbcStatement.fetchResult(Unknown Source)
at org.hsqldb.jdbc.jdbcStatement.executeQuery(Unknown Source)
in statement [SELECT * FROM "Clients" WHERE ((UPPER("ClientName") LIKE '%' || UPPER (:var_CL) || '%' ) OR (:var_CL IS NULL))]

The interesting thing is that if instead of the listbox I just include a table (as a subform based on Query "Search Client") everything works perfectly, showing only the few clients that contain the search term within it. The Query also works perfectly on test mode.

So this is what I tried. If anybody has a suggestion, I would be really thankful. But the main problem is this: too many entries in "Clients" to choose from. Many thanks!

[Villeroy]

The easy solution: if it works with a form, then use a one-column form instead of a listbox showing the client's invoices (latest on top). Then click on the invoice and show the invoice details in a sub-sub-form. You can build arbitrary complex hierarchies of forms and subforms.

p.s. Parameter substitution works between forms and subforms where the subform shows the record set according to the parameter value(s) substituted by its parent form.
Listboxes don't take any parameters. However, you can use something like
SELECT "txt", "id" FROM "Listbox Source" WHERE "something" LIKE '%'|| (SELECT "filter_text" FROM "other_table")

[UnklDonald418]

Here is a demonstration of something similar to what I think you are attempting.

Demo36a_CustomerSelect.odb

(39.68 KiB) Downloaded 170 times

[Villeroy]

And this one: viewtopic.php?t=94393&p=449618#p449618
The macro free first form "Voyages" works well with some extra clicks.
The second one "Voyages_Autorefresh" seems to be broken but "Voyages_AutoRefresh2" works fine with select, enter, tab, select, enter, tab after you have installed this macro code: download/file.php?id=35500

[Tryhard]

Thank you for your great suggestions!
The one that comes very close to what I want is the Customer Select demonstration. Since I am bad with Macros, I will use this solution with buttons.
I see that the 'Step 2' button saves and the ' Step 3' button refreshes. Isn't there a way for just one button do both actions? Just wondering.
Many thanks once again you both!

[UnklDonald418]

Using the approach shown in Demo36a_CustomerSelect.odb it is possible to eliminate the button labeled Step 2 Save filter string but the trade-off is a popup dialog that requires an answer.

To see how that would work
Type a string into the text box labeled Step 1 Enter Filter string
Press the button labeled Step 3 Update Listbox
Then you should see a dialog The content of the current form has been modified. Do you want to save your changes?
Fortunately, Yes is the default selection, so simply pressing Enter will save the string value in the Filter table and the list box selection list should be updated.