Use the reports Open event to invoke your Date Range selection dialog (complete with CustomerID, Beginning Date, and Ending Date text boxes) with something like:
Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "frmReportDateRange", , , , , acDialog, "Set Date Range"
If Not IsLoaded("frmReportDateRange") Then
Cancel = True
Then in the Control Source property of your textbox in your report header, place something like:
="Orders Recieverd from " & [Forms]![frmReportDateRange]![txtCustomerID] & “, “ & “Between “ & [Forms]![frmReportDateRange]![txtBeginningDate] & " And " & [Forms]![frmReportDateRange]![txtEndingDate]
Have your Date Range selection form open as a modal form, place a report preview command button on it, then hide the form (while keeping it loaded) when the the preview button is clicked with something like:
Private Sub cmdPreview_Click()
On Error GoTo HandleErr
If IsNull([txtBeginningDate]) Or IsNull([txtEndingDate]) Then
MsgBox "You must enter both beginning and ending dates."
DoCmd.GoToControl "txtBeginning Date"
If [txtBeginningDate] > [txtEndingDate] Then
MsgBox "Ending date must be greater than Beginning date."
Me.Visible = False
Select Case Err
MsgBox Err & ": " & Err.Description, vbCritical, _
"Error in Form_frmReportDateRange.Preview_Click"
Might also want to add a NoData event to the report in case no data is available for the selected date range:
Private Sub Report_NoData(Cancel As Integer)
MsgBox "There is no data for this report. Canceling report..."
Cancel = -1
Private Sub Report_Close()
DoCmd.Close acForm, "frmReportDateRange"
And here's a generic IsLoaded routine called by the reports Open event:
Function IsLoaded(ByVal strFormName As String) As Integer
' Returns True if the specified form is open in Form view or Datasheet view.
Const conObjStateClosed = 0
Const conDesignView = 0
If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> conObjStateClosed Then
If Forms(strFormName).CurrentView <> conDesignView Then
IsLoaded = True
Somethin' like that...