isurgyn
asked on
Intermittent Error 2501: The Open Report Action was canceled
Hi,
I am getting Error 2501 in a very intermittent and inconsistent fashion. It typically only occurs on a couple of windows 7 clients but other identical systems do not generate the error. Those systems only seem to have the problem at certain times or days.
The following DoCmd.OutputTo line of code is the "problem":
MyFileName = "Full Exam - " & Format(Now, "mm-dd-yyyy hh-mm-ss") & ".pdf"
DoCmd.OutputTo acOutputReport, "rptMyReport", acFormatPDF, FilePath & MyFileName, 0
DoCmd.SetWarnings (WarningsOff)
DoCmd.OpenQuery "qryMyAppendQuery"
Forms!frmMyForm.Requery
DoCmd.SetWarnings (WarningsOn)
DoCmd.Close acForm, "frmAnotherForm"
The issue will go away and then return another day. Sometimes reinstalling the runtime seems to make it better others not.
I am wondering if it is a timeout issue with the network / server system although I have never seen it happen on any of the clients running XP Pro. Perhaps it is the Windows 7 devices are running so fast that the server doesn't have time to finish before the code continues. ??? Obviously I have no clue.
Anyway there are a lot of posts about this but none seem to have a solution.
Thoughts?
I am running Access 2007 front end with MS SQL Server 2008 backend on a LAN with a brand new server with lots of capacity.
Thanks
I am getting Error 2501 in a very intermittent and inconsistent fashion. It typically only occurs on a couple of windows 7 clients but other identical systems do not generate the error. Those systems only seem to have the problem at certain times or days.
The following DoCmd.OutputTo line of code is the "problem":
MyFileName = "Full Exam - " & Format(Now, "mm-dd-yyyy hh-mm-ss") & ".pdf"
DoCmd.OutputTo acOutputReport, "rptMyReport", acFormatPDF, FilePath & MyFileName, 0
DoCmd.SetWarnings (WarningsOff)
DoCmd.OpenQuery "qryMyAppendQuery"
Forms!frmMyForm.Requery
DoCmd.SetWarnings (WarningsOn)
DoCmd.Close acForm, "frmAnotherForm"
The issue will go away and then return another day. Sometimes reinstalling the runtime seems to make it better others not.
I am wondering if it is a timeout issue with the network / server system although I have never seen it happen on any of the clients running XP Pro. Perhaps it is the Windows 7 devices are running so fast that the server doesn't have time to finish before the code continues. ??? Obviously I have no clue.
Anyway there are a lot of posts about this but none seem to have a solution.
Thoughts?
I am running Access 2007 front end with MS SQL Server 2008 backend on a LAN with a brand new server with lots of capacity.
Thanks
I would but a STOP or check in the On NoData event of rptMyReport to see if it's a matter if the report actually has no data (in which case it's canceled) and then examine why it has no data, which is probably the case (that the error is because of the data).
Jim.
Jim.
I agree with Jim. If you have a NoData event that cancels the report opening event then you would get an error when the OutputTo method fires.
This is a very good reason not to use "SETWARNINGS False", but instead, add an error handler to your routine.
This is a very good reason not to use "SETWARNINGS False", but instead, add an error handler to your routine.
ASKER
OK. I am not certain about the no data idea but here is how the process is supposed to work and does work 98% of the time.
The user opens a form that is based on a query from a data table. New entries are made in the form. The form is chocked full of stuff from the table and any new updates to multiple fields. So there is never an event where the form has no data in the fields.
However....
Then the code noted above is run from a command button on the form. The report is based off the same query as the open form. The form is then closed.
Perhaps I need a save record command somewhere prior to running the report.
Could that give me a no data error?
The user opens a form that is based on a query from a data table. New entries are made in the form. The form is chocked full of stuff from the table and any new updates to multiple fields. So there is never an event where the form has no data in the fields.
However....
Then the code noted above is run from a command button on the form. The report is based off the same query as the open form. The form is then closed.
Perhaps I need a save record command somewhere prior to running the report.
Could that give me a no data error?
ASKER
Also, the code errors out on this line:
DoCmd.OutputTo acOutputReport, "rptMyReport", acFormatPDF, FilePath & MyFileName, 0
which is before I SetWarnings Off.
Also, I have tried closing the form (which should update the record in the table) then immediately reopen the form, hit the command button and get the same 2501 error.
Immediately after receiving the error on one of the client systems, I can walk to another client system, open the very same form on the same identical query on the same identical record and hit the command button and the code executes correctly.
So, despite my limited knowledge of vba and its idiosyncracies, I am skeptical of the no data explanation.
DoCmd.OutputTo acOutputReport, "rptMyReport", acFormatPDF, FilePath & MyFileName, 0
which is before I SetWarnings Off.
Also, I have tried closing the form (which should update the record in the table) then immediately reopen the form, hit the command button and get the same 2501 error.
Immediately after receiving the error on one of the client systems, I can walk to another client system, open the very same form on the same identical query on the same identical record and hit the command button and the code executes correctly.
So, despite my limited knowledge of vba and its idiosyncracies, I am skeptical of the no data explanation.
<<So, despite my limited knowledge of vba and its idiosyncracies, I am skeptical of the no data explanation.>>
Well let's cover a few things:
1. When you open a report, if you cancel it in the OnOpen event or it has no data, then error 2501 is returned to the calling code. That may or may not be correct (sometimes you really do have no data and that's OK).
2. You have no error trapping and set warnings false is after the Output, so you'll see the error no matter what.
Where does that leave us? Setting #2 aside for the moment (you could add error trapping or move Setwarnings to mask the error), you have to figure out why the error is being returned.
Putting a stop or a msgbox in the NoData event will confirm that the report sees no data. Beyond that, then you need to figure out why. Maybe it's some criteria in the reports recordsource, a form field that's null that it's pointing to, etc.
But the first thing is that you have to confirm it's hitting no data. Once you do, then you need to ask if that's OK or not. If it is, you'll need to add error trapping to the code or move the setwarnings.
But either way, first step is to figure out what's going on in the report.
Jim.
Well let's cover a few things:
1. When you open a report, if you cancel it in the OnOpen event or it has no data, then error 2501 is returned to the calling code. That may or may not be correct (sometimes you really do have no data and that's OK).
2. You have no error trapping and set warnings false is after the Output, so you'll see the error no matter what.
Where does that leave us? Setting #2 aside for the moment (you could add error trapping or move Setwarnings to mask the error), you have to figure out why the error is being returned.
Putting a stop or a msgbox in the NoData event will confirm that the report sees no data. Beyond that, then you need to figure out why. Maybe it's some criteria in the reports recordsource, a form field that's null that it's pointing to, etc.
But the first thing is that you have to confirm it's hitting no data. Once you do, then you need to ask if that's OK or not. If it is, you'll need to add error trapping to the code or move the setwarnings.
But either way, first step is to figure out what's going on in the report.
Jim.
ASKER
OK I really appreciate your help. I have implemented all of the above and a lot more. I am about ready to give up on this.
1. I have removed the Set Warnings and replaced with error trapping. Lots of annoying messages about running queries but the only error ever trapped is Err 2501.
2. I have placed MsgBoxes in the report on OnNoData, OnOpen and even OnLoad
ie. MsgBox "Err Number = " & Err.Number & "Err Description = " & Err.Description
On no event has the report ever returned anything but Err = 0. I can also open the report while the form is open. Again no error on opening the report.
3. I have changed the syntax on the pdf file name so that it is more conventional in case Windows didn't like the spaces and hyphens.
4. I have changed the file path to the local C: drive to ensure this was not being caused by some latency in the MS SQL Server 2008 protocol.
5. I have place up to 30 DoEvents following each line of code.
6. The code runs on 6 different devices:
a) Laptop running Windows 7 where I do most of the development coding of forms etc.. It is not networked so the data is a pure Access database. The code has never returned a 2501 error.
b) Four Windows 7 clients all from the same identical manufacturer running on the server. Two clients never return a 2501 error. One returns the error >90% of the time. One returns the error 50% of the time. Two essentially never return the error.
c) One Window XP Pro client that has NEVER returned a 2501 error.
d) I have reinstalled Access 2007 from scratch on the two Windows 7 systems that return the error - still the same error.
e) I have downloaded the Microsoft Office Add-in: Microsoft Save as PDF or XPS
7. I have changed the code to the following:
MyFileName = "PO_Exam_" & Format(Now(), "mm_dd_yyyy_hh_nn_ss") & ".pdf"
DoCmd.OpenReport "rptPOExam", acViewPreview, , , acHidden
DoEvents
DoCmd.SelectObject acReport, "rptPOExam"
DoCmd.OutputTo acOutputReport, "rptPOExam", acFormatPDF, LocalPath & MyFileName, False
DoEvents
DoCmd.Close acReport, "rptPOExam"
MsgBox "The archive file has been created", vbOKOnly, "Archive Complete"
NOTE: This code will work about 50% of the time. When it errors out it will open the report in Print Preview mode then return the 2501 error.
I must be missing something. I just can't figure out what it is.
1. I have removed the Set Warnings and replaced with error trapping. Lots of annoying messages about running queries but the only error ever trapped is Err 2501.
2. I have placed MsgBoxes in the report on OnNoData, OnOpen and even OnLoad
ie. MsgBox "Err Number = " & Err.Number & "Err Description = " & Err.Description
On no event has the report ever returned anything but Err = 0. I can also open the report while the form is open. Again no error on opening the report.
3. I have changed the syntax on the pdf file name so that it is more conventional in case Windows didn't like the spaces and hyphens.
4. I have changed the file path to the local C: drive to ensure this was not being caused by some latency in the MS SQL Server 2008 protocol.
5. I have place up to 30 DoEvents following each line of code.
6. The code runs on 6 different devices:
a) Laptop running Windows 7 where I do most of the development coding of forms etc.. It is not networked so the data is a pure Access database. The code has never returned a 2501 error.
b) Four Windows 7 clients all from the same identical manufacturer running on the server. Two clients never return a 2501 error. One returns the error >90% of the time. One returns the error 50% of the time. Two essentially never return the error.
c) One Window XP Pro client that has NEVER returned a 2501 error.
d) I have reinstalled Access 2007 from scratch on the two Windows 7 systems that return the error - still the same error.
e) I have downloaded the Microsoft Office Add-in: Microsoft Save as PDF or XPS
7. I have changed the code to the following:
MyFileName = "PO_Exam_" & Format(Now(), "mm_dd_yyyy_hh_nn_ss") & ".pdf"
DoCmd.OpenReport "rptPOExam", acViewPreview, , , acHidden
DoEvents
DoCmd.SelectObject acReport, "rptPOExam"
DoCmd.OutputTo acOutputReport, "rptPOExam", acFormatPDF, LocalPath & MyFileName, False
DoEvents
DoCmd.Close acReport, "rptPOExam"
MsgBox "The archive file has been created", vbOKOnly, "Archive Complete"
NOTE: This code will work about 50% of the time. When it errors out it will open the report in Print Preview mode then return the 2501 error.
I must be missing something. I just can't figure out what it is.
ASKER
I have also changed the OutputReport format to ".xps". Same error.
<<2. I have placed MsgBoxes in the report on OnNoData, OnOpen and even OnLoad
ie. MsgBox "Err Number = " & Err.Number & "Err Description = " & Err.Description
On no event has the report ever returned anything but Err = 0. I can also open the report while the form is open. Again no error on opening the report.>>
OK. Understand, but understand that the error 2501 is returned by the report to the calling code. Your not going to see 2501 in the report itself and as you have stated, your seeing no error.
But you are stopping in the NoData event and that means that based on the reports recordsource, at that specific instant, there was no data for the report. So it's going to cancel the open event and return a 2501.
<<6. The code runs on 6 different devices: >>
This is where it's going to get a little tougher because it may not be the app that's at fault. Couple different things may be going on:
1. Network/server problem - Something causes the report to see no data. You should however get a message box if that were happening. But just to cover the bases, but a msgbox statement in the reports OnError event and see if you hit it.
2. Timing issue - Your preparing data the report is based on, but for some reason that process is not complete before the report gets to it.
This would show up on different devices because each runs at a different speed. How we can test for this:
a. remove all the Doevents and replace them with a single call to the following function:
Public Function Wait(intSeconds As Integer) As Integer
Dim datCurDateTime As Date
10 datCurDateTime = Now
20 Do Until DateDiff("s", datCurDateTime, Now) > intSeconds
30 DoEvents
40 Loop
End Function
So:
Dim intRet as integer
intRet = Wait(5)
This is a function because I often call it from a macro and macro can't call subs. You can change it to a sub if you wish.
Now see if the 2501 pops up. If not, then you've found the problem.
3. Prep of data is right, but something in the data itself (ie. a Null) is causing the report to see no data.
Only way your going to figure this out is if immeaditaly after getting a 2501, you can open the report manually and it's works fine *without* anything being changed.
The report should have nothing going in the OnOpen, Load, etc that might affect it. If it does, you'll need to put a stop in the On Open, then step trhough the code with F8 making sure that it does everything it's supposed to.
Jim
ie. MsgBox "Err Number = " & Err.Number & "Err Description = " & Err.Description
On no event has the report ever returned anything but Err = 0. I can also open the report while the form is open. Again no error on opening the report.>>
OK. Understand, but understand that the error 2501 is returned by the report to the calling code. Your not going to see 2501 in the report itself and as you have stated, your seeing no error.
But you are stopping in the NoData event and that means that based on the reports recordsource, at that specific instant, there was no data for the report. So it's going to cancel the open event and return a 2501.
<<6. The code runs on 6 different devices: >>
This is where it's going to get a little tougher because it may not be the app that's at fault. Couple different things may be going on:
1. Network/server problem - Something causes the report to see no data. You should however get a message box if that were happening. But just to cover the bases, but a msgbox statement in the reports OnError event and see if you hit it.
2. Timing issue - Your preparing data the report is based on, but for some reason that process is not complete before the report gets to it.
This would show up on different devices because each runs at a different speed. How we can test for this:
a. remove all the Doevents and replace them with a single call to the following function:
Public Function Wait(intSeconds As Integer) As Integer
Dim datCurDateTime As Date
10 datCurDateTime = Now
20 Do Until DateDiff("s", datCurDateTime, Now) > intSeconds
30 DoEvents
40 Loop
End Function
So:
Dim intRet as integer
intRet = Wait(5)
This is a function because I often call it from a macro and macro can't call subs. You can change it to a sub if you wish.
Now see if the 2501 pops up. If not, then you've found the problem.
3. Prep of data is right, but something in the data itself (ie. a Null) is causing the report to see no data.
Only way your going to figure this out is if immeaditaly after getting a 2501, you can open the report manually and it's works fine *without* anything being changed.
The report should have nothing going in the OnOpen, Load, etc that might affect it. If it does, you'll need to put a stop in the On Open, then step trhough the code with F8 making sure that it does everything it's supposed to.
Jim
BTW, on this:
DoCmd.SetWarnings (WarningsOff)
DoCmd.OpenQuery "qryMyAppendQuery"
<< Lots of annoying messages about running queries but the only error ever trapped is Err 2501.>>
The way you want to execute queries for preping data is by using the .execute method:
CurrentDB().Execute "qryMyAppendQuery", dbFailOnError
The reason you want to do it this way is because of that last argument; dbFailOnError.
The problem with DoCmd.Setwarnings is that *all* messages and *errors* are masked, so if you got a problem with a query, your never going to know it.
Your queries should run without errors. With dbFailOnError, if the query has a problem, a error will be rasied and your error trapping code can react accordingly. But you won't receive any messages if everything is fine.
Jim.
DoCmd.SetWarnings (WarningsOff)
DoCmd.OpenQuery "qryMyAppendQuery"
<< Lots of annoying messages about running queries but the only error ever trapped is Err 2501.>>
The way you want to execute queries for preping data is by using the .execute method:
CurrentDB().Execute "qryMyAppendQuery", dbFailOnError
The reason you want to do it this way is because of that last argument; dbFailOnError.
The problem with DoCmd.Setwarnings is that *all* messages and *errors* are masked, so if you got a problem with a query, your never going to know it.
Your queries should run without errors. With dbFailOnError, if the query has a problem, a error will be rasied and your error trapping code can react accordingly. But you won't receive any messages if everything is fine.
Jim.
ASKER
<OK. Understand, but understand that the error 2501 is returned by the report to the calling code. Your not going to see 2501 in the report itself and as you have stated, your seeing no error.
But you are stopping in the NoData event and that means that based on the reports recordsource, at that specific instant, there was no data for the report. So it's going to cancel the open event and return a 2501.>
I need to clarify that on the OnError Event on the report I have a MsgBox that is something like:
MsgBox "This report has no data",vbOKOnly,"No Data in Report"
This MsgBox has never returned an error despite the dreaded 2501 Error being returned to Access code.
I only added the other error messages later on the OnLoad and OnOpen to see if there was some other error like the report being too wide etc. Not sure if that made any sense but both the OnOpen and OnLoad MsgBoxes on the report would return Err=0 immediately followed by the 2501 Error message.
I am going to try eveything you suggested but I just wanted to clarify that with the current version of the code the error 2501 is returned AFTER the report is open for viewing in Print Preview mode on the computer monitor so the report appears to me to be getting the data.? The Preview report is filled with the expected data so seems to be hanging on the acHidden part of that line??
But you are stopping in the NoData event and that means that based on the reports recordsource, at that specific instant, there was no data for the report. So it's going to cancel the open event and return a 2501.>
I need to clarify that on the OnError Event on the report I have a MsgBox that is something like:
MsgBox "This report has no data",vbOKOnly,"No Data in Report"
This MsgBox has never returned an error despite the dreaded 2501 Error being returned to Access code.
I only added the other error messages later on the OnLoad and OnOpen to see if there was some other error like the report being too wide etc. Not sure if that made any sense but both the OnOpen and OnLoad MsgBoxes on the report would return Err=0 immediately followed by the 2501 Error message.
I am going to try eveything you suggested but I just wanted to clarify that with the current version of the code the error 2501 is returned AFTER the report is open for viewing in Print Preview mode on the computer monitor so the report appears to me to be getting the data.? The Preview report is filled with the expected data so seems to be hanging on the acHidden part of that line??
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the feedback.
I have added the following code as a quick fix since I have found that if I leave the form open, clear the error message and simply continue to execute the code enough times, it will eventually output the report to pdf.
ErrHander:
If Err.Number = 2501 Then Resume
That seems to work but is a pretty messy approach. I will use it as a patch for a few days until I can get back to that particular system and try some of the alternatives that you suggested, since it is really only one computer that is returning 90+ percent of the errors.
I have added the following code as a quick fix since I have found that if I leave the form open, clear the error message and simply continue to execute the code enough times, it will eventually output the report to pdf.
ErrHander:
If Err.Number = 2501 Then Resume
That seems to work but is a pretty messy approach. I will use it as a patch for a few days until I can get back to that particular system and try some of the alternatives that you suggested, since it is really only one computer that is returning 90+ percent of the errors.
ASKER
Jim did a great job of coaching me through troubleshooting this error. After further analysis the error only occurs on our ASUS computers running Windows 7 Pro. These 4 systems also have VMWare Workstation installed to run Windows XP in virtual mode. Perhaps this has created some wonky system setting. The vb code runs perfectly on every other system without error.
MyFileName = "Full Exam - " & Format(Now, "mm-dd-yyyy hh-mm-ss") & ".pdf"
DoCmd.OutputTo acOutputReport, "rptMyReport", acFormatPDF, FilePath & MyFileName, 0
DoEvents
DoCmd.SetWarnings (WarningsOff)
DoCmd.OpenQuery "qryMyAppendQuery"
DoEvents
Forms!frmMyForm.Requery
DoEvents
DoCmd.SetWarnings (WarningsOn)
DoCmd.Close acForm, "frmAnotherForm"
In other words, insert a Doevents after anything that may take a while to complete
If the issue is resolved, then remove each Doevents (To see which are really not needed), until the issue returns, then only keep the DoEvents that are needed.
A bit "Brutish", so let's see if another Expert has something more elegant.....
;-)
JeffCoachman