Basing queries on a sub form

[Ed Robichaud]

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
source.

 

[Eamonn]

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
ALLOCATED]
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
Eamonn

 

[chris.nebinger]

The correct format to reference a field on a subform is

Forms("FormName").Controls("SubFormControlName").Form.Controls("ControlName")

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


Chris Nebinger

 

[Ed Robichaud]

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.
-Ed

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
ALLOCATED]
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
Eamonn

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
source.

 

[Eamonn]

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

given

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
-Eamonn

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.
-Ed

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
ALLOCATED]
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
Eamonn

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
source.