Error Report (Instead of Pop-Up Box) & Pop-Up Box saying "Macro Completed"
MS-ACCESS-2003: VBA Modules & Macro that runs them.
I have a Macro that does a number of steps. After the Macro has completed all other steps, I want to implement a "last step" in the Macro that will show a Pop-Up Box ( perhaps with a "happy-face" ) - that will say " Macro Completed".
Also - Step Number one - operates a VBA Module - where at the end of it - there will be a sign that will Pop-Up saying "Completed - No Errors" - or will show an Error Message reading in the Pop-Up Box if the Array did not fully execute. Only if you hit "OK" - will this pop-up box disappear and the Macro continue onwards to steps 2 thru 5.
How can I replace this Error Box or Completed with No Errors Box with a "report" that will export saying so - to a particular destination - that will also "not" stop the rest of the macro steps from proceeding ????
I already have "SetWarnings" to No - but, this pop-up box still comes up.
I do want to see the error-message from the Array - but, not have it stop the progress of the Macro (waiting for me to hit OK) nor do I want to have to sit there at my desk while this operates - waiting to hit "OK" when this Pop-Up Box comes up on my screen.
But, I do want to know "what the error-message will say" and "which run" the Error-Message Report that will generate is coming from......
There is a pathway that will always be constant: P:Prod:AUD:
This Macro works from a MS Access 2003 Database that will create & output many tables & query-definitoins to 4 new MS Access Databases to one location ( P:\Prod\AUD\ ) which will always remain the same. The mdb naming convention will start with the runner's initials followed by a common-segment followed by whatever the runner chooses to name it in particular (either month name, or test# , etc. - totally left to the runner/user's choice). The common segment will be 4 choices: AUD_FY2008 or MID_FY2008 or BUD_FY2008 or END_FY2008 .
An example of a full mdb name would be: RAJ_AUD_FY2008_TEST5
I want any or all four of these names indicated within that "error message report" - that will generate in place of the "pop-up box".
My goal is for steps 1 thru 5 of the Macro to run continuously and an error-report to generate with it for me to review. I also want a Pop-Up Box that will come-up at the very end when all steps of the Macro and that error-report have completed generating to say "Macro Completed" (with a happy face icon.... )
Can you guys please help me with this?
Thanking you for your time & help, Sincerely, Raj.
I have a Macro that does a number of steps. After the Macro has completed all other steps, I want to implement a "last step" in the Macro that will show a Pop-Up Box ( perhaps with a "happy-face" ) - that will say " Macro Completed".
Also - Step Number one - operates a VBA Module - where at the end of it - there will be a sign that will Pop-Up saying "Completed - No Errors" - or will show an Error Message reading in the Pop-Up Box if the Array did not fully execute. Only if you hit "OK" - will this pop-up box disappear and the Macro continue onwards to steps 2 thru 5.
How can I replace this Error Box or Completed with No Errors Box with a "report" that will export saying so - to a particular destination - that will also "not" stop the rest of the macro steps from proceeding ????
I already have "SetWarnings" to No - but, this pop-up box still comes up.
I do want to see the error-message from the Array - but, not have it stop the progress of the Macro (waiting for me to hit OK) nor do I want to have to sit there at my desk while this operates - waiting to hit "OK" when this Pop-Up Box comes up on my screen.
But, I do want to know "what the error-message will say" and "which run" the Error-Message Report that will generate is coming from......
There is a pathway that will always be constant: P:Prod:AUD:
This Macro works from a MS Access 2003 Database that will create & output many tables & query-definitoins to 4 new MS Access Databases to one location ( P:\Prod\AUD\ ) which will always remain the same. The mdb naming convention will start with the runner's initials followed by a common-segment followed by whatever the runner chooses to name it in particular (either month name, or test# , etc. - totally left to the runner/user's choice). The common segment will be 4 choices: AUD_FY2008 or MID_FY2008 or BUD_FY2008 or END_FY2008 .
An example of a full mdb name would be: RAJ_AUD_FY2008_TEST5
I want any or all four of these names indicated within that "error message report" - that will generate in place of the "pop-up box".
My goal is for steps 1 thru 5 of the Macro to run continuously and an error-report to generate with it for me to review. I also want a Pop-Up Box that will come-up at the very end when all steps of the Macro and that error-report have completed generating to say "Macro Completed" (with a happy face icon.... )
Can you guys please help me with this?
Thanking you for your time & help, Sincerely, Raj.
Raj_B_700,
First,
Unless you are 10 years old, let's forget the "smiley face".
Your question is quite complex.
Some of what you are asking for seems to indicate that you want the error handler to control "Program flow".
The error handler simply "Handles" the error and exits gracefully.
It is not designed to "Pause" execution, and allow you to continue.
If you look at a standard un-handled message, you have two options: Debug or End.
"Continue" is not an option.
Here are two good links on "Global Error Handlers", that might help you:
http://support.microsoft.com/kb/q191474/
http://www.fmsinc.com/TPapers/vbacode/Debug.asp#AdvancesErrorHandling
JeffCoachman
First,
Unless you are 10 years old, let's forget the "smiley face".
Your question is quite complex.
Some of what you are asking for seems to indicate that you want the error handler to control "Program flow".
The error handler simply "Handles" the error and exits gracefully.
It is not designed to "Pause" execution, and allow you to continue.
If you look at a standard un-handled message, you have two options: Debug or End.
"Continue" is not an option.
Here are two good links on "Global Error Handlers", that might help you:
http://support.microsoft.com/kb/q191474/
http://www.fmsinc.com/TPapers/vbacode/Debug.asp#AdvancesErrorHandling
JeffCoachman
ASKER
Hi, Thanks for the links... I'm studying them in detail.
My Macro does "stop" when that Error-Message-Pop-Up Box comes up. Unless you hit OK, the Macro will not proceed to it's next step - which is to 'open' a make-table query. There are total 5 steps in that Macro - out of which that Array is the 1st which has the error-message-box displaying at the end with a 'OK' button to hit.
The simplest thing here that I want to do is for the 'OK' to be automatically hit or clicked or activated - after the pop-up box has opened. That way - once that's gone, my Macro proceeds further.
According to what you have said - error handlers do not have a 'continue' option. So, I presume that the 'OK' stands for an 'end' function?
Can you provide me the simplest code that I could write at the very end of that Module coding - where I would tell it to accept the OK automated - without me having to sit here and wait for it to pop-up?
Thanks, sincerely, Raj.
My Macro does "stop" when that Error-Message-Pop-Up Box comes up. Unless you hit OK, the Macro will not proceed to it's next step - which is to 'open' a make-table query. There are total 5 steps in that Macro - out of which that Array is the 1st which has the error-message-box displaying at the end with a 'OK' button to hit.
The simplest thing here that I want to do is for the 'OK' to be automatically hit or clicked or activated - after the pop-up box has opened. That way - once that's gone, my Macro proceeds further.
According to what you have said - error handlers do not have a 'continue' option. So, I presume that the 'OK' stands for an 'end' function?
Can you provide me the simplest code that I could write at the very end of that Module coding - where I would tell it to accept the OK automated - without me having to sit here and wait for it to pop-up?
Thanks, sincerely, Raj.
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 detailed response. I understand what you are saying.
I'm doing this - as I'm ammending something that is existing - that is not working well anymore - but, is still being retained as is with workarounds. Hence, a Macro that executed 10 Arrays - is now segregated into 8 executing from the 1st Module, and #9 and #10 executing from subsequent module vba coding.
But, that original Array has this error-msg-box that pops up. Generally, it does not show any errors and will read completed without errors. My Manager does not want this removed. But, it is holding up the future steps of the macro from executing - because it expects my Manager to hit OK. My Manager does not want to have to sit there to watch for the msg-box to come-up and for him to hit OK - and then for it to proceed further - where he has to then again come back to retrive the results. He just wants to let it run - and come back once-for-all when it has fully completed. There are seldom any errors - and hence - he is not concerned that that initial pop-up box.
Sorry - this is so convoluted....
Any simple way that I can tell it to just default the OK as being hit by my Manager within a matter of seconds? That way - it appears, disappears, and the Macro proceeds further.
Thanking you for your timely feedback & help.... sincerely, Raj.
I'm doing this - as I'm ammending something that is existing - that is not working well anymore - but, is still being retained as is with workarounds. Hence, a Macro that executed 10 Arrays - is now segregated into 8 executing from the 1st Module, and #9 and #10 executing from subsequent module vba coding.
But, that original Array has this error-msg-box that pops up. Generally, it does not show any errors and will read completed without errors. My Manager does not want this removed. But, it is holding up the future steps of the macro from executing - because it expects my Manager to hit OK. My Manager does not want to have to sit there to watch for the msg-box to come-up and for him to hit OK - and then for it to proceed further - where he has to then again come back to retrive the results. He just wants to let it run - and come back once-for-all when it has fully completed. There are seldom any errors - and hence - he is not concerned that that initial pop-up box.
Sorry - this is so convoluted....
Any simple way that I can tell it to just default the OK as being hit by my Manager within a matter of seconds? That way - it appears, disappears, and the Macro proceeds further.
Thanking you for your timely feedback & help.... sincerely, Raj.
Raj_B_700,
I still don't understand the code.
Sorry I could not help more.
JeffCoachman
I still don't understand the code.
Sorry I could not help more.
JeffCoachman
ASKER
Thanks for the detailed feedback.... It's ok - I'll work on removing this error message thing from the Array. Thanking you for your timely help, sincerely, Raj.
ASKER
ErrorExportAUD:
Set qry = Nothing
Set dbn = Nothing
ERRID = ERRID Or 1
DoCmd.Hourglass False
For Each err In Errors
MsgBox err.Number & vbCrLf & err.Description
Next
End Sub
ErrorExportBUD:
Set qry = Nothing
Set dbn = Nothing
ERRID = ERRID Or 2
DoCmd.Hourglass False
For Each err In Errors
MsgBox err.Number & vbCrLf & err.Description
Next
End Sub
ErrorExportMID:
Set qry = Nothing
Set dbn = Nothing
ERRID = ERRID Or 8
DoCmd.Hourglass False
For Each err In Errors
MsgBox err.Number & vbCrLf & err.Description
Next
End Sub
ErrorExportEND:
Set qry = Nothing
Set dbn = Nothing
ERRID = ERRID Or 4
DoCmd.Hourglass False
For Each err In Errors
MsgBox err.Number & vbCrLf & err.Description
Next
End Sub
So - I would just want these to be coming via a report that generates (without any pop-up coming up that I will have to hit ok on). I guess that there is a default set somewhere that if none of these four have any errors, then the pop-up message will say "Completed without any errors". I can't spot where in the module coding it is telling it to generat a 'no errors" pop-up. This (no errors message) also - should just come via the report - and 'not' as a popup box.
The only pop-up box I want is at the very end of the Macro - to indicate that "Macro Completed" (with a happy face).....
** Oh.... Can I change those numbers by replacing them with the segment name??? Like "Error in AUD" or "Error in MID" , etc.
Thanking you for your timely help, sincerely, Raj.