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

Creating and using forms

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

Postby FKlusmann » Sun Apr 23, 2017 5:53 pm

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
Attachments
Test-Find_Person.odb
(19.86 KiB) Downloaded 98 times
Last edited by FKlusmann on Sun Apr 23, 2017 9:02 pm, edited 1 time in total.
I Thank you ALL! (Win 10 - LibreOffice Version: 5.3.7.2 (x64))
Saving the Planet! Only recycled electrons were used in this post!
FKlusmann
 
Posts: 41
Joined: Fri Jan 20, 2017 3:22 am

Re: Run Query from Form & show results in Subform

Postby Villeroy » Sun Apr 23, 2017 7:38 pm

Macros are evil and obsolete in this particular case.
Code: Select all   Expand viewCollapse view
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"
Attachments
PowerFilter_Find_Person(1).odb
(22.17 KiB) Downloaded 152 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26401
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Run Query from Form & show results in Subform

Postby FKlusmann » Sun Apr 23, 2017 9:01 pm

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!
I Thank you ALL! (Win 10 - LibreOffice Version: 5.3.7.2 (x64))
Saving the Planet! Only recycled electrons were used in this post!
FKlusmann
 
Posts: 41
Joined: Fri Jan 20, 2017 3:22 am

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

Postby Villeroy » Sun Apr 23, 2017 9:18 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26401
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby FKlusmann » Sun Apr 23, 2017 10:39 pm

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.
I Thank you ALL! (Win 10 - LibreOffice Version: 5.3.7.2 (x64))
Saving the Planet! Only recycled electrons were used in this post!
FKlusmann
 
Posts: 41
Joined: Fri Jan 20, 2017 3:22 am

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

Postby Villeroy » Mon Apr 24, 2017 6:22 am

Code: Select all   Expand viewCollapse view
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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26401
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby FKlusmann » Mon Apr 24, 2017 4:18 pm

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!
I Thank you ALL! (Win 10 - LibreOffice Version: 5.3.7.2 (x64))
Saving the Planet! Only recycled electrons were used in this post!
FKlusmann
 
Posts: 41
Joined: Fri Jan 20, 2017 3:22 am

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

Postby Villeroy » Mon Apr 24, 2017 7:14 pm

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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26401
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby FKlusmann » Mon Apr 24, 2017 8:11 pm

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)
I Thank you ALL! (Win 10 - LibreOffice Version: 5.3.7.2 (x64))
Saving the Planet! Only recycled electrons were used in this post!
FKlusmann
 
Posts: 41
Joined: Fri Jan 20, 2017 3:22 am


Return to Forms

Who is online

Users browsing this forum: No registered users and 1 guest