Updating Chart in Report based on input from form with VBA

[srk2011]

Hi all,

I am new to both Access and VBA, so here goes...

I have an MS Graph Chart in a report that I would like to update based on input from a form. The form has several checkboxes. The user clicks on them to select fields to be used in a SELECT statement for a Make Table query.

I would like to base the data shown in the Graph on the results of this Make Table query after the report loads.

For example, the "HN" Checkbox....if it is checked, I would for the chart to graph one set of data, and if unchecked, graph another

The code for the report loading event is as follows:

Public Sub Report_Load()

If Forms("ControlForm").Controls("HN").Value = -1 Then
Me.Graph4.RowSourceType = "TABLE/QUERY"
Me.Graph4.RowSource = "TRANSFORM Sum([HN]) AS [SumOfHN] SELECT (Format([Date],'DDDDD')) FROM " _
& "[Persons] GROUP BY (Int([Date])),(Format([Date],'DDDDD')) PIVOT [PersonID];"
Me.Graph4.Requery
Else
Me.Graph4.RowSourceType = "TABLE/QUERY"
Me.Graph4.RowSource = "TRANSFORM Sum([VMI]) AS [SumOfVMI] SELECT (Format([Date],'DDDDD')) FROM " _
& "[Persons] GROUP BY (Int([Date])),(Format([Date],'DDDDD')) PIVOT [PersonID];"
Me.Graph4.Requery
End If

End Sub

I inserted the chart and the built the Chart ("Chart4") on the Report using the Design Wizard for one instance of the above IF statement.

However, if I run it so as the satisfy the other condition, the chart does not change, still only displaying it as I initially built it. I must be doing something wrong here, I assume I should not built the chart using the Design Wizard, but of course, I could be completely on the wrong track.

Thanks!