[Solved] Run Query from Form & show results in Subform

[FKlusmann]

Thanks again for sharing your knowledge with us.
I have a query to look for names with “Parameter Input”. “%i%” finds three records, “Fred” finds one as expected. That works fine but looks lousy.

I cannot figure out how to put this together as a Form and Subform. I’d like the “Parameter Input” to be part of the Main Form and the output of the query as the SubForm.
My failure is attached.
Please give me some hints.
Thank you.
-- Fred

[Villeroy]

Macros are evil and obsolete in this particular case.

Code: Select all

DROP VIEW "v_Find_People";
Alter table "tblPeople" alter column "FirstName"VARCHAR_IGNORECASE(15);
create table "Filter"(VCIC VARCHAR_IGNORECASE(15),FID INTEGER PRIMARY KEY);
insert into "Filter" VALUES('e',0);

Then I modified the query and made a "power filtering" form. Search this forum for "power filtering"

[FKlusmann]

Villeroy said: "Then I modified the query and made a "power filtering" form.

And that works very well! Thank you, Sir!

And "Search this forum for "power filtering"
I have, and have RPG's tutoring and example. Not all that I read sticks in my head,,,,

Again, Thank you, Sir!

[Villeroy]

I changed the field types from VARCHAR to VARCHAR_IGNORECASE for obvious reasons.
The main form which takes the filter criteria is based on one single record in a separate "Filter" table (WHERE ID=0). This single record is just made in order to provide a parent form for the filtered subform. Open the form properties. The main form query selects this single record only. The main form does not allow new records nor deletion of records so the user can not tab into another (new) record. Showing a navigation bar for a single record is not necessary.
Subforms can take parameter names instead of field names. Watch the properties of the subform too. The parameter is substituted by the parent form's "VCIG" (varchar_ignorecase).
The subform query selects all fields from the data table WHERE ("NameField= :parameter OR :parameter IS NULL). The condition returns True for all records when there is nothing in the search box so all records are shown.
The OK button belongs to the subform with action "Refresh". When another form's form control takes the focus, the previous form is saved automatically before the subform is refreshed.
A third query merges the filter record WHERE ID=0 AND ("NameField= "VCIC" OR "VCIC" IS NULL) with the data table in order to create a report. SInce this query is based on 2 tables, it is not editable which does not matter since reports are not editable anyway.

[FKlusmann]

Thank you for the explanation.

Placing "e" in VCIC shows "Edwin" while %e% shows Edwin, Fred and Aileen.
So I tried to expand the ->LIKE "Filter"."VCIC" || '%'<- to ->LIKE '%' || "Filter"."VCIC" || '%'<- hoping that "e" in txtVCIC would show all three. Is it my syntax or something else?

Again, my thanks for treating me gently.
-- Fred

NOTE: Wierdly, place the letter in txtVCIC in the form and do not see the hoped-for result, however, run the query alone and the hoped-for results are there....

Edit: Note added.

[Villeroy]

Code: Select all

SELECT "PersonID" AS "PersonID", "FirstName" AS "FirstName", "LastName" AS "LastName", "PhonePrimary" AS "PhonePrimary", "EmailPrimary" AS "EmailPrimary" 
FROM "tblPeople", "Filter" 
WHERE "Filter"."FID" = 0 AND "FirstName" LIKE '%'|| "Filter"."VCIC" || '%' 
ORDER BY "LastName" ASC, "FirstName" ASC

[FKlusmann]

Thank you again, Villeroy !
What a difference a "space" can make!
Why would the stand-alone query be forgiving; the form based on the same query not?
Is there a better place to see the syntax than the "HyperSQL User Guide" from http://hsqldb.org/doc/guide/guide.pdf ?
Thank you again!

[Villeroy]

This query requires only one space in "ORDER BY". I only added '%' || before "FirstName", tested the query, copied my working query from the SQL editor into the forum post.

[FKlusmann]

My point is that
LIKE '%' || "Filter"."VCIC" || '% works in the standalone query but not the form.
LIKE '%'|| "Filter"."VCIC" || '%' works in both.

-- Fred (LibreOffice 5.2.6.2 on Win 10 Ver 1511)