How to handel an error 2501 prompted by msgbox code when no records are returned in a report?

[Frank's Newsgroups]

BlankI have a report with as message box in it that returns when no records
are found. Specifically, how do I add error-handling to the procedure in
order to trap error 2501.

my code is as follows and placed in the [On no data] property of the report:

MsgBox " Your filter selection option has no records to display."
Cancel = True

 

[PC Datasheet]

At the beginning of your code put this line:
On Error Goto ErrorHandler

At the very end of your code add:
ExitHere:
Exit Sub
ErrorHandler:
If Err.Number = 2501 Then
MsgBox " Your filter selection option has no records to display."
Cancel = True
Else
MsgBox Err.Description,,"Error# " & Err.Number
End If
Resume ExitHere

 

[Dirk Goldgar]

BlankI have a report with as message box in it that returns when no
records are found. Specifically, how do I add error-handling to the
procedure in order to trap error 2501.

my code is as follows and placed in the [On no data] property of the
report:

MsgBox " Your filter selection option has no records to display."
Cancel = True

I assume by the above that you mean the OnNoData property is set to
"[Event Procedure]", and your code is in an event procedure for this
event, so it looks like this:

Private Sub Report_NoData(Cancel As Integer)
MsgBox " Your filter selection option has no records to
display."
Cancel = True
End Sub

This being the case, you need to trap the 2501 error (action cancelled)
in the code procedure that opens the report, not in any event procedure
in the report itself. So if you are opening the report with some code
along the lines of:

DoCmd.OpenReport "ReportName", acViewPreview, , strCriteria

then you need an error-handler there:

On Error GoTo Err_Handler

' ... other code ...

DoCmd.OpenReport "ReportName", acViewPreview, , strCriteria

' ... maybe other code ...

Exit_Point:
Exit Sub

Err_Handler:
If Err.Number = 2501 Then
Resume Next
Else
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point
End If

End Sub

 

[Frank's Newsgroups]

Dirk:

Thanks for reply. Still having a bit of trouble. I call the open report from
a modul. There is a command button on a form that that when clicked calls a
report after filtering the records. Some filitered results have no records.
I'm trying to display the message box without the runtime error 2501 when a
report has no records

The code on the on click event of
the button is
____________________________________________________________________________
______________

Private Sub btnPrintFilteredReport_Click()
Dim frm As Form
Dim strReportName As String

Set frm = Me
strReportName = Me!lstReports


Call frankPrintFilterReport(frm, strReportName)


End Sub
____________________________________________________________________________
______________
The code for the
Module is:


Function frankPrintFilterReport(frm As Form, strReportName As String)

Dim strCriteria As String
stFilterValue = frm.cmbFilterValue
stConvFilterValue = StrConv(stFilterValue, vbLowerCase)
If blnYesNo = True Then
If stFilterValue = "yes" Or stFilterValue = "true" Or
stFilterValue = "on" Then
frm.cmbFilterValue = -1
ElseIf stFilterValue = "no" Or stFilterValue = "false" Or
stFilterValue = "off " Then
frm.cmbFilterValue = 0
Else
End If
End If
strCriteria = "((([" & stFormDataSource & "].[" & stFilterField & "]) =
Forms.[" & frm.Name & "].cmbFilterValue))"

DoCmd.OpenReport strReportName, acViewPreview, ,
WhereCondition:=strCriteria

End Function


____________________________________________________________________________
_______________

Any furhter assistance is greatly appreciated.

BlankI have a report with as message box in it that returns when no
records are found. Specifically, how do I add error-handling to the
procedure in order to trap error 2501.

my code is as follows and placed in the [On no data] property of the
report:

MsgBox " Your filter selection option has no records to display."
Cancel = True

I assume by the above that you mean the OnNoData property is set to
"[Event Procedure]", and your code is in an event procedure for this
event, so it looks like this:

Private Sub Report_NoData(Cancel As Integer)
MsgBox " Your filter selection option has no records to
display."
Cancel = True
End Sub

