Chart uses Filtered Form as Recordset

[Al Camp]

I presently have a chart that shows sales referral counts according to
different advertising medium. It plots against ALL records in a table, and
that works fine.

However, rather than base the chart against the entire table, I'd like to
use the recordset from an open filtered form as the recordsource for the
chart. The filtering will usually be different, sometimes by City,
sometimes, by State, etc etc.

My take is to try to "capture" the filtered recordset at the time the chart
is called for... save it to a Temp table, and use that table as the
recordsource for the report chart.

What would be the best method to capture that "active" filtered recordset to
a table?

Thanks in advance,
Al Camp

 

[Duane Hookom]

How do we know what you mean by "active filtered recordset"? Are you
allowing users to open tables and apply filtering? Or, are you using a form
with unbound controls used as criteria in a query?

Using a form with controls is a much more controlled method.

 

[Al Camp]

Duane,
Thanks for the response... sorry I wasn't clearer.

The user is filtering a normal data entry form (frmCustomers) via the
filtering functions available in the form's shortcut (Right-Click) menu.
FilterByForm, FilterBySelection...etc etc. Filtering will probably always
vary... according to the user's needs. No way to "can" a query...

I have several other reports that use the same .filter property as
frmCustomers form at print time, but I can't figure out how I can do that
for a Chart. The chart doesn't have a FilterProperty, so I thought I'd try
to control the table the chart is based on.
ex. If the user filters frmCustomers by State and City, I'd like my
chart to reflect/report just those values.

I found a way to do it manually...
1. Filter the form by State and Zipcode.
2. Use the Advanced Filter/Sort button to display the current filter
as a query design grid.
3. Save that query as qryChartData
4. The Chart always looks to qryChartData as it RecordSource.
Now I just need to "save this filter as a query" programatically. Help
only shows how to do it manually.

Thanks for your patience,
Al Camp

 

[Duane Hookom]

You can access the current form's filter property and add it to the chart's
row source. For instance assuming Chart1 has a Row Source property of:
SELECT Region, Sum(Sales) as [Total Sales]
FROM tblSales
GROUP BY Region;

You can try code like (caution, air coding ahead):
Dim strSQL as String
strSQL = "SELECT Region, Sum(Sales) as [Total Sales] " & _
"FROM tblSales WHERE " & Me.Filter & _
" GROUP BY Region"
Me.Chart1.RowSource = strSQL

 

[Al Camp]

Thanks Duane,
It may take me a while to work this out, but I'll try to get back with
the results ASAP.
I also tried...
DoCmd.RunCommand acCmdAdvancedFilterSort
DoCmd.RunCommand acCmdSaveAsQuery

which works, but... acCmdSaveAsQuery always prompts for a Query Name...
which I can't have.

Close but no cigar!
Thanks,
Al Camp

You can access the current form's filter property and add it to the
chart's row source. For instance assuming Chart1 has a Row Source property
of:
SELECT Region, Sum(Sales) as [Total Sales]
FROM tblSales
GROUP BY Region;

You can try code like (caution, air coding ahead):
Dim strSQL as String
strSQL = "SELECT Region, Sum(Sales) as [Total Sales] " & _
"FROM tblSales WHERE " & Me.Filter & _
" GROUP BY Region"
Me.Chart1.RowSource = strSQL


--
Duane Hookom
MS Access MVP

Duane,
Thanks for the response... sorry I wasn't clearer.

The user is filtering a normal data entry form (frmCustomers) via the
filtering functions available in the form's shortcut (Right-Click) menu.
FilterByForm, FilterBySelection...etc etc. Filtering will probably
always vary... according to the user's needs. No way to "can" a query...

I have several other reports that use the same .filter property as
frmCustomers form at print time, but I can't figure out how I can do that
for a Chart. The chart doesn't have a FilterProperty, so I thought I'd
try to control the table the chart is based on.
ex. If the user filters frmCustomers by State and City, I'd like my
chart to reflect/report just those values.

I found a way to do it manually...
1. Filter the form by State and Zipcode.
2. Use the Advanced Filter/Sort button to display the current
filter as a query design grid.
3. Save that query as qryChartData
4. The Chart always looks to qryChartData as it RecordSource.
Now I just need to "save this filter as a query" programatically.
Help only shows how to do it manually.

Thanks for your patience,
Al Camp

 

[Al Camp]

Duane,
I'm not having any luck getting the Report/Chart to accept the SQL string
as a Rowsource. The problem is how to pass a variable string from the form
to the report/chart.
I declared a global variable in an external module...
Public AdvChartSQL as String
I have this frmCustomer code...
AdvChartSQL = "TRANSFORM Count(tblCSNHCustomers.Advertising) AS
AdCount SELECT tblCSNHCustomers.Advertising FROM tblCSNHCustomers " & _
"WHERE " & Me.Filter & " GROUP BY tblCSNHCustomers.Advertising ORDER BY
tblCSNHCustomers.Advertising DESC PIVOT 'Count';"
Refresh
DoCmd.OpenReport "rptAdvertisingReferrals", acViewPreview

