CODEWITHSUNDEEP
ms-accessvbams-access-2010
god.exits
god.exits

Reputation: 21

How to reference a report from a form

I'm trying to reference a report object in some VBA code from a form object in Access 2010. I understand that in a report, I can use the syntax Reports![report name] to reference the report named "report_name", but this does not seem to work from within form code.

So my question is: How can I reference a report object from within VBA code for a form object?

Upvotes: 2

Views: 11664

Answers (2)

HansUp
HansUp

Reputation: 97131

Here is code for the click event of a command button on my form. It opens a report named rptFoo, then refers to the open form to retrieve its name property and Debug.Print that name to the Immediate Window.

Private Sub cmdReferenceReport_Click()
    DoCmd.OpenReport "rptFoo", acViewPreview
    Debug.Print Reports!rptFoo.name '<- view this in Immediate window; Ctrl+g will take you there
End Sub

Here's another way to do it.

Private Sub cmdReferenceReport_Click()
    DoCmd.OpenReport "rptFoo", acViewPreview
    Dim rpt As Report
    Set rpt = Reports!rptFoo
    Debug.Print rpt.name
    Set rpt = Nothing
End Sub

Upvotes: 5

David Alan Condit
David Alan Condit

Reputation: 1253

@HansUp when passing the report name as a string variable (rather than a string literal as you have in your code), I receive the runtime error 2451 - "The report name 'reportName' you entered is misspelled or refers to a report that isn't open or doesn't exist." Like the OP, I'm also using MS Access 2010.

My solution for referencing a report from a form using a string variable for the report name is to use parenthesis syntax: Reports(string report name variable here)

Example:

Public Sub Set_Report_RecordSource(reportName As String, datasourceQueryName As String)
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Purpose: Sets a report's recordsource programmatically. Especially useful
    ' for a report that is used by many forms.
    ' Params:
    '   reportName = Report whose RecordSource needs to be set.
    '   datasourceQueryName = The query name that will return records to display
    '   by the specified report (reportName).
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    DoCmd.OpenReport reportName:=reportName, View:=acViewDesign
    Reports(reportName).RecordSource = datasourceQueryName
    DoCmd.Close ObjectType:=acReport, ObjectName:=reportName, Save:=acSaveYes
End Sub

Upvotes: 1

Related Questions