Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rating: Thread Rating: 10 votes, 5.00 average. Display Modes
Old 02-05-2007, 03:33 PM   #1
ShanVel
ShanMug
 
Join Date: Oct 2005
Location: Akron, OH, USA
Posts: 51
Thanks: 0
Thanked 0 Times in 0 Posts
ShanVel is on a distinguished road
Run-time error 2501; OpenReport action was cancelled

Folks,
Need your help. I searched the entire forum and found couple of postings on this, but none of them indicates if there is any fix for this problem.

If there is no data, basically I want to show a message and close the report. I have some code to handle this under no data event like below:
Private Sub Report_NoData(Cancel As Integer)
MsgBox "There is no data for this report. Canceling report..."
Cancel = True
End Sub


But this 2501 always pops up and I couldn't find a way to stop this. I also searched Microsoft knowledgebase, no help.
Here's how my clik event procedure looks like:
Private Sub cmdSearch_Click()
On Error GoTo Err_PreviewRprt
Dim strTbl As String

Dim strSQL As String

Dim strOptItem As String
Dim strOptBlrType As String

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


'Set up SQL statement for report record source
If IsNull(optCriteria) Then optCriteria = 1
strTbl = Me.cboTypeOfGuar.Column(0)
strOptItem = Choose(optCriteria, "memPred", "memGuar", "memRiskLevel", "memLDs")

strOptBlrType = Choose(optBoilerType, "SWUP", "RB", "CFB", "All")



strSQL = "SELECT tblProjts1.chrProjectName, tblProjts1.chrBlrPropNum, tblProjts1.chrBoilerType, " & _
strTbl & ".memGuranItem, " & strTbl & "." & strOptItem & _
" FROM tblProjts1 INNER JOIN " & strTbl & " ON " & _
"tblProjts1.intProjectId = " & strTbl & ".intProjectId" & _
" WHERE (((" & strTbl & ".memGuranItem)" & " IS NOT NULL)" & " AND " & "((tblProjts1.chrBoilerType) = '" & strOptBlrType & "'))"


'Open report in Design view to set the report's record source
'and search item label's caption
DoCmd.Echo False 'suppress the screen updates


DoCmd.OpenReport "rptBlrSrchItem1", acViewDesign
With Reports("rptBlrSrchItem1")
.RecordSource = strSQL
.Controls("strOptItem").ControlSource = Choose(optCriteria, "memPred", "memGuar", "memRiskLevel", "memLDs")

End With

DoCmd.Close , , acSaveYes

'Now show the search results to the user
DoCmd.OpenReport "rptBlrSrchItem1", acViewPreview

DoCmd.Echo True

Exit_PreviewRprt:
Exit Sub

Err_PreviewRprt:
If Err = 2501 Then
Resume Exit_PreviewRprt
Else
MsgBox Err.Description
Resume Exit_PreviewRprt
End If

End Sub


Any help is much appreciated.

ShanVel is offline   Reply With Quote
Old 02-05-2007, 03:58 PM   #2
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,817 Times in 1,575 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
You have to capture the error in the code that opens your report as that's the code that is generating the error, not the cancel of the no data. I usually put in an error handler and trap for error 2501 to basically tell it to ignore that error (exit sub) when that error occurs. But the confusing part that got me initially was that I was trying to figure out what code generated it. It is the DoCmd.OpenReport code as it isn't finishing since the open gets canceled before the opening code finishes.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Old 02-05-2007, 04:00 PM   #3
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,817 Times in 1,575 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
I just noticed that your trap is slightly off:

Change
Code:
If Err = 2501 Then
Resume Exit_PreviewRprt
Else
MsgBox Err.Description
Resume Exit_PreviewRprt
End If
to this
Code:
If Err.Number = 2501 Then
    Resume Exit_PreviewRprt
Else
    MsgBox Err.Description
    Resume Exit_PreviewRprt
End If

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
The Following User Says Thank You to boblarson For This Useful Post:
Matt_Lavoie (09-15-2011)
Old 02-05-2007, 04:00 PM   #4
Wiz47
Learning by inches ...
 
Wiz47's Avatar
 
Join Date: Nov 2006
Location: Georgia
Posts: 274
Thanks: 5
Thanked 6 Times in 6 Posts
Wiz47 is on a distinguished road
Quote:
Originally Posted by ShanVel
Folks,
Need your help. I searched the entire forum and found couple of postings on this, but none of them indicates if there is any fix for this problem.

If there is no data, basically I want to show a message and close the report. I have some code to handle this under no data event like below:
Private Sub Report_NoData(Cancel As Integer)
MsgBox "There is no data for this report. Canceling report..."
Cancel = True
End Sub


But this 2501 always pops up and I couldn't find a way to stop this. I also searched Microsoft knowledgebase, no help.
Here's how my clik event procedure looks like:
Private Sub cmdSearch_Click()
On Error GoTo Err_PreviewRprt
Dim strTbl As String