I tried using the report OnOpen...
Me.CSNHReferral.RowSource = AdvChartSQL
(fails with invalid expression to property rowsource)

How can I assign this string to my report chart recordsource?

Thank you for any help.
Al Camp

You can access the current form's filter property and add it to the
chart's row source. For instance assuming Chart1 has a Row Source property
of:
SELECT Region, Sum(Sales) as [Total Sales]
FROM tblSales
GROUP BY Region;

You can try code like (caution, air coding ahead):
Dim strSQL as String
strSQL = "SELECT Region, Sum(Sales) as [Total Sales] " & _
"FROM tblSales WHERE " & Me.Filter & _
" GROUP BY Region"
Me.Chart1.RowSource = strSQL


--
Duane Hookom
MS Access MVP

Duane,
Thanks for the response... sorry I wasn't clearer.

The user is filtering a normal data entry form (frmCustomers) via the
filtering functions available in the form's shortcut (Right-Click) menu.
FilterByForm, FilterBySelection...etc etc. Filtering will probably
always vary... according to the user's needs. No way to "can" a query...

I have several other reports that use the same .filter property as
frmCustomers form at print time, but I can't figure out how I can do that
for a Chart. The chart doesn't have a FilterProperty, so I thought I'd
try to control the table the chart is based on.
ex. If the user filters frmCustomers by State and City, I'd like my
chart to reflect/report just those values.

I found a way to do it manually...
1. Filter the form by State and Zipcode.
2. Use the Advanced Filter/Sort button to display the current
filter as a query design grid.
3. Save that query as qryChartData
4. The Chart always looks to qryChartData as it RecordSource.
Now I just need to "save this filter as a query" programatically.
Help only shows how to do it manually.

Thanks for your patience,
Al Camp

 

[Duane Hookom]

You can use a saved query as the chart row source and some DAO code:
Currentdb.QueryDefs("qxtbChartRowSource").SQL = AdvChartSQL
then open the freport

--
Duane Hookom
MS Access MVP

Duane,
I'm not having any luck getting the Report/Chart to accept the SQL
string as a Rowsource. The problem is how to pass a variable string from
the form to the report/chart.
I declared a global variable in an external module...
Public AdvChartSQL as String
I have this frmCustomer code...
AdvChartSQL = "TRANSFORM Count(tblCSNHCustomers.Advertising) AS
AdCount SELECT tblCSNHCustomers.Advertising FROM tblCSNHCustomers " & _
"WHERE " & Me.Filter & " GROUP BY tblCSNHCustomers.Advertising ORDER BY
tblCSNHCustomers.Advertising DESC PIVOT 'Count';"
Refresh
DoCmd.OpenReport "rptAdvertisingReferrals", acViewPreview

I tried using the report OnOpen...
Me.CSNHReferral.RowSource = AdvChartSQL
(fails with invalid expression to property rowsource)

How can I assign this string to my report chart recordsource?

Thank you for any help.
Al Camp

You can access the current form's filter property and add it to the
chart's row source. For instance assuming Chart1 has a Row Source
property of:
SELECT Region, Sum(Sales) as [Total Sales]
FROM tblSales
GROUP BY Region;

You can try code like (caution, air coding ahead):
Dim strSQL as String
strSQL = "SELECT Region, Sum(Sales) as [Total Sales] " & _
"FROM tblSales WHERE " & Me.Filter & _
" GROUP BY Region"
Me.Chart1.RowSource = strSQL


--
Duane Hookom
MS Access MVP

Duane,
Thanks for the response... sorry I wasn't clearer.

The user is filtering a normal data entry form (frmCustomers) via the
filtering functions available in the form's shortcut (Right-Click) menu.
FilterByForm, FilterBySelection...etc etc. Filtering will probably
always vary... according to the user's needs. No way to "can" a
query...

I have several other reports that use the same .filter property as
frmCustomers form at print time, but I can't figure out how I can do
that for a Chart. The chart doesn't have a FilterProperty, so I thought
I'd try to control the table the chart is based on.
ex. If the user filters frmCustomers by State and City, I'd like my
chart to reflect/report just those values.

I found a way to do it manually...
1. Filter the form by State and Zipcode.
2. Use the Advanced Filter/Sort button to display the current
filter as a query design grid.
3. Save that query as qryChartData
4. The Chart always looks to qryChartData as it RecordSource.
Now I just need to "save this filter as a query" programatically.
Help only shows how to do it manually.

