Basing queries on a sub form

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" wrote in message
...

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






"Ed Robichaud" wrote:

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" wrote in message
...

The correct format to reference a field on a subform is

Forms("FormName").Controls("SubFormControlName").F orm.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

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

"Eamonn" wrote in message
...
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






"Ed Robichaud" wrote:

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" wrote in message
...

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

"Ed Robichaud" wrote:

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

"Eamonn" wrote in message
...
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






"Ed Robichaud" wrote:

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" wrote in message
...