Access subreport with multiple records - referencing each record in VBA?

Question

I have an Access report, which has a nested subreport. In the subreport, there is a chart, which I need to reference via VBA in order to adjust the axes.

The problem I have is that the subreport has multiple records, which fit on the same page. My VBA is triggered within the OnPrint event of the detail section of the main report (which is where the subreport containing the chart is located), and it seems that the reference to the chart is only referencing the first chart on each page.

My code is as follows:

If (Reports![Desired_Future_State Overview]![Desired_Future_State_Metric Programme Breakdown].Report![Walkup Chart].Object.Axes(2).MaximumScale) = 0 Then
      Reports![Desired_Future_State Overview]![Desired_Future_State_Metric Programme Breakdown].Report![Walkup Chart].Object.Axes(2).ReversePlotOrder = True
End If

I can't see any info in the MS documentation regarding how to reference multiple instances using this syntax - is this possible, and how would I go about it?

I'm referring to instances of the chart - because the subreport has multiple records, there is more than 1 chart on a page. The reference syntax above only executes for the first chart on each page (it's in the OnPrint event of the main report). — user3734067
Is your issue finding the controls or the code you should run on them when you do? If the prior, you could possibly loop through each control (For Each ctl In Me.Controls), checking on the ControlType for each one. Check this documentation: msdn.microsoft.com/en-us/library/office/…. If the control type is a certain type (acSubform, in your case) plus some other identifying feature(s), you could run your code on that. Good luck. — VBlades

Ravi Yenugu Ravi Yenugu · Accepted Answer · 2014-06-12T17:33:44

What I usually do, is refer to the specific chart in the Reports Code ,in the following example I used Detail_Format of the report and referenced the Graph named GraphName

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
 Dim objchart As Object
 Dim objAxis As Object

 Set objchart = Me!GraphName.Object
 Set objAxis = objchart.Axes(2)

 Dim Maxscale As Double
 Dim MinScale As Double

 Maxscale = Forms.MyForm.Maxscale
 MinScale = Forms.MyForm.MinScale

 objAxis.MaximumScale = Maxscale
 objAxis.MinimumScale = MinScale

End Sub

Access subreport with multiple records - referencing each record in VBA?

1 Answers