Dim strSQL As String

Dim strOptItem As String
Dim strOptBlrType As String

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


'Set up SQL statement for report record source
If IsNull(optCriteria) Then optCriteria = 1
strTbl = Me.cboTypeOfGuar.Column(0)
strOptItem = Choose(optCriteria, "memPred", "memGuar", "memRiskLevel", "memLDs")

strOptBlrType = Choose(optBoilerType, "SWUP", "RB", "CFB", "All")



strSQL = "SELECT tblProjts1.chrProjectName, tblProjts1.chrBlrPropNum, tblProjts1.chrBoilerType, " & _
strTbl & ".memGuranItem, " & strTbl & "." & strOptItem & _
" FROM tblProjts1 INNER JOIN " & strTbl & " ON " & _
"tblProjts1.intProjectId = " & strTbl & ".intProjectId" & _
" WHERE (((" & strTbl & ".memGuranItem)" & " IS NOT NULL)" & " AND " & "((tblProjts1.chrBoilerType) = '" & strOptBlrType & "'))"


'Open report in Design view to set the report's record source
'and search item label's caption
DoCmd.Echo False 'suppress the screen updates


DoCmd.OpenReport "rptBlrSrchItem1", acViewDesign
With Reports("rptBlrSrchItem1")
.RecordSource = strSQL
.Controls("strOptItem").ControlSource = Choose(optCriteria, "memPred", "memGuar", "memRiskLevel", "memLDs")

End With

DoCmd.Close , , acSaveYes

'Now show the search results to the user
DoCmd.OpenReport "rptBlrSrchItem1", acViewPreview

DoCmd.Echo True

Exit_PreviewRprt:
Exit Sub

Err_PreviewRprt:
If Err = 2501 Then
Resume Exit_PreviewRprt
Else
MsgBox Err.Description
Resume Exit_PreviewRprt
End If

End Sub


Any help is much appreciated.
Just add an error handler to the code you use to execute the report:

Something like:

Code:
Private Sub Open_Report_Click()

On Error GoTo Err_Report_Click

    DoCmd.OpenReport Me.Report, ac_Preview

Exit_Report_Click:
    Exit Sub

Err_Report_Click:
    If Err.Number = 2501 Then
        Resume Next
    Else

        MsgBox Err.Description
        Resume Exit_Report_Click
    End If
End Sub
__________________
"Give a man a fish, and feed him for a day. Teach him to fish, and he'll sit on the dock for hours and drink beer."
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by Wiz47; 02-05-2007 at 04:06 PM.
Wiz47 is offline   Reply With Quote
The Following User Says Thank You to Wiz47 For This Useful Post:
manusri (03-28-2014)
Old 02-05-2007, 04:11 PM   #5
ShanVel
ShanMug
 
Join Date: Oct 2005
Location: Akron, OH, USA
Posts: 51
Thanks: 0
Thanked 0 Times in 0 Posts
ShanVel is on a distinguished road
boblarson,
Thanks for catching up the mistake.

I fixed the line to Err.Number, still same message Error 2501

When I click 'Debug' button it always takes to this line of code:
DoCmd.OpenReport "rptBlrSrchItem1", acViewPreview
ShanVel is offline   Reply With Quote
Old 02-05-2007, 04:30 PM   #6
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,817 Times in 1,575 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Okay, something isn't happening right. You should not be getting a debug button given that your error handler doesn't give a dialog with that option. That tells me that it is happening on the code that doesn't have an error handler. I would put a breakpoint in a few lines above your code where it is highlighting and then step through it, a line at a time, when it breaks so that you can see exactly when it is generating the errror. It is at that point where you will need to put another error handler.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Old 02-05-2007, 04:31 PM   #7
ShanVel
ShanMug
 
Join Date: Oct 2005
Location: Akron, OH, USA
Posts: 51
Thanks: 0
Thanked 0 Times in 0 Posts
ShanVel is on a distinguished road
Quote:
Originally Posted by Wiz47
Just add an error handler to the code you use to execute the report:

Something like:

Code:
Private Sub Open_Report_Click()

On Error GoTo Err_Report_Click

    DoCmd.OpenReport Me.Report, ac_Preview

Exit_Report_Click:
    Exit Sub

Err_Report_Click:
    If Err.Number = 2501 Then
        Resume Next
    Else

        MsgBox Err.Description
        Resume Exit_Report_Click
    End If
End Sub
Wiz47,
I did try this and same err 2501 messgae. Looks like there is no way to trap this error. I tried to trap this on the report's "on close, on error, on open, on no data" and nothing seems to work.

ShanVel is offline   Reply With Quote
Old 02-05-2007, 05:06 PM   #8
ShanVel
ShanMug
 
Join Date: Oct 2005
Location: Akron, OH, USA
Posts: 51
Thanks: 0
Thanked 0 Times in 0 Posts
ShanVel is on a distinguished road
bolarson,