Thanks for your patience,
Al Camp


How do we know what you mean by "active filtered recordset"? Are you
allowing users to open tables and apply filtering? Or, are you using a
form with unbound controls used as criteria in a query?

Using a form with controls is a much more controlled method.

--
Duane Hookom
MS Access MVP


I presently have a chart that shows sales referral counts according to
different advertising medium. It plots against ALL records in a table,
and that works fine.

However, rather than base the chart against the entire table, I'd like
to use the recordset from an open filtered form as the recordsource
for the chart. The filtering will usually be different, sometimes by
City, sometimes, by State, etc etc.

My take is to try to "capture" the filtered recordset at the time the
chart is called for... save it to a Temp table, and use that table as
the recordsource for the report chart.

What would be the best method to capture that "active" filtered
recordset to a table?

Thanks in advance,
Al Camp

 

[Al Camp]

Duane,
That did the trick! Just what I needed... how to save the current form
filter as a query or table.
If there is no filter applied to the form the QueryDef code doesn't work,
so I just built an IF statement to handle Filteron=True or Filteron = False
situation.
No matter what I tried, I just couldn't seem to assign that chart
Rowsource "on the fly"
Thanks a lot for the help!
Al Camp

You can use a saved query as the chart row source and some DAO code:
Currentdb.QueryDefs("qxtbChartRowSource").SQL = AdvChartSQL
then open the freport

--
Duane Hookom
MS Access MVP

Duane,
I'm not having any luck getting the Report/Chart to accept the SQL
string as a Rowsource. The problem is how to pass a variable string from
the form to the report/chart.
I declared a global variable in an external module...
Public AdvChartSQL as String
I have this frmCustomer code...
AdvChartSQL = "TRANSFORM Count(tblCSNHCustomers.Advertising) AS
AdCount SELECT tblCSNHCustomers.Advertising FROM tblCSNHCustomers " & _
"WHERE " & Me.Filter & " GROUP BY tblCSNHCustomers.Advertising ORDER BY
tblCSNHCustomers.Advertising DESC PIVOT 'Count';"
Refresh
DoCmd.OpenReport "rptAdvertisingReferrals", acViewPreview

I tried using the report OnOpen...
Me.CSNHReferral.RowSource = AdvChartSQL
(fails with invalid expression to property rowsource)

How can I assign this string to my report chart recordsource?

Thank you for any help.
Al Camp

You can access the current form's filter property and add it to the
chart's row source. For instance assuming Chart1 has a Row Source
property of:
SELECT Region, Sum(Sales) as [Total Sales]
FROM tblSales
GROUP BY Region;

You can try code like (caution, air coding ahead):
Dim strSQL as String
strSQL = "SELECT Region, Sum(Sales) as [Total Sales] " & _
"FROM tblSales WHERE " & Me.Filter & _
" GROUP BY Region"
Me.Chart1.RowSource = strSQL


--
Duane Hookom
MS Access MVP


Duane,
Thanks for the response... sorry I wasn't clearer.

The user is filtering a normal data entry form (frmCustomers) via
the filtering functions available in the form's shortcut (Right-Click)
menu. FilterByForm, FilterBySelection...etc etc. Filtering will
probably always vary... according to the user's needs. No way to "can"
a query...

I have several other reports that use the same .filter property as
frmCustomers form at print time, but I can't figure out how I can do
that for a Chart. The chart doesn't have a FilterProperty, so I
thought I'd try to control the table the chart is based on.
ex. If the user filters frmCustomers by State and City, I'd like my
chart to reflect/report just those values.

I found a way to do it manually...
1. Filter the form by State and Zipcode.
2. Use the Advanced Filter/Sort button to display the current
filter as a query design grid.
3. Save that query as qryChartData
4. The Chart always looks to qryChartData as it RecordSource.
Now I just need to "save this filter as a query" programatically.
Help only shows how to do it manually.

Thanks for your patience,
Al Camp


How do we know what you mean by "active filtered recordset"? Are you
allowing users to open tables and apply filtering? Or, are you using a
form with unbound controls used as criteria in a query?

Using a form with controls is a much more controlled method.

--
Duane Hookom
MS Access MVP


I presently have a chart that shows sales referral counts according to
different advertising medium. It plots against ALL records in a
table, and that works fine.

However, rather than base the chart against the entire table, I'd
like to use the recordset from an open filtered form as the
recordsource for the chart. The filtering will usually be different,
sometimes by City, sometimes, by State, etc etc.

My take is to try to "capture" the filtered recordset at the time the
chart is called for... save it to a Temp table, and use that table as
the recordsource for the report chart.

What would be the best method to capture that "active" filtered
recordset to a table?

Thanks in advance,
Al Camp