Basing queries on a sub form

Basing queries on a sub form

Post by RWFtb25 » Wed, 08 Mar 2006 21:51:27


Basing queries on a sub form

Post by Ed Robicha » Wed, 08 Mar 2006 21:58:13

It's difficult to answer a blank question, but based on your msg title, you
appear to have it backwards. Queries are based on tables and/or other
queries. Forms (including sub-forms) use those queries as their record


Basing queries on a sub form

Post by RWFtb25 » Wed, 08 Mar 2006 23:01:29

Apologies ED -thought I cut and pasted my question in but clearly didnt.

Basically I have a lot of VBA code set up so that a sub form will act as a
filter table and I want to be able to base some charts on that selection (ie
the contents of the subform). Now I can do this fine with a command button
linked to a query which draws the info from the main linked in .xls sheet.
However to save me writing a frightning amount of code if I could just get
the query to run of the subform it would be superb.

For my first graph a breakdown by country Ive managed to link the query on
to the form using

SELECT subform3.Country, Sum(subform3.[LCL CCY ALLOCATED]) AS [SumOfLCL CCY
FROM sheet1 AS subform3
GROUP BY subform3.Country;

It s just when I run the query it pulls straight from the main sheet and
ignores my subform without any error message

Maybe I could have a table created that just from the subform and i could
run my query off that table.

Appreciate the help

Basing queries on a sub form

Post by chris.nebi » Wed, 08 Mar 2006 23:39:59

The correct format to reference a field on a subform is


Keep in mind, the SubFormControlName is not the name of the form, but
rather, the name of the control on the form.

Chris Nebinger

Basing queries on a sub form

Post by Ed Robicha » Thu, 09 Mar 2006 06:38:06

Rather than jump through all the hoops you outline (exporting to Excel,
etc.) have you thought about using a pivotchart? Access has the same graph
engine as Excel. You could include all the same filter fields that are now
in your forms (and incidentally, sub-forms typically show records from the
many side of a relationship/link, e.g. main form shows single customer
record, while related subform shows all invoices for that customer) and let
your users dynamically change all the chart inputs.

Basing queries on a sub form

Post by RWFtb25 » Thu, 09 Mar 2006 19:18:27

G Morning,

Appols ED if I wasnt clear but I am actually using the pivot chart view for
my graphs in Access. Problem is my query while populating my graph
automatically from a cmd button on the form will use all data in my form as
opposed to the filtered data in my subform.

Chris I'm fairly new (about a month) to Access/SQL/VBA could you possible
just tell me what exactly I have to write


Form name = "Copy of formqs"
Subform name= "sheet1 subform3"
For my first graphing query I want to sort by "Country"

tried your code but couldnt get it working

Thanks guys