Worked in 2003, crashes in 2007

[Emblem1]

Hi All.

I have a form in which a user can select records to be placed into a report via dates in a table. The code worked great in Access 2003, but crashes the database every time in Access 2007. Here is the code - what do I need to modify? I am pretty sure the error lies in the "Select Case cboDateOptions" -about halfway down.

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 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 is done with a seperate query built for the report. All others use the code below

Select Case cboDateOptions
Case "Records Older Than 1 Year (PM's are expired)"
Call ExpiredPMReports
Exit Sub
Case "Only Locations with NO PM Record"
stDocName = "rptNO_PM_Record"
DoCmd.OpenReport stDocName, acPreview
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 IsNull(Trim(Me.txtStartDate)) Then
GoTo DoReport
Else:
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
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

Thanks for any help you can provide...

 

[genomon]

This can happen when the report is set to a printer that the new Access installation doesn't recognize. Make sure you have a conventional printer set to default and that you can print to it successfully.

That being said, have a look in Access help about using breakpoints, stepping through code, using Debug.Print, and On Error to display meaningful information from the Err object.

There are also some great FAQs here about debugging VBA code.

An làmb a bheir, ‘s i a gheibh.

 

[Emblem1]

So, just getting back to this. I did some more digging into the database. I found that the error actually lies with the report. Ugh. Running the report manually also causes the same problem. I will repost this thread over there.