Printing report: avoiding report with #ERROR 1

[MrKABC]

Hello!

I am using a form to call up a report, and sometimes there are no records that fit the criteria specified in the form, (TEAM and YEAR) and the report loads but the fields contain #ERROR in them (obviously because no records were retrieved.) What I would like to do is have a message box state that there are no records to be found, or something like that.

Here is the code I currently have attached to the PRINT button: (edited down for space)

Case 4 ' List Box 4 selected,
'Need YEAR, and TEAM
If (cmbYear.Value = "&quot Or IsNull(cmbYear.Value) Then
strMsg = "You must select a Year from the dropdown list!"
intResponse = MsgBox(strMsg, vbOKOnly, "ERROR!&quot
cmbYear.SetFocus
End
ElseIf (cmbTeamName.Value = "&quot Or IsNull(cmbTeamName.Value) Then
strMsg = "You must select a Team from the dropdown list!"
intResponse = MsgBox(strMsg, vbOKOnly, "ERROR!&quot
cmbTeamName.SetFocus
End
Else
DoCmd.OpenReport "RptYTDTeamSummary", ReportDest, , "Forms![PrintForm]![CmbTeamName] = [MainTbl]![TeamID] and Forms![PrintForm]![CmbYear] = [MainTbl]![AuditYear]"
End If

This is for ACCESS 97. Thanks in advance!

Mr. K

 

[jiqjaq]

You need to put code in the Report_NoData section of the report. This section is only executed after the report is opened and there are no records. You can capture your error there and then cancel the report (Docmd.Cancel or Cancel = True).

 

[jfischer]

Reports have an event for On No Data. You can use this event to display a message and then cancel the report.

In your Form you should put an On Error Resume Next statement just before you open the Report. This will prevent the report cancellation from causing an error in the Form.

 

[MrKABC]

Thanks for the helpful answers! I entered the on no data info like you folks requested, problem is my form wigs out, think I have the syntax wrong to trap the error. Here is what I have in the report:

Private Sub Report_NoData(Cancel As Integer)
On Error GoTo ReportNoDataError

'generates a message box saying there are no records
strMsg = "Sorry, there are no audits on file for this team."
intResponse = MsgBox(strMsg, vbOKOnly, "The File Inspector!&quot
DoCmd.Close

Exit_ReportNoDataError:
Exit Sub

ReportNoDataError: 'error routine if no records

If Err = 2046 Or Err = 2585 Then
Resume Exit_ReportNoDataError
Else
MsgBox Err.Description
Resume Exit_ReportNoDataError
End If

End Sub

Um, it does the box OK but the report still opens... Did I do something wrong? Thank you again for your help!

Mr. K

 

[Lightning]

Try this Code:

Private Sub Report_NoData(Cancel As Integer)
' Display a message if user enters criteri for which there are no records,
' and don't preview or print report.

Dim strMsg As String, strTitle As String
Dim intStyle As Integer

strMsg = "Sorry, there are no audits on file for this team."
intStyle = vbOKOnly
strTitle = "The File Inspector!"

MsgBox strMsg, intStyle, strTitle
Cancel = True
Call Report_Deactivate


End Sub

Private Sub Report_Deactivate()
'If the form PrintForm is open, restore the form to normal size,
'otherwise exit and return to database window.

If IsLoaded("PrintForm&quot Then
Forms!printForm.SetFocus
DoCmd.Restore
End If

End Sub


If there is no data for your report, this code will then call the report deactivate event, shut the report and set the focus back to your form. Note: the code assumes that the Form is still loaded in the background. If the Form has been closed the IsLoaded function will return False and simply close the report.

If you haven't installed the ISLoaded function, copy it from the Northwind Sample database into a new module.

HTH
Lightning

 

[MrKABC]

Lightning, thank you for the great assist! There is only one final problem, I think it is in trapping the error message. It is after the report disappears, and control reverts to PrintForm. I get an error message that states:

"The OpenReport Action was canceled.

You used a method of the DoCmd object to carry out an action in Visual Basic, but then clicked Cancel in a dialog box.

For example, you used the Close method to close a changed form, then clicked Cancel in the dialog box that asks if you want to save changes you made to the form."

What???? I am thoroughly confused. How do I squash this error message?

Thank YOUUUUUUUUUUUU!!! <grin!>

Mr K.

 

[Lightning]

This message is appearing because you are using the Docmd.OpenReport command to open the report. Because there is no data, the Cancel=True action is then generating the error message.

To get rid of this message, turn Warnings off and on in your Command Button code. At the beginning of the code:
DoCmd.SetWarnings False

At the end of the code:
DoCmd.SetWarnings True

HTH
Lightning

 

[MrKABC]

Thanks again Lightning!

Sounds great, unfortunately I still get the warning message even though I set the warnings to FALSE. Here is the code:

Case 4 ' List Box 4 selected, YTD Team Summary
'Need YEAR, and TEAM
If (cmbYear.Value = &quot;&quot Or IsNull(cmbYear.Value) Then
strMsg = &quot;You must select a Year from the dropdown list!&quot;
intResponse = MsgBox(strMsg, vbOKOnly, &quot;The File Inspector!&quot
cmbYear.SetFocus
End
ElseIf (cmbTeamName.Value = &quot;&quot Or IsNull(cmbTeamName.Value) Then
strMsg = &quot;You must select a Team from the dropdown list!&quot;
intResponse = MsgBox(strMsg, vbOKOnly, &quot;All Hail Moldy Mike and the Big 'D'!&quot
cmbTeamName.SetFocus
End
Else
DoCmd.SetWarnings False
DoCmd.OpenReport &quot;RptYTDTeamSummary&quot;, ReportDest, , &quot;Forms![PrintForm]![CmbTeamName] = [MainTbl]![TeamID] and Forms![PrintForm]![CmbYear] = [MainTbl]![AuditYear]&quot;
End If

Did NOT reset warnings to TRUE as a test, no difference. Maybe I should disable warnings in the report code also?

Thanks a million!

 

[Lightning]

Sorry, I misled you a bit there. I was thinking that this is a warning message, but Access treats this as an Error message, and turning warnings off does not stop error messages displaying.

So, scratch the SetWarnings code lines. BUT before you do, make sure that SetWarning is set to true. Disabling Warnings anywhere in the code turns warnings off for Access, not just that code or object.

Now back to your problem. Since this is an error message, you need to trap for that error. After a bit of digging I found that this error is Error number 2501. So you would need to trap for this error number

Something like

If Err.Number = 2501 Then
Resume Next
End if

or whatever action you need.

HTH
Lightning

 

[MrKABC]

Lightning:

It worked like a charm! You are in fact THE man!

Thank you again for all of your help.

Mr. K

 

[Lightning]

Glad it Helped.