Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old April 7th, 2005, 04:34 AM
Registered User
Join Date: Apr 2005
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Changing Access Charts Using Combo Boxes


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.


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


Old April 7th, 2005, 06:59 AM
Friend of Wrox
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts

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.

Old April 7th, 2005, 08:47 AM
Registered User
Join Date: Apr 2005
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts

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!



Similar Threads
Thread Thread Starter Forum Replies Last Post
3 combo boxes ttkt Beginning PHP 0 July 2nd, 2005 02:20 PM
Combo boxes socoolbrewster Access 1 March 4th, 2004 09:28 AM
Combo Boxes tjs206 VB Databases Basics 2 December 10th, 2003 05:20 PM
Combo Boxes Louisa Beginning VB 6 2 September 10th, 2003 09:26 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.