Hi all,
I am a newby here, and not even a programmer, although I have been tinkering with access since version 2; but not under the hood very often so to speak!
Courtesy of Allen Browne's (MVP) web site, I have got my hands on two tips. One to design a form for entering dates to limit a report, and another to design a form with a list box, which contains a list of saved reports to choose from, and preview them or print them. So far so good. I have built both forms as instructed and they work fine. However, I need a single form which will allow the user to select a report from the list box, specify a date range and decide whether to print or preview the report by virtue of a check box. So in other words, I need to merge both forms in to one.
The code for both forms work as an Event Procedure to the OnClick property of a command button. I have tried to merge the two sets of code together to achieve my stated aims, but have only partial success. I can select a a report from the list box, and preview them or print them; however, the date range filter is not working.
I would be most grateful if someone to take a look at the code and see where I have gone wrong.
I can't post links yet, so paste the following on to the end of AllenBrowne.com to see the code for the date range form:
/casu-08.html
and paste the following on to the end of AllenBrowne.com to see the code for the report list box form:
/ser-19.html
Here is the code as merged by me:
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.
'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
Const strcJetDate = "\#mm\/dd\/yyyy\#" 'Do NOT change it to match your local settings.
'DO set the values in the next 2 lines.
strWhere = "[Me.lstReports]"
strDateField = "[Date]" 'Put your field name in the square brackets in these quotes.
'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
'Open the report.
Debug.Print strWhere 'Remove the single quote from the start of this line for debugging purposes.
DoCmd.OpenReport Me.lstReports, IIf(Me.chkPreview.Value, acViewPreview, acViewNormal), strWhere
DoCmd.Close acForm, "frmWhatDates"
Exit_Handler:
Exit Sub
Err_Handler:
Select Case Err.Number
Case 2501 'Cancelled by user, or by NoData event.
MsgBox "Report cancelled, or no matching data.", vbInformation, "Information"
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
End Select
Resume Next
End Sub
Appreciate any help.
Thanks.
I am a newby here, and not even a programmer, although I have been tinkering with access since version 2; but not under the hood very often so to speak!
Courtesy of Allen Browne's (MVP) web site, I have got my hands on two tips. One to design a form for entering dates to limit a report, and another to design a form with a list box, which contains a list of saved reports to choose from, and preview them or print them. So far so good. I have built both forms as instructed and they work fine. However, I need a single form which will allow the user to select a report from the list box, specify a date range and decide whether to print or preview the report by virtue of a check box. So in other words, I need to merge both forms in to one.
The code for both forms work as an Event Procedure to the OnClick property of a command button. I have tried to merge the two sets of code together to achieve my stated aims, but have only partial success. I can select a a report from the list box, and preview them or print them; however, the date range filter is not working.
I would be most grateful if someone to take a look at the code and see where I have gone wrong.
I can't post links yet, so paste the following on to the end of AllenBrowne.com to see the code for the date range form:
/casu-08.html
and paste the following on to the end of AllenBrowne.com to see the code for the report list box form:
/ser-19.html
Here is the code as merged by me:
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.
'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
Const strcJetDate = "\#mm\/dd\/yyyy\#" 'Do NOT change it to match your local settings.
'DO set the values in the next 2 lines.
strWhere = "[Me.lstReports]"
strDateField = "[Date]" 'Put your field name in the square brackets in these quotes.
'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
'Open the report.
Debug.Print strWhere 'Remove the single quote from the start of this line for debugging purposes.
DoCmd.OpenReport Me.lstReports, IIf(Me.chkPreview.Value, acViewPreview, acViewNormal), strWhere
DoCmd.Close acForm, "frmWhatDates"
Exit_Handler:
Exit Sub
Err_Handler:
Select Case Err.Number
Case 2501 'Cancelled by user, or by NoData event.
MsgBox "Report cancelled, or no matching data.", vbInformation, "Information"
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
End Select
Resume Next
End Sub
Appreciate any help.
Thanks.