This being the case, you need to trap the 2501 error (action cancelled)
in the code procedure that opens the report, not in any event procedure
in the report itself. So if you are opening the report with some code
along the lines of:

DoCmd.OpenReport "ReportName", acViewPreview, , strCriteria

then you need an error-handler there:

On Error GoTo Err_Handler

' ... other code ...

DoCmd.OpenReport "ReportName", acViewPreview, , strCriteria

' ... maybe other code ...

Exit_Point:
Exit Sub

Err_Handler:
If Err.Number = 2501 Then
Resume Next
Else
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point
End If

End Sub

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Dirk:

Thanks for reply. Still having a bit of trouble. I call the open
report from a modul. There is a command button on a form that that
when clicked calls a report after filtering the records. Some
filitered results have no records. I'm trying to display the message
box without the runtime error 2501 when a report has no records

The code on the on click
event of the button is
________________________________________________________________________
____
______________

Private Sub btnPrintFilteredReport_Click()
Dim frm As Form
Dim strReportName As String

Set frm = Me
strReportName = Me!lstReports


Call frankPrintFilterReport(frm, strReportName)


End Sub
________________________________________________________________________
____
______________
The code
for the Module is:


Function frankPrintFilterReport(frm As Form, strReportName As String)

Dim strCriteria As String
stFilterValue = frm.cmbFilterValue
stConvFilterValue = StrConv(stFilterValue, vbLowerCase)
If blnYesNo = True Then
If stFilterValue = "yes" Or stFilterValue = "true" Or
stFilterValue = "on" Then
frm.cmbFilterValue = -1
ElseIf stFilterValue = "no" Or stFilterValue = "false" Or
stFilterValue = "off " Then
frm.cmbFilterValue = 0
Else
End If
End If
strCriteria = "((([" & stFormDataSource & "].[" & stFilterField &
"]) = Forms.[" & frm.Name & "].cmbFilterValue))"

DoCmd.OpenReport strReportName, acViewPreview, ,
WhereCondition:=strCriteria

End Function


________________________________________________________________________
____
_______________

Any furhter assistance is greatly appreciated.

BlankI have a report with as message box in it that returns when no
records are found. Specifically, how do I add error-handling to the
procedure in order to trap error 2501.

my code is as follows and placed in the [On no data] property of the
report:

MsgBox " Your filter selection option has no records to display."
Cancel = True

I assume by the above that you mean the OnNoData property is set to
"[Event Procedure]", and your code is in an event procedure for this
event, so it looks like this:

Private Sub Report_NoData(Cancel As Integer)
MsgBox " Your filter selection option has no records to
display."
Cancel = True
End Sub

This being the case, you need to trap the 2501 error (action
cancelled) in the code procedure that opens the report, not in any
event procedure in the report itself. So if you are opening the
report with some code along the lines of:

DoCmd.OpenReport "ReportName", acViewPreview, , strCriteria

then you need an error-handler there:

On Error GoTo Err_Handler

' ... other code ...

DoCmd.OpenReport "ReportName", acViewPreview, , strCriteria

' ... maybe other code ...

Exit_Point:
Exit Sub

Err_Handler:
If Err.Number = 2501 Then
Resume Next
Else
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point
End If

End Sub

You would put the code I suggested in the procedure
frankPrintFilterReport(). Put the line:

immediately under the function header line, and put the lines

between the "DoCmd.OpenReport" line and the "End Sub" line. Take out
the quotation ">> " prefixes from the above, of course.

 

[Frank]

Dirk:

Thanks once again for assistance.

This is what I did in module:

____________________________________

Function frankPrintFilterReport(frm As Form, strReportName As String)
On Error GoTo Err_Handler

