Passing Information to a Report
Often, I see examples where people pass information between Forms and Reports using Opening Arguments “openArgs” … I did this myself until I happened upon an article, I think it was from “Smart Access” which explained a very satisfying way of Passing information around.
The method still employs Opening Arguments “openArgs” but instead of using the “openArgs” to pass data, it is used to pass the Form Name, the name of the Form from where the data originates, and of course it will also be the form Opening the Report. With the Form name now in the Report, the Report knows where to look for the information it needs.
Pass Info In to a Report
Pass Info In to a Report
Video 1 (3:41)
The open-args method, the method of passing lots of information in a string, separated by delimiters and passing them through with the openargs, it’s quite a challenging method of handling data.
The method explained in this video is far superior. This method makes the data available in the “Calling Form” the Form that is Opening the Report. The Report is directed to extract the necessary information from the “Calling Form”.
With this method you have a greater degree of control over the information. It’s logical, it’s easy, especially if you need to add or remove information.
… …
Passing Information to a Report - The FORM CODE
'======================== this code in the Form ======================
Option Compare Database
Option Explicit
Private mstrRptHeaderLabel As String
Private mstrRptSQL As String
'<<<<<<<<<<<<<< --- PROPERTY STATEMENTS --- >>>>>>>>>
Property Let prpRptHeaderLabel(strHeader As String)
mstrRptHeaderLabel = strHeader
End Property 'prpRptHeaderLabel Let
Property Get prpRptHeaderLabel() As String
prpRptHeaderLabel = mstrRptHeaderLabel
End Property 'prpRptHeaderLabel Get
Property Get prpRptSQL() As String
prpRptSQL = mstrRptSQL
End Property 'prpRptSQL Get
Property Let prpRptSQL(strHeader As String)
mstrRptSQL = strHeader
End Property 'prpRptSQL Let
Private Sub btnOpenRport_Click()
Me.prpRptSQL = "SELECT ID, T1, T2, T3, T4 FROM Table1 WHERE (((T3)='ww'))"
Me.prpRptHeaderLabel = "REPORT HEADER FROM FORM"
Dim strRptName As String
strRptName = "rptTable1"
DoCmd.OpenReport strRptName, acViewReport, , , , Me.Name
With Reports(strRptName)
.Caption = "I CAN CHANGE THE CAPTION"
End With
End Sub
'======================== this code in the Form ======================
Passing Information to a Report - The REPORT CODE
'======================== this code in the Report ======================
Option Compare Database
Option Explicit
Private Sub Report_Open(Cancel As Integer)
If Len(Me.OpenArgs) > 0 Then
With Forms(Me.OpenArgs)
Me.RecordSource = .prpRptSQL
Me.lblRptHeader.Caption = .prpRptHeaderLabel
Me.lblShowSQL.Caption = .prpRptSQL
End With
End If
End Sub
'======================== this code in the Report ======================
