[Solved] Run Query from Form & show results in Subform
[Solved] Run Query from Form & show results in Subform
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
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
- Attachments
-
- Test-Find_Person.odb
- (19.86 KiB) Downloaded 342 times
Last edited by FKlusmann on Sun Apr 23, 2017 9:02 pm, edited 1 time in total.
Thank you ALL! (Win 10 - LibreOffice Version: 7.0.3.1 (x64))
Saving the Planet! Only recycled electrons were used in this post!
Saving the Planet! Only recycled electrons were used in this post!
Re: Run Query from Form & show results in Subform
Macros are evil and obsolete in this particular case.
Then I modified the query and made a "power filtering" form. Search this forum for "power filtering"
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);
- Attachments
-
- PowerFilter_Find_Person(1).odb
- (22.17 KiB) Downloaded 422 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Run Query from Form & show results in Subform
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!
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!
Thank you ALL! (Win 10 - LibreOffice Version: 7.0.3.1 (x64))
Saving the Planet! Only recycled electrons were used in this post!
Saving the Planet! Only recycled electrons were used in this post!
Re: [Solved!] Run Query from Form & show results in Subform
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.
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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Solved] Run Query from Form & show results in Subform
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.
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.
Thank you ALL! (Win 10 - LibreOffice Version: 7.0.3.1 (x64))
Saving the Planet! Only recycled electrons were used in this post!
Saving the Planet! Only recycled electrons were used in this post!
Re: [Solved] Run Query from Form & show results in Subform
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
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Solved] Run Query from Form & show results in Subform
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!
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!
Thank you ALL! (Win 10 - LibreOffice Version: 7.0.3.1 (x64))
Saving the Planet! Only recycled electrons were used in this post!
Saving the Planet! Only recycled electrons were used in this post!
Re: [Solved] Run Query from Form & show results in Subform
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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Solved] Run Query from Form & show results in Subform
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)
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)
Thank you ALL! (Win 10 - LibreOffice Version: 7.0.3.1 (x64))
Saving the Planet! Only recycled electrons were used in this post!
Saving the Planet! Only recycled electrons were used in this post!