How do I connect a combo box with a pivotchart?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want the user to be able to change the x axis(category) field of a
pivotChart by selecting from a combo box in the main form, but the choices
are the fields of a query, not rows of one field. Is this possible, if not
what other options do I have to control the level of pivoting by the user?
thanx
 
Hi gebuh,

If the field names in your query are static, you can simply create a lookup
table manually to use as the rowsource of your dropdown. If they are
dynamic, you can loop through the field names with DAO, similar to the
following, on the form's On_Current event:

Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim fld As DAO.Field
Dim sql As String
Dim strName As String

' clear old values
sql = "DELETE * FROM YourLookupTable"
DoCmd.RunSQL sql

Set db = CurrentDb()
Set tbl = db.TableDefs("YourQuery")
For Each fld In tbl.Fields
strName = fld.Name

sql = "INSERT INTO YourLookupTable VALUES (" & strName & ")"
DoCmd.RunSQL sql

Next fld

Me.YourCombobox.RowSource = YourLookupTable


NOTE - the above code is untested, and will require you to insert the proper
table and query names.
 
Thanx for replying SusanV but I'm not sure this is what I'm looking for- I
can get the combo box in the main form to display the fields I want by
manipulating "row source type" in the combo box properties, but I want to tie
this combo box to the category field (x axis) of the pivotChart in the
subform so that the user changes the x axis based on their selection in the
combo box.
 
Back
Top