Dim strCriteria As String
stFilterValue = frm.cmbFilterValue
stConvFilterValue = StrConv(stFilterValue, vbLowerCase)
If blnYesNo = True Then
If stFilterValue = "yes" Or stFilterValue = "true" Or
stFilterValue = "on" Then
frm.cmbFilterValue = -1
ElseIf stFilterValue = "no" Or stFilterValue = "false" Or
stFilterValue = "off " Then
frm.cmbFilterValue = 0
Else
End If
End If
strCriteria = "((([" & stFormDataSource & "].[" & stFilterField & "]) =
Forms.[" & frm.Name & "].cmbFilterValue))"
DoCmd.OpenReport strReportName, acViewPreview, ,
WhereCondition:=strCriteria


Exit_Point

Err_Handler:
If Err.Number = 2501 Then
Resume Next
Else
MsgBox Err.Description, vbExclamation, "Error " & Err.Number

End If


End Function

__________________________________________

When I compile I get a compile error - "sub or function not defined".

There is no sub end in this part of the module.

Also, If the error message comes up, will above code close the report
automatically?

Thanks much for your guidance.

Dirk:

Thanks for reply. Still having a bit of trouble. I call the open
report from a modul. There is a command button on a form that that
when clicked calls a report after filtering the records. Some
filitered results have no records. I'm trying to display the message
box without the runtime error 2501 when a report has no records

The code on the on click
event of the button is
________________________________________________________________________
____
______________

Private Sub btnPrintFilteredReport_Click()
Dim frm As Form
Dim strReportName As String

Set frm = Me
strReportName = Me!lstReports


Call frankPrintFilterReport(frm, strReportName)


End Sub
________________________________________________________________________
____
______________
The code
for the Module is:


Function frankPrintFilterReport(frm As Form, strReportName As String)

Dim strCriteria As String
stFilterValue = frm.cmbFilterValue
stConvFilterValue = StrConv(stFilterValue, vbLowerCase)
If blnYesNo = True Then
If stFilterValue = "yes" Or stFilterValue = "true" Or
stFilterValue = "on" Then
frm.cmbFilterValue = -1
ElseIf stFilterValue = "no" Or stFilterValue = "false" Or
stFilterValue = "off " Then
frm.cmbFilterValue = 0
Else
End If
End If
strCriteria = "((([" & stFormDataSource & "].[" & stFilterField &
"]) = Forms.[" & frm.Name & "].cmbFilterValue))"

DoCmd.OpenReport strReportName, acViewPreview, ,
WhereCondition:=strCriteria

End Function


________________________________________________________________________
____
_______________

Any furhter assistance is greatly appreciated.

BlankI have a report with as message box in it that returns when no
records are found. Specifically, how do I add error-handling to the
procedure in order to trap error 2501.

my code is as follows and placed in the [On no data] property of the
report:

MsgBox " Your filter selection option has no records to display."
Cancel = True

I assume by the above that you mean the OnNoData property is set to
"[Event Procedure]", and your code is in an event procedure for this
event, so it looks like this:

Private Sub Report_NoData(Cancel As Integer)
MsgBox " Your filter selection option has no records to
display."
Cancel = True
End Sub

This being the case, you need to trap the 2501 error (action
cancelled) in the code procedure that opens the report, not in any
event procedure in the report itself. So if you are opening the
report with some code along the lines of:

DoCmd.OpenReport "ReportName", acViewPreview, , strCriteria

then you need an error-handler there:

On Error GoTo Err_Handler

' ... other code ...

DoCmd.OpenReport "ReportName", acViewPreview, , strCriteria

' ... maybe other code ...

Exit_Point:
Exit Sub

Err_Handler:
If Err.Number = 2501 Then
Resume Next
Else
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point
End If

End Sub

You would put the code I suggested in the procedure
frankPrintFilterReport(). Put the line:

immediately under the function header line, and put the lines

between the "DoCmd.OpenReport" line and the "End Sub" line. Take out
the quotation ">> " prefixes from the above, of course.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Dirk:

Thanks once again for assistance.

This is what I did in module:

____________________________________

Function frankPrintFilterReport(frm As Form, strReportName As String)
On Error GoTo Err_Handler

