I have an almost exact same function in my database. I have a custom dialog
box that asks for start date and end date, a button that says Preview, and
one for cancel. I found a great source and code created by adapted from Allen
Browne. It works perfectly!
Copy and paste the vba into your onclick event for the preview button(or OK
button in you case, remember to change the name cmdPreview to Ok or whatever
yours is).
I have the VBA code for my preview button as
Option Compare Database
Option Explicit
Private Sub cmdPreview_Click()
'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 = "Tech Report" 'Put your report name in these quotes.
strDateField = "[CallDate]" '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.
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
End Sub
Hope this helps!
PS
HotRodSue said:
My mistake, the form is NOT open when I print. The Report is open when I
print.
The form is a custom dialog box. the form I created is a custom dialog box.
Once the Start Date and End Date are entered, I click the OK command button.
The form closes and the Report opens in Report Preview, ready to print.
I just did a test where I had the report open in Report Preview. Then went
back and opened my form (dialog box), entered the Start and End Date and did
NOT click OK, leaving the form open.
The Report then printed with the date range rather than the error.
Do you have any suggestions how I may get this working properly?
HotRodSue said:
Yes the form is still open when I print. It has the Date Range displayed.
Just can't figure why it's not printing as displayed in the Reporter Header?
Bob Larson said:
Is the form still open when you print?
--
Thanks,
Bob Larson
Access MVP
Free Access Tutorials and Resources:
http://www.btabdevelopment.com
A report is opened via a form that requires a Start Date and End Date. I
would like the date range to show up in the report header.
I created an unbound text box in the header of the report and in the
control
source referred to form that generates the report as such:
=[Forms]![AskForDates]![txtStartDate]
The report opens and displays the date range. However, when I print the
report the unbound text box prints the following:
#Name?
Any suggestion on how to resolve this?
