Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Report Options / Filtering - no report displayed

Status
Not open for further replies.
Emblem1

Emblem1

Technical User
Jan 11, 2007
77
US
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 does'nt display anything.

Really the idea here is that when a selection in the combo box is made for "Records Older Than 1 Year (PM's are expired)", a different set of reports and queries are needed for the same radio button options. So I tried calling different subs. The reports all work ok when I call them manually in the DB, but accessing them via the form is the problem.

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()
'sub used for all reports except those which are for "Records Older Than 1 Year (PM's are expired)"
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()

'sub used for "Records Older Than 1 Year (PM's are expired)"
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
 
Sort by date Sort by votes
At the top of your module add this...

Code: 
Option Explicit

When you try and compile it you will see what is wrong.

The easiest way to fix it would be to pass the strReport variable to the procedures ByRef.
 
Upvote 0 Downvote
I figured this out by just reconfiguring the subs a bit. Also, alhtough the Option Explicit would tighten up the code, it would require me to go through the entire module and my coding abilites aren't quite up that caliber yet. Thanks for the input though.

Here is the final code that works....

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 stDocName As String
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 quesry 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.

Select Case cboDateOptions
Case "Records Older Than 1 Year (PM's are expired)"
Call ExpiredPMReports
Exit Sub
Case Else
If FramePMRecords.Value = 1 Then '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
ElseIf FramePMRecords.Value = 2 Then '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
ElseIf FramePMRecords.Value = 3 Then 'both
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 If
End Select


Select Case cboDateOptions
Case "No Date Range"
'GoTo DoReport
Case "Records Within 1 Year (PM's are current)"
strWhere = strField & " > " & Format(Date - 366, conDateFormat)
Me.txtStartDate.Value = Date - 366
Me.txtEndDate.Value = Date
'GoTo DoReport
Case "Specific Date Range"
'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 ExpiredPMReports()

Dim stDocName As String

If FramePMRecords.Value = 1 Then 'trunkline
Select Case frmInfoOption
Case 1 'condensed
Select Case frmSort
Case 1
stDocName = "rptPMHistoryTrunklineOnly-OlderThan1yr-Condensed-bydate"
Case 2
stDocName = "rptPMHistoryTrunklineOnly-OlderThan1yr-Condensed-byname"
End Select
Case 2 'not condensed
Select Case frmSort
Case 1
stDocName = "rptPMHistoryTrunklineOnly-OlderThan1yr-bydate"
Case 2
stDocName = "rptPMHistoryTrunklineOnly-OlderThan1yr-byname"
End Select
End Select
'GoTo DoReportExpired
ElseIf FramePMRecords.Value = 2 Then 'city
Select Case frmInfoOption
Case 1 'condensed
Select Case frmSort
Case 1
stDocName = "rptPMHistoryCityOnly-OlderThan1yr-Condensed-bydate"
Case 2
stDocName = "rptPMHistoryCityOnly-OlderThan1yr-Condensed-byname"
End Select
Case 2 'not condensed
Select Case frmSort
Case 1
stDocName = "rptPMHistoryCityOnly-OlderThan1yr-bydate"
Case 2
stDocName = "rptPMHistoryCityOnly-OlderThan1yr-byname"
End Select
End Select
'GoTo DoReportExpired
ElseIf FramePMRecords.Value = 3 Then 'both
Select Case frmInfoOption
Case 1 'condensed
Select Case frmSort
Case 1
stDocName = "rptPMHistoryBoth-OlderThan1yr-Condensed-bydate"
Case 2
stDocName = "rptPMHistoryBoth-OlderThan1yr-Condensed-byname"
End Select
Case 2 'not condensed
Select Case frmSort
Case 1
stDocName = "rptPMHistoryBoth-OlderThan1yr-bydate"
Case 2
stDocName = "rptPMHistoryBoth-OlderThan1yr-byname"
End Select
End Select
End If

On Error Resume Next
DoCmd.OpenReport stDocName, acPreview

End Sub
 
Upvote 0 Downvote
At least add this before your first procedure....

Code: 
Public stDocName As String


That explicitly declares the variable so that all the procedures can see it. Right now you are doing it implicitly which I am surprised works. I would go ahead and define it to avoid problems and avoid others not seing what you intended to do.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

JimStrand
  • Locked
  • Question
Using Report Header (Form control) as Variable in Report Expression 1
Replies
11
Views
1K
dhookom
dhookom
JimStrand
  • Locked
  • Question
Trying to Use Form Control as Variable in Report Control Source 2
Replies
5
Views
419
Andrzejek
Andrzejek
bobbyer
  • Locked
  • Question
ms accesss option group on report
Replies
4
Views
183
bobbyer
bobbyer
Moss100
  • Locked
  • Question
Stop user changing size of Access Application (not form)
Replies
1
Views
483
Andrzejek
Andrzejek
Moss100
  • Locked
  • Question
Code not working 1
Replies
5
Views
213
Darrylles
Darrylles

Part and Inventory Search

Sponsor

Back
Top