I added a breakpoint just above the following line and step thru line by line.
"DoCmd.OpenReport "rptBlrSrchItem1", acViewPreview"

This time I didn't get the 2501 messgae with 'End' and 'Debug' button, but just when it came to that line it crashed.

Also, I tried one more error trap as follows and no use.
On Error Resume Next
DoCmd.OpenReport "rptBlrSrchItem1", acViewPreview


Strange, I spent almost a day and we can't trap an error which has an error code by MS Access 2003.
ShanVel is offline   Reply With Quote
Old 02-05-2007, 05:48 PM   #9
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,218
Thanks: 10
Thanked 3,903 Times in 3,846 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
That error is commonly trapped, and your code should trap it. I'm shooting from the hip, but try modifying the

DoCmd.Close

to explicitly close the report.
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 02-05-2007, 06:01 PM   #10
Wiz47
Learning by inches ...
 
Wiz47's Avatar
 
Join Date: Nov 2006
Location: Georgia
Posts: 274
Thanks: 5
Thanked 6 Times in 6 Posts
Wiz47 is on a distinguished road
Quote:
Originally Posted by ShanVel
Wiz47,
I did try this and same err 2501 messgae. Looks like there is no way to trap this error. I tried to trap this on the report's "on close, on error, on open, on no data" and nothing seems to work.
That is strange. I use this errorhandler to trap the option to cancel when someone backs out of various procedures - so the CANCEL msgbox doesn't pop up - it just exits the sub.

I use the same procedure (tweaked a bit and uses a different err.number) to handle no data in a report. I use it on the onload event for the report (I have it send a message that the report does not have data, then it allows the user to exit the sub by clicking OK). I thought that sending a message on a "No Data" event was better than exiting the sub without letting them know why.
__________________
"Give a man a fish, and feed him for a day. Teach him to fish, and he'll sit on the dock for hours and drink beer."
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Wiz47 is offline   Reply With Quote
Old 02-06-2007, 07:19 AM   #11
ShanVel
ShanMug
 
Join Date: Oct 2005
Location: Akron, OH, USA
Posts: 51
Thanks: 0
Thanked 0 Times in 0 Posts
ShanVel is on a distinguished road
Wiz47,
Can you give me some more detailed explanation? You are talking about load event of a report? I don't see that event listed for a report?
ShanVel is offline   Reply With Quote
Old 02-06-2007, 03:32 PM   #12
ShanVel
ShanMug
 
Join Date: Oct 2005
Location: Akron, OH, USA
Posts: 51
Thanks: 0
Thanked 0 Times in 0 Posts
ShanVel is on a distinguished road
I am back on the same issue.

Dou you think using a macro to open the report instead of Docmd (DoCmd.OpenReport "rptBlrSrchItem1", acViewPreview) would help? I just don't know how to do this inside my click event procedure. Your help is greatly appreciated.
ShanVel is offline   Reply With Quote
Old 02-06-2007, 04:27 PM   #13
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,817 Times in 1,575 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
If you can post your database, we can track down the problem. There's obviously something not being communicated and sometimes it's hard to know what to ask when seeing can give the answer immediately (especially for me as I'm very visual).
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Old 02-06-2007, 04:27 PM   #14
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,218
Thanks: 10
Thanked 3,903 Times in 3,846 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Personally, no. Did you try my shot-in-the-dark in post 9? I always trap empty reports by setting cancel = true in the no data event, and trapping 2501 in the code that opened it. You have something weird going on. Can you post a sample db?
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 02-06-2007, 08:02 PM   #15
Wiz47
Learning by inches ...
 
Wiz47's Avatar
 
Join Date: Nov 2006
Location: Georgia
Posts: 274
Thanks: 5
Thanked 6 Times in 6 Posts
Wiz47 is on a distinguished road
Quote:
Originally Posted by ShanVel
Wiz47,
Can you give me some more detailed explanation? You are talking about load event of a report? I don't see that event listed for a report?
Sorry for not responding sooner, I was working on something else. I meant to say the OnOpen event - I didn't mean to add to the confusion. I would take BobLarson's and PBaldy's advice and upload a sample of the Db. You have two of the best here willing to help. I'd certainly jump at that opportunity if I had a problem.

__________________
"Give a man a fish, and feed him for a day. Teach him to fish, and he'll sit on the dock for hours and drink beer."
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Wiz47 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
The Openreport action was canceled error Ice Rhino Modules & VBA 13 07-24-2018 07:55 PM
OpenReport action was cancelled pool12k Reports 0 10-06-2004 10:01 AM
Run time versions and missing references Dan_T General 8 07-19-2004 03:50 AM
OpenReport Action Cancelled k7i5t3n Reports 0 06-08-2004 10:22 AM
Error Dialog Box: OpenReport Action Cancelled jalge Reports 6 04-24-2003 07:07 AM




All times are GMT -8. The time now is 05:35 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World