Hi.
I am trying to modify some code I have behind a form titled 'Reports'. Form selection options allows the user to filter dates, locations, and detail level of the report via a series of radio buttons using select case code. I wanted to make some changes, so I created some new reports that are not dependent on a couple of date input boxes on the form as most of the other reports are, but instead have the date slection written into the query itself. A combo box decides the inital report, and based on that I wanted the code to call seperate subs to decide which set of reports to use for the radio button filtering. Now my report 'OK' button on the form doen'st display anything.
Thanks a bunch in advance to anyone who may help me.
Here is all the relevant code from the form.
Private Sub Command4_Click()
'This is the code used to open the correct PM Records Report with the correct filtering
If Trim(Me!cboDateOptions & "") = "" Then
MsgBox "Please select a Date Range Option.", vbOKOnly + vbCritical
cboDateOptions.SetFocus
Exit Sub
Else:
Dim strReport As String
Dim strField As String
Dim strWhere As String
Const conDateFormat = "\#mm\/dd\/yyyy\#"
' If "Records Older Than 1 Year (PM's are expired)" is selected, then a different query must be used
' that does not access the form for the dates. This allow the records that do not have
' a PM date on file to be included in the report per the specfic queries behind the individual reports.
'This code filters dates based on selections in the form
Select Case cboDateOptions
Case "No Date Range"
Call FilteredDates
'GoTo DoReport
Case "Records Older Than 1 Year (PM's are expired)"
Call ExpiredReport
'GoTo DoReport
Case "Records Within 1 Year (PM's are current)"
Call FilteredDates
strWhere = strField & " > " & Format(Date - 366, conDateFormat)
Me.txtStartDate.Value = Date - 366
Me.txtEndDate.Value = Date
'GoTo DoReport
Case "Specific Date Range"
Call FilteredDates
'Both start date and end date fields are empty
If IsNull(Trim(Me.txtStartDate)) And IsNull(Trim(Me.txtEndDate)) Then
MsgBox "You must include one or both the StartDate or EndDate." & vbCrLf & _
"If you do not want to choose a date range then select" & vbCrLf & _
"a different option in the Date Range Option drop down box.", vbOKOnly + vbCritical
txtStartDate.SetFocus
Exit Sub
'Start date is not null
ElseIf Not IsNull(Trim(Me.txtStartDate)) Then
'If end date is not null
If Not IsNull(Trim(Me.txtEndDate)) Then
'Check start date is before end date
If Not Me.txtStartDate < Me.txtEndDate Then 'return error if start date is after end date
MsgBox "Start date must be BEFORE end date.", vbOKOnly + vbCritical
txtStartDate.SetFocus
Exit Sub
Else 'Both start date and end date
strWhere = strField & " Between " & Format(Me.txtStartDate, conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
GoTo DoReport
End If
ElseIf IsNull(Trim(Me.txtEndDate)) Then
strWhere = strField & " >= " & Format(Me.txtStartDate, conDateFormat) '(Start date but no end date)
GoTo DoReport
End If
'End date is not null
ElseIf Not IsNull(Trim(Me.txtEndDate)) Then
strWhere = strField & " <= " & Format(Me.txtEndDate, conDateFormat)
'GoTo DoReport
End If
End Select
End If
DoReport:
' Debug.Print strWhere
On Error Resume Next
DoCmd.OpenReport strReport, acViewPreview, , strWhere
If Err = 2501 Then Err.Clear
End Sub
Private Sub FilteredDates()
Select Case FramePMRecords.Value ' FramePMRecords is the Option group containing each of the report names
Case 1 'trunkline
Select Case frmInfoOption
Case 1 'condensed
Select Case frmSort
Case 1
strReport = "rptPMHistoryTrunklineOnlyCondensed-bydate"
Case 2
strReport = "rptPMHistoryTrunklineOnlyCondensed-byname"
End Select
Case 2 'not condensed
Select Case frmSort
Case 1
strReport = "rptPMHistoryTrunklineOnly-bydate"
Case 2
strReport = "rptPMHistoryTrunklineOnly-byname"
End Select
End Select
strField = "[qryLocationPMHistorylastdateTrunklineOnly]![Last_PM_Date]" 'query to use for trunkline
Case 2 'city
Select Case frmInfoOption
Case 1 'condensed
Select Case frmSort
Case 1
strReport = "rptPMHistoryCityOnlyCondensed-bydate"
Case 2
strReport = "rptPMHistoryCityOnlyCondensed-byname"
End Select
Case 2 'not condensed
Select Case frmSort
Case 1
strReport = "rptPMHistoryCityOnly-bydate"
Case 2
strReport = "rptPMHistoryCityOnly-byname"
End Select
End Select
strField = "[qryLocationPMHistorylastdateCityOnly]![Last_PM_Date]" 'query to use for city
Case 3 'all locations
Select Case frmInfoOption
Case 1 'condensed
Select Case frmSort
Case 1
strReport = "rptPMHistoryBothCondensed-bydate"
Case 2
strReport = "rptPMHistoryBothCondensed-byname"
End Select
Case 2 'not condensed
Select Case frmSort
Case 1
strReport = "rptPMHistoryBoth-bydate"
Case 2
strReport = "rptPMHistoryBoth-byname"
End Select
End Select
strField = "[qryLocationPMHistorylastdate]![Last_PM_Date]" 'query to use for all
End Select
End Sub
Private Sub ExpiredReport()
Select Case FramePMRecords.Value
Case 1 'trunkline
Select Case frmInfoOption
Case 1 'condensed
Select Case frmSort
Case 1
strReport = "rptPMHistoryTrunklineOnly-OlderThan1yr-Condensed-bydate"
Case 2
strReport = "rptPMHistoryTrunklineOnly-OlderThan1yr-Condensed-byname"
End Select
Case 2 'not condensed
Select Case frmSort
Case 1
strReport = "rptPMHistoryTrunklineOnly-OlderThan1yr-bydate"
Case 2
strReport = "rptPMHistoryTrunklineOnly-OlderThan1yr-byname"
End Select
End Select
Case 2 'city
Select Case frmInfoOption
Case 1 'condensed
Select Case frmSort
Case 1
strReport = "rptPMHistoryCityOnly-OlderThan1yrCondensed-bydate"
Case 2
strReport = "rptPMHistoryCityOnly-OlderThan1yrCondensed-byname"
End Select
Case 2 'not condensed
Select Case frmSort
Case 1
strReport = "rptPMHistoryCityOnly-OlderThan1yr-bydate"
Case 2
strReport = "rptPMHistoryCityOnly-OlderThan1yr-byname"
End Select
End Select
Case 3 'all locations
Select Case frmInfoOption
Case 1 'condensed
Select Case frmSort
Case 1
strReport = "rptPMHistoryBoth-OlderThan1yrCondensed-bydate"
Case 2
strReport = "rptPMHistoryBoth-OlderThan1yrCondensed-byname"
End Select
Case 2 'not condensed
Select Case frmSort
Case 1
strReport = "rptPMHistoryBoth-OlderThan1yr-bydate"
Case 2
strReport = "rptPMHistoryBoth-OlderThan1yr-byname"
End Select
End Select
End Select
End Sub
I am trying to modify some code I have behind a form titled 'Reports'. Form selection options allows the user to filter dates, locations, and detail level of the report via a series of radio buttons using select case code. I wanted to make some changes, so I created some new reports that are not dependent on a couple of date input boxes on the form as most of the other reports are, but instead have the date slection written into the query itself. A combo box decides the inital report, and based on that I wanted the code to call seperate subs to decide which set of reports to use for the radio button filtering. Now my report 'OK' button on the form doen'st display anything.
Thanks a bunch in advance to anyone who may help me.
Here is all the relevant code from the form.
Private Sub Command4_Click()
'This is the code used to open the correct PM Records Report with the correct filtering
If Trim(Me!cboDateOptions & "") = "" Then
MsgBox "Please select a Date Range Option.", vbOKOnly + vbCritical
cboDateOptions.SetFocus
Exit Sub
Else:
Dim strReport As String
Dim strField As String
Dim strWhere As String
Const conDateFormat = "\#mm\/dd\/yyyy\#"
' If "Records Older Than 1 Year (PM's are expired)" is selected, then a different query must be used
' that does not access the form for the dates. This allow the records that do not have
' a PM date on file to be included in the report per the specfic queries behind the individual reports.
'This code filters dates based on selections in the form
Select Case cboDateOptions
Case "No Date Range"
Call FilteredDates
'GoTo DoReport
Case "Records Older Than 1 Year (PM's are expired)"
Call ExpiredReport
'GoTo DoReport
Case "Records Within 1 Year (PM's are current)"
Call FilteredDates
strWhere = strField & " > " & Format(Date - 366, conDateFormat)
Me.txtStartDate.Value = Date - 366
Me.txtEndDate.Value = Date
'GoTo DoReport
Case "Specific Date Range"
Call FilteredDates
'Both start date and end date fields are empty
If IsNull(Trim(Me.txtStartDate)) And IsNull(Trim(Me.txtEndDate)) Then
MsgBox "You must include one or both the StartDate or EndDate." & vbCrLf & _
"If you do not want to choose a date range then select" & vbCrLf & _
"a different option in the Date Range Option drop down box.", vbOKOnly + vbCritical
txtStartDate.SetFocus
Exit Sub
'Start date is not null
ElseIf Not IsNull(Trim(Me.txtStartDate)) Then
'If end date is not null
If Not IsNull(Trim(Me.txtEndDate)) Then
'Check start date is before end date
If Not Me.txtStartDate < Me.txtEndDate Then 'return error if start date is after end date
MsgBox "Start date must be BEFORE end date.", vbOKOnly + vbCritical
txtStartDate.SetFocus
Exit Sub
Else 'Both start date and end date
strWhere = strField & " Between " & Format(Me.txtStartDate, conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
GoTo DoReport
End If
ElseIf IsNull(Trim(Me.txtEndDate)) Then
strWhere = strField & " >= " & Format(Me.txtStartDate, conDateFormat) '(Start date but no end date)
GoTo DoReport
End If
'End date is not null
ElseIf Not IsNull(Trim(Me.txtEndDate)) Then
strWhere = strField & " <= " & Format(Me.txtEndDate, conDateFormat)
'GoTo DoReport
End If
End Select
End If
DoReport:
' Debug.Print strWhere
On Error Resume Next
DoCmd.OpenReport strReport, acViewPreview, , strWhere
If Err = 2501 Then Err.Clear
End Sub
Private Sub FilteredDates()
Select Case FramePMRecords.Value ' FramePMRecords is the Option group containing each of the report names
Case 1 'trunkline
Select Case frmInfoOption
Case 1 'condensed
Select Case frmSort
Case 1
strReport = "rptPMHistoryTrunklineOnlyCondensed-bydate"
Case 2
strReport = "rptPMHistoryTrunklineOnlyCondensed-byname"
End Select
Case 2 'not condensed
Select Case frmSort
Case 1
strReport = "rptPMHistoryTrunklineOnly-bydate"
Case 2
strReport = "rptPMHistoryTrunklineOnly-byname"
End Select
End Select
strField = "[qryLocationPMHistorylastdateTrunklineOnly]![Last_PM_Date]" 'query to use for trunkline
Case 2 'city
Select Case frmInfoOption
Case 1 'condensed
Select Case frmSort
Case 1
strReport = "rptPMHistoryCityOnlyCondensed-bydate"
Case 2
strReport = "rptPMHistoryCityOnlyCondensed-byname"
End Select
Case 2 'not condensed
Select Case frmSort
Case 1
strReport = "rptPMHistoryCityOnly-bydate"
Case 2
strReport = "rptPMHistoryCityOnly-byname"
End Select
End Select
strField = "[qryLocationPMHistorylastdateCityOnly]![Last_PM_Date]" 'query to use for city
Case 3 'all locations
Select Case frmInfoOption
Case 1 'condensed
Select Case frmSort
Case 1
strReport = "rptPMHistoryBothCondensed-bydate"
Case 2
strReport = "rptPMHistoryBothCondensed-byname"
End Select
Case 2 'not condensed
Select Case frmSort
Case 1
strReport = "rptPMHistoryBoth-bydate"
Case 2
strReport = "rptPMHistoryBoth-byname"
End Select
End Select
strField = "[qryLocationPMHistorylastdate]![Last_PM_Date]" 'query to use for all
End Select
End Sub
Private Sub ExpiredReport()
Select Case FramePMRecords.Value
Case 1 'trunkline
Select Case frmInfoOption
Case 1 'condensed
Select Case frmSort
Case 1
strReport = "rptPMHistoryTrunklineOnly-OlderThan1yr-Condensed-bydate"
Case 2
strReport = "rptPMHistoryTrunklineOnly-OlderThan1yr-Condensed-byname"
End Select
Case 2 'not condensed
Select Case frmSort
Case 1
strReport = "rptPMHistoryTrunklineOnly-OlderThan1yr-bydate"
Case 2
strReport = "rptPMHistoryTrunklineOnly-OlderThan1yr-byname"
End Select
End Select
Case 2 'city
Select Case frmInfoOption
Case 1 'condensed
Select Case frmSort
Case 1
strReport = "rptPMHistoryCityOnly-OlderThan1yrCondensed-bydate"
Case 2
strReport = "rptPMHistoryCityOnly-OlderThan1yrCondensed-byname"
End Select
Case 2 'not condensed
Select Case frmSort
Case 1
strReport = "rptPMHistoryCityOnly-OlderThan1yr-bydate"
Case 2
strReport = "rptPMHistoryCityOnly-OlderThan1yr-byname"
End Select
End Select
Case 3 'all locations
Select Case frmInfoOption
Case 1 'condensed
Select Case frmSort
Case 1
strReport = "rptPMHistoryBoth-OlderThan1yrCondensed-bydate"
Case 2
strReport = "rptPMHistoryBoth-OlderThan1yrCondensed-byname"
End Select
Case 2 'not condensed
Select Case frmSort
Case 1
strReport = "rptPMHistoryBoth-OlderThan1yr-bydate"
Case 2
strReport = "rptPMHistoryBoth-OlderThan1yr-byname"
End Select
End Select
End Select
End Sub