Dim strCriteria As String
stFilterValue = frm.cmbFilterValue
stConvFilterValue = StrConv(stFilterValue, vbLowerCase)
If blnYesNo = True Then
If stFilterValue = "yes" Or stFilterValue = "true" Or
stFilterValue = "on" Then
frm.cmbFilterValue = -1
ElseIf stFilterValue = "no" Or stFilterValue = "false" Or
stFilterValue = "off " Then
frm.cmbFilterValue = 0
Else
End If
End If
strCriteria = "((([" & stFormDataSource & "].[" & stFilterField &
"]) = Forms.[" & frm.Name & "].cmbFilterValue))"
DoCmd.OpenReport strReportName, acViewPreview, ,
WhereCondition:=strCriteria


Exit_Point

Err_Handler:
If Err.Number = 2501 Then
Resume Next
Else
MsgBox Err.Description, vbExclamation, "Error " & Err.Number

End If


End Function

__________________________________________

When I compile I get a compile error - "sub or function not defined".

There is no sub end in this part of the module.

You left out some of my suggested code, and there was one error in my
suggestion, in that I had an Exit Sub line that should have been Exit
Function. Try the following:

'------ start of revised code -------
Function frankPrintFilterReport(frm As Form, strReportName As String)

On Error GoTo Err_Handler

Dim strCriteria As String

stFilterValue = frm.cmbFilterValue
stConvFilterValue = StrConv(stFilterValue, vbLowerCase)

If blnYesNo = True Then
If stFilterValue = "yes" _
Or stFilterValue = "true" _
Or stFilterValue = "on" _
Then
frm.cmbFilterValue = -1
ElseIf stFilterValue = "no" _
Or stFilterValue = "false" _
Or stFilterValue = "off " _
Then
frm.cmbFilterValue = 0
End If
End If

strCriteria = _
"((([" & stFormDataSource & "].[" & stFilterField & _
"])= Forms![" & frm.Name & "].cmbFilterValue))"

DoCmd.OpenReport strReportName, acViewPreview, _
WhereCondition:=strCriteria

Exit_Point:
Exit Function

Err_Handler:
If Err.Number = 2501 Then
Resume Next
Else
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point
End If

End Function
'------ end of revised code -------

I don't know where these variables stFormDataSource and stFilterField
are being defined, so I don't know if the absence of definition for them
represents an error or not.

Also, If the error message comes up, will above code close the report
automatically?

The report is actually being closed -- cancelled -- by the code you have
in its NoData event. This code is just dealing with the fact that
cancelling the report raises error 2501 in the code procedure that
opened the report.

 

[Frank]

Dirk:

Thanks for the ump-teen time. I finally got things working; however, never
did get module to work. What I did was go to the "on no data" property event
of each report and place this code in it:

______________________________________________

On Error GoTo ErrorHandler
MsgBox " Your filter selection option has no records to display."
Cancel = True
ExitHere:
Exit Sub
ErrorHandler:
If Err.Number = 2501 Then
MsgBox " Your filter selection option has no records to display."
Cancel = True
Else
MsgBox Err.Description, , "Error# " & Err.Number
End If
Resume ExitHere

End Sub
_________________________________________________

It now works. Maybe above code could be shorten or cleaned up? Anyway, each
report works now.

When I placed everything in the module, it compiled OK, but when opening a
report, nothing happened, it just showed the blank report.

Thanks once again for your professional assistance and patience in dealing
with me.

Have a good one,

Frank

Dirk:

Thanks once again for assistance.

This is what I did in module:

____________________________________

Function frankPrintFilterReport(frm As Form, strReportName As String)
On Error GoTo Err_Handler

