I have the below code in a form to filter a report. Date fields to allow the user to filter to a certain date set and location field to allow them to filter to a particular.
I want to add code so that 1) if the user presses 'enter' on their keyboard after inputting a filter, it opens the report and then 2) if the user has inputted no filters but hits the button or presses enter, then the form does not open the report.
I've tried looking for some guides online for both questions and had no luck. Any advice?
Code:
Private Sub btnOpenReport_Click()On Error GoTo Err_Handler
Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Dim strFilter As String
Set frm = Forms!frmSearchTool
strFilter = ""
Const strcJetDate = "\#mm\/dd\/yyyy\#"
strReport = "rptSearchReport"
strDateField = "[SubmissionDate]"
lngView = acViewReport
'Date Filter
If IsDate(Me.txtStartDate) Then
strWhere = "(" & strDateField & " >= " & Format(DateValue(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
'Location
If Not IsNull(frm!ComboLocation) Then
If strWhere <> "" Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "[Location] ='" & frm!ComboLocation & "'"
End If
If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If
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