How to check whether SetWarnings is On or Off?

[KeithWilliams]

Hi,

I have a subroutine where I need to know whether SetWarnings was on or off, so I can turn it off if necessary, and restore it to its prior state before returning to the calling script.

In other words, I need to suppress warnings in the subroutine, but not affect the status of warnings in the calling program. Sort of a GetWarnings () function call - but that doesn't exist...

Any help would be much appreciated!

Keith.

 

[ghudson]

I took a peek and I do not see how you can check if setwarnings is on or off.

Why not code the setwarnings on or off where needed?

 

[KeithWilliams]

Hi,

Thanks for your comments. The main need for this is to handle error logging. I have an automated "refresh" process that will run daily. Any errors need to be logged into a table without interrupting the refresh process. The refresh process needs to run unattended, so no warnings or errors should be displayed.

Therefore most of the refresh logic is preceded by the command:

On Error GoTo LogError

Then I handle errors thus:

LogError:
    DoCmd.SetWarnings False
    DoCmd.RunSQL "INSERT INTO SAP_MessageLog (LogMsg) Values (" & Chr$(34) & Err.Number & ":" & Err.Description & Chr$(34) & ")"
    DoCmd.SetWarnings True
    Resume Next

However, I also need to suppress warnings, such as "you are about to run an append query that will append 10765 rows to the database" to allow the process to run unattended. Wrapped around such queries I code:

    DoCmd.SetWarnings False
    'Run 1st query here.
    'Run 2nd query here.
    DoCmd.SetWarnings True

The problem arises when the first query generates an error, as Access then prompts me to confirm that I want to run the 2nd query, because the LogError process leaves the Warnings switched on. This is no good for an unattended process.

I may be forced to turn off warnings altogether for the duration of the script, but this will obviously make debugging harder.

Thanks again,
Keith.

 

[Mile-O]

I'd much rather use DAO to put errors into a table than convert a string to an SQL statement due to the increase your method would cause to the database size.

 

[KeithWilliams]

Hi again,

Sorry, I don't understand the difference between using DAO vs RunSQL, I think the result would be the same, a row is added to the SAP_MessageLog table. The size of the table will be negligible in any case compared to the other data tables I use.

Thanks,
Keith.

 

[Mile-O]

Sorry, I don't understand the difference between using DAO vs RunSQL

The following is not a query:

"INSERT INTO SAP_MessageLog (LogMsg) Values (" & Chr$(34) & Err.Number & ":" & Err.Description & Chr$(34) & ")"

As it stands it is a string. To perform the query the string has to be converted to a query and, once used, the query is then deleted. When you delete objects from a database the space they occupied is not reclaimed until the database is compacted.

Thus opening a table and inserting values via DAO or ADO (or a stored QueryDef) does not create and delete new queries each time they are executed keeping the database size down.

 

[KeithWilliams]

Oh, thanks. I will look into that. I guess there is no answer to my original question!

Keith.

 

[KenHigg]

Keith, I looked and could not find out where Access stores this setting and how to get to it. I think the best method would be to only turn them off and back on within the same module and not leave them off across pcs of code. I would also include a turn on command as part of the error checking code if an error occurs while the the warnings switch was turned off.

kh