Chart based on query not updating

how do you filter? If its by using the Filter By control then you are not updating the recordsource of your graph and so the graph doesnt change.

if you create a text box on frmMain for your filter value - txtFilter  & a button to  filter (maybe a toggle).

priv sub cmdSetFilter onClick()

if me.cmdSetFilter then
me.Chartage.filter ="YourField =" & me.txtFilter
me.filterOn = True
else
me.filter0n = false.
end sub

oh and include
me.ChartAge.Requery



if me.cmdSetFilter then
me.Chartage.filter ="YourField =" & me.txtFilter
me.filterOn = True
me.ChartAge.Requery

else
me.filter0n = false.
end sub


I havent used charts like this myself before so just a guess.

@Jonathan Kelly

I have a multiselect listbox and a date range in the main form which send criterias to a query (let's call it qAge) after clicking on a button.
A crosstab query is made from qAge.
The chart is made from the crosstab and the subform shows data from the crosstab.
When I filter, the subform data is correct. Shouldn't the chart's record source be okay too?

frmMain!ChartAge.Requery doesnt work?

maybe try resetting the recordsource for the chart after the setting the criteria.
frmMain!ChartAge.recordsource = ""
frmMain!ChartAge.Requery
frmMain!ChartAge.recordsource = qAge

and add
frmMain!ChartAge.Requery

Check the master/child links for the chart's subform.  They are what keep the chart in sync with the main form.

Me.ChartAge.Form.Refresh

I put together a quick mock up of a chart based on table and put them on a form.

I change the data and call

Me.ChartAge.Form.Refresh and the chart updates

db7.mdb

Thanks for your feedbacks.

It is working, but not for all values.

The subform with data still shows the correct data, but sometimes the chart is not in sync with the crosstab.

When I click on Refresh All data on the Ribbon menu when I'm getting the incorrect data, I'm getting the error:

The Microsoft Access engine does not recognize '[PM01]' as a valid field name or expression.

Is there any particular reason why it could happen?

Could it be because the headings of the crosstab are not fixed? (It's only showing column with values in in)
IncorrectOutput.png
CorrectOuput.png
Xtab.png

.refresh simply updates the existing recordset so the criteria doesn't change
.requery reruns the query which is what you want.

Also, since the subform isn't bound, requerying it isn't going to do anything.  You need to requery the object with the recordsource you want to update:

Me.frmChart.Form!OLEUnbound0.Requery

@PatHartman

Thanks for your explanation.

It's working - but if the crosstab query return an empty column (in this case PM06), I'm still getting the error:

The Microsoft Access engine does not recognize '[PM06]' as a valid field name or expression.

Is it because I've set the Y axis of the chart manually?

I tried to set up the column headings in the property sheet of the crosstab with: "PM01","PM02","PM03","PM04","PM05","PM06" in order to force the empty column to appear.

However, now I'm getting a prompt to enter the value of [Poste] and [Date] which are fields of a query the crosstab is made from.
ValuesY.png
ChartColumnUnavailable.png
ChartValues.png

We are now morphing into a different problem statement.

When you want to use crosstabs as the recordSource for a form or report, you have to make the columns fixed.

Sometimes this is easy.  For example if you always want 12 months even when one month doesn't have data you can do this by using the columns property of the crosstab ro specify a fixed set of columns.

If the columns are going to vry, the problem is more difficult to solve.  In this case, you have to determine a maximum number of columns because you really can't get away from the fact that columns will be bound to controls and the recordsource always has to return the same number of columns.  In this case though, there are several techniques that can be used to generate columns named 1-x and then use a table to convert 1 to abc, 2 to xyz, etc. to get the column titles.

Let us know more about your data.

I see.

The number of columns are indeed going to vary.

tAge:
It's where all datas are. A Type of orders may not exist for a Poste de trav within a certain range of Date. When I select that specific Poste de trav/range of Date, the column heading isn't appearing in the crosstab.
qAge:
It's a query where the criterias are sent to in order to filter (made from tAge).
AgeXtab:
It's the crosstab made from qAge.
Row headings: Age
Column headings: Type of orders
Values: Count of Age

I'm not sure if this is clear.

Thanks!
Data.png