Dim strCriteria As String
stFilterValue = frm.cmbFilterValue
stConvFilterValue = StrConv(stFilterValue, vbLowerCase)
If blnYesNo = True Then
If stFilterValue = "yes" Or stFilterValue = "true" Or
stFilterValue = "on" Then
frm.cmbFilterValue = -1
ElseIf stFilterValue = "no" Or stFilterValue = "false" Or
stFilterValue = "off " Then
frm.cmbFilterValue = 0
Else
End If
End If
strCriteria = "((([" & stFormDataSource & "].[" & stFilterField & "]) =
Forms.[" & frm.Name & "].cmbFilterValue))"
DoCmd.OpenReport strReportName, acViewPreview, ,
WhereCondition:=strCriteria


Exit_Point

Err_Handler:
If Err.Number = 2501 Then
Resume Next
Else
MsgBox Err.Description, vbExclamation, "Error " & Err.Number

End If


End Function

__________________________________________

When I compile I get a compile error - "sub or function not defined".

There is no sub end in this part of the module.

Also, If the error message comes up, will above code close the report
automatically?

Thanks much for your guidance.

Dirk:

Thanks for reply. Still having a bit of trouble. I call the open
report from a modul. There is a command button on a form that that
when clicked calls a report after filtering the records. Some
filitered results have no records. I'm trying to display the message
box without the runtime error 2501 when a report has no records

The code on the on click
event of the button is
________________________________________________________________________
____
______________

Private Sub btnPrintFilteredReport_Click()
Dim frm As Form
Dim strReportName As String

Set frm = Me
strReportName = Me!lstReports


Call frankPrintFilterReport(frm, strReportName)


End Sub
________________________________________________________________________
____
______________
The code
for the Module is:


Function frankPrintFilterReport(frm As Form, strReportName As String)

Dim strCriteria As String
stFilterValue = frm.cmbFilterValue
stConvFilterValue = StrConv(stFilterValue, vbLowerCase)
If blnYesNo = True Then
If stFilterValue = "yes" Or stFilterValue = "true" Or
stFilterValue = "on" Then
frm.cmbFilterValue = -1
ElseIf stFilterValue = "no" Or stFilterValue = "false" Or
stFilterValue = "off " Then
frm.cmbFilterValue = 0
Else
End If
End If
strCriteria = "((([" & stFormDataSource & "].[" & stFilterField &
"]) = Forms.[" & frm.Name & "].cmbFilterValue))"

DoCmd.OpenReport strReportName, acViewPreview, ,
WhereCondition:=strCriteria

End Function


________________________________________________________________________
____
_______________

Any furhter assistance is greatly appreciated.





BlankI have a report with as message box in it that returns when no
records are found. Specifically, how do I add error-handling to the
procedure in order to trap error 2501.

my code is as follows and placed in the [On no data] property of the
report:

MsgBox " Your filter selection option has no records to display."
Cancel = True

I assume by the above that you mean the OnNoData property is set to
"[Event Procedure]", and your code is in an event procedure for this
event, so it looks like this:

Private Sub Report_NoData(Cancel As Integer)
MsgBox " Your filter selection option has no records to
display."
Cancel = True
End Sub

This being the case, you need to trap the 2501 error (action
cancelled) in the code procedure that opens the report, not in any
event procedure in the report itself. So if you are opening the
report with some code along the lines of:

DoCmd.OpenReport "ReportName", acViewPreview, , strCriteria

then you need an error-handler there:

On Error GoTo Err_Handler

' ... other code ...

DoCmd.OpenReport "ReportName", acViewPreview, , strCriteria

' ... maybe other code ...

Exit_Point:
Exit Sub

Err_Handler:
If Err.Number = 2501 Then
Resume Next
Else
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point
End If

End Sub

You would put the code I suggested in the procedure
frankPrintFilterReport(). Put the line:

On Error GoTo Err_Handler

immediately under the function header line, and put the lines

Exit_Point:
Exit Sub

Err_Handler:
If Err.Number = 2501 Then
Resume Next
Else
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point
End If

between the "DoCmd.OpenReport" line and the "End Sub" line. Take out
the quotation ">> " prefixes from the above, of course.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)