Solved

Intermittent Error 2501: The Open Report Action was canceled

Posted on 2012-03-19
14
1,768 Views
Last Modified: 2012-05-14
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
0
Comment
Question by:isurgyn
14 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37739646
In a lot of cases you need strategically placed "DoEvents":
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
0
 
LVL 57
ID: 37739653
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.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 37739670
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.
0
 

Author Comment

by:isurgyn
ID: 37739750
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?
0
 

Author Comment

by:isurgyn
ID: 37740019
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.
0
 
LVL 57
ID: 37740056
<<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.
0
 

Author Comment

by:isurgyn
ID: 37764075
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.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:isurgyn
ID: 37764084
I have also changed the OutputReport format to ".xps".  Same error.
0
 
LVL 57
ID: 37765697
<<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
0
 
LVL 57
ID: 37765726
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.
0
 

Author Comment

by:isurgyn
ID: 37766503
<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??
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 37767901
<<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.
>>

  and that would be correct, because no error has occured, it's just that there is no data for it.

  As I mentioned in another comment, error 2501 really isn't an error, it's just saying that the opening of the report was canceled.  That may be an OK thing in some cases.

<<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.  >>

 You would get an outright error in the code, but error handling should be in place if you have any code in those events.

  If it is something the report sees (like report being too wide), you would get the OnError event of the report firing and the error that was encountered would be passed in through the DataErr argument.  This is true for forms as well by the way.

<<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.? >>

  It may be then as you suspected that it's a problem with the saving of the PDF file.  You should only get 2501 back if something went wrong.

  It would be helpfull to see the entire routine that calls the report.

  Last, and I really wouldn't suggest this, if getting the 2501 is your only problem, you could trap it and ignore it.

Jim.
0
 

Author Comment

by:isurgyn
ID: 37768544
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.
0
 

Author Closing Comment

by:isurgyn
ID: 37968413
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.
0

Featured Post

Integrate social media with email signatures

Is your company active on social media? Do you also use email signatures? Including social media icons in your email signature is a great way to get fans for free. Let all your email users know you’re on social media quickly and easily, in a single click.

Join & Write a Comment

The recent Microsoft changes on update philosophy for Windows pre-10 and their impact on existing WSUS implementations.
Possible fixes for Windows 7 and Windows Server 2008 updating problem. Solutions mentioned are from Microsoft themselves. I started a case with them from our Microsoft Silver Partner option to open a case and get direct support from Microsoft. If s…
This tutorial will show how to configure a new Backup Exec 2012 server and move an existing database to that server with the use of the BEUtility. Install Backup Exec 2012 on the new server and apply all of the latest hotfixes and service packs. The…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now