Many apologies for the delay in my reply.
1. This is the most problematic issue - I can't get my form to display the data in the "recommendation" field of the sub-form. And currently, when it does do a search, it informs me that it is showing me record 1 of 1, even though I know there are more records that match the entered search criteria.
I suspect the source of several problems may be the various boolean (check box) fields in the “Data” and “Filter” tables. Such fields in the embedded database can be tri-state: they can hold 1 or 0 but also a NULL. For your purposes I suggest this tri-state is better avoided and NULL values should not be permitted for any of the boolean fields. The search criteria on those fields then becomes the simple values of 1 for checked and 0 for unchecked.
[Please note that this differs from the 'Power filter' example, which uses text fields. The search criteria for text fields are either the content of the field or the presence of a NULL.]
2. On the "Search" form, I would like the search fields (Problem, and the 6 checkboxes for age) to appear as blank each time, rather than as whatever was last entered.
You will appreciate that all these search form controls are contained in the “Filter” main data form whose source is the SQL
Code: Select all
SELECT "Filter".* FROM "Filter" AS "Filter" WHERE "ID" = 0
Thus, those form controls will tend to be filled with whatever values have been previously saved in the “Filter” table. I suspect it would require a macro (possibly triggered as the form document opens) to clear all those values. I would, however, avoid resort to any macro until you have all the other elements of the “Search” form document fully operational.
3. In the table that is part of the subform (on the Search form), I would like it to list the actual problem name (eg., reading) rather than the id number (eg., 1).
In Base this facility is readily supplied by use of a List box. For example in place of the Text box use a List box bound to the same “Data Field” property with SQL as “Type of list contents” property and
Code: Select all
SELECT "Problem", "ID_Prob" FROM "Problem"
as the “List Content” property.
Please come back to let us know how you get on or if these explanataions merely confuse further.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB