Hello,

I am trying to create a chart whose underlying query is linked to 2 combo boxes on the same form.

I want to pass the values from the combo boxes into the chart query to allow the chart data to be manipulated easily.

The combo box uses "Row Source Type" = Table/Query. The "Row Source" references a query to extract a distinct list of data.

After making a selection for the first Combo box, I want the second combo box to populate with a data set of the query result from the previous combo box.

E.g.

The base query selects all data; Combo Box 1 contains all the unique countires in the query set, after selecting country "UK", the 2nd Combo box contains only the unique years data relevant to "UK".

Once the cobmo boxes have been selected, I want the Chart to update accordingly i.e. by passing the combo box values into the query that lies beneath the graph.

I have tried doing this using the Link Child Fields and Link Master Field properties of the Chart. It seems to work OK initially, however when I save the form and re-open, I get the following error message "3637 Cannot use the crosstab of a non-fixed column as a subquery".

I'm not sure what this error means - think it relates to the Query within the Chart row source.

Any help on this approach to dynamic charting or any other approaches would be gratefully received


Regards

Andy

Go to the Microsoft Access Developer Center page (http://www.msdn.microsoft.com/office...anding/access/) and do a search for "cascading combo boxes" to get the MS spin on this.

Then once your combo boxes are set up, you can pass the values from those combo boxes directly to the query and then refresh the chart.


mmcdonal

Thanks mmcdonal

I have managed to achieve the desired cascading of combo boxes using some VB -see below.

Private Sub OpCoName_AfterUpdate()
Me!Combo5.RowSource = "SELECT Distinct [Year] FROM [qryPortAnalysis2A] WHERE [OpCo_Id]=" & Me!OpCoName & " ORDER BY [Year];"

End Sub

I then linked the combo boxes to my chart using the Link Child/Master properties and the graph toggled between datasets correctly.

However, when I close the form and re open it a pop-up box appears asking for parameter values of the fields in the Link Child Field property of the chart.

Why is this? I have tried to populate these fields using onload

Private Sub Form_Load()

[Forms]![frmPortAnalysis2]![OpCoName] = 1
[Forms]![frmPortAnalysis2]![Combo5] = 2005

End Sub

But it still does not work - I feel like I'm alomst there!

Regards

Andy