This one has me stumped. I have a form that contains two text fields to enter a date range, and three multiple-select list boxes. When a "generate report" button is clicked, I would like the code to display a report that is filtered by the date range entered, and all the data selected from the three list boxes.
I have the report completed and it works perfectly when the date ranges are entered, however, when I select something from one of the list boxes, I get an error (see attached - the dates in the attachment are because I entered those dates in the range).
Here is my code and I'm sure I have something wrong in the section that has the comment: There is something wrong between these two lines:
If I remove the section between the indicated comments, then the code and report runs fine, but only uses the date filters.
Also, I'm afraid I cannot update the database due to sensitive information; however, if needed, I can create a sample. I'm hoping someone will see the problem in the code and have a solution though.
Thank you for your time. Any help is greatly appreciated!
I have the report completed and it works perfectly when the date ranges are entered, however, when I select something from one of the list boxes, I get an error (see attached - the dates in the attachment are because I entered those dates in the range).
Here is my code and I'm sure I have something wrong in the section that has the comment: There is something wrong between these two lines:
Code:
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
Dim lngView As Long
Dim ctl As Control
Dim VarItem As Variant
Const strcJetDate = "\#mm\/dd\/yyyy\#" 'Do NOT change this format.
strReport = "rptRecentChanges"
strDateField = "[EffectiveDate]"
lngView = acViewReport '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
'THERE IS SOMETHING WRONG BETWEEN THESE TWO LINES
Set ctl = Me.lstDrugProduct
For Each VarItem In ctl.ItemsSelected
strWhere = strWhere & ctl.ItemData(VarItem) & ","
Next VarItem
Set ctl = Me.lstDescription
For Each VarItem In ctl.ItemsSelected
strWhere = strWhere & ctl.ItemData(VarItem) & ","
Next VarItem
Set ctl = Me.lstRecordType
For Each VarItem In ctl.ItemsSelected
strWhere = strWhere & ctl.ItemData(VarItem) & ","
Next VarItem
'THERE IS SOMETHING WRONG BETWEEN THESE TWO LINES
'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 SubIf I remove the section between the indicated comments, then the code and report runs fine, but only uses the date filters.
Also, I'm afraid I cannot update the database due to sensitive information; however, if needed, I can create a sample. I'm hoping someone will see the problem in the code and have a solution though.
Thank you for your time. Any help is greatly appreciated!
Attachments
-
error1.JPG
