I've been using Allen Browne's Method 2: Form for entering the dates on my reports. This works fine. I have a new report with multiple subreports. Is it somehow possible to link them all to the same date entered on the one (single) form?
This is the form code to accept the date and open the main report, based on the described method above.
My subreports are as follows; each have the required textboxes in the report header p/ Allen's instructions:
rptSum1MeterReadings
rptSum2AirlineTransactions
rptSum3TruckVolume
rptSum4TankVolume
This is the form code to accept the date and open the main report, based on the described method above.
My subreports are as follows; each have the required textboxes in the report header p/ Allen's instructions:
rptSum1MeterReadings
rptSum2AirlineTransactions
rptSum3TruckVolume
rptSum4TankVolume
Code:
' Set focus on another control allowing the textboxes to update
Me.txtStartDate.SetFocus
Me.txtEndDate.SetFocus
'On Error GoTo Err_Handler 'Remove the single quote from start of this line once you have it working.
'Purpose: Filter a report to a date range.
'Documentation: http://allenbrowne.com/casu-08.html
'Note: Filter uses "less than the next day" in case the field has a time component.
Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#" 'Do NOT change it to match your local settings.
'DO set the values in the next 3 lines.
strReport = "rptSum" 'Put your report name in these quotes.
strDateField = "[TransactionDate]" 'Put your field name in the square brackets in these quotes.
lngView = acViewPreview 'Use acViewNormal to print instead of preview.
'Build the filter string.
If IsDate(Me.txtStartDate) Then
strWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
End If
If IsDate(Me.txtEndDate) Then
If strWhere <> vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
End If
'Close the report if already open: otherwise it won't filter properly.
If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If
'Open the report.
'Debug.Print strWhere 'Remove the single quote from the start of this line for debugging purposes.
' Close form builder
DoCmd.Close acForm, "frmReportBuildSum", acSaveNo
' Open report
DoCmd.OpenReport strReport, lngView, , strWhere
Exit_Handler:
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
End If
Resume Exit_Handler