Save Changes Warning no longer popping up

[willknapp]

[SOLVED] Save Changes Warning no longer popping up

Periodoically, the "Save Changes?" warning that pops when closing a query whose design has changed stops popping up, and the changes are automatically saved. I've tried going into the immediate window to change SetWarnings to True, but that doesn't fix the issue. (Further, I've scoured my code for instances of "DoCmd.SetWarnings False" to make sure that they are always followed by a "Docmd.SetWarnings True" in the Exit lines.

Has anyone run into this problem before? I've tried searching a number of different sights, and all I can find are instances of people talking about wanting to disable the Query Action warnings, which I don't want to do. I just want to be able to fiddle with queries, but then close them without saving in case I butcher something. I could create a copy of the query and fiddle with that, but I shouldn't have to...

 

[gemma-the-husky]

if you turn it off in code, you need to turn it back on in code

?docmd.setwarnings true

in the immediate window should work OK.

 

[GinaWhipp]

I have see this happen if the query fails AND you have *On Error Resume Next* as your error handling. It never reaching the *DoCmd.SetWarnings True* line. (Another example of why one shouldn't use that line for Error Handling.)

 

[willknapp]

if you turn it off in code, you need to turn it back on in code

?docmd.setwarnings true

in the immediate window should work OK.

That's what I thought, but it does not. I've tried it and the warning is still disabled. And like I said, every procedure I have in code finishes with a docmd.setwarnings true statement just before the exit line. Every procedure passes through that regardless of any errors (due to handling), so I don't think it's caused by the code...

 

[willknapp]

I have see this happen if the query fails AND you have *On Error Resume Next* as your error handling. It never reaching the *DoCmd.SetWarnings True* line. (Another example of why one shouldn't use that line for Error Handling.)

Well, it should work under these conditions, right? (I use Resume Next during development so when I break during an error, I F8 to the line immediately below the offending code.):

Private Sub MyProc()
10     On error goto PROC_ERR
 
100    DoCmd.SetWarnings False
110    [COLOR=green]'Offending Code which includes an action query whose warnings I want to suppress[/COLOR]
 
PROC_EXIT:
9000   DoCmd.SetWarnings True
9100   Exit Sub
 
PROC_ERR:
9200   GlobalErrHandler
9300   Resume Next

 

[GinaWhipp]

Nope, the Procedure never hits PROC_EXIT, so your warnings never get turned back on. Perhaps this will help...

http://www.accessmvp.com/strive4peace/Code.htm

What I do is write *MyProc* with no error handling during development, providing it's one I have used before... Let it *break* on any and all issues so I cn *fix* it then add appropiate error handling.

 

[willknapp]

Nope, the Procedure never hits PROC_EXIT, so your warnings never get turned back on. Perhaps this will help...

http://www.accessmvp.com/strive4peace/Code.htm

What I do is write *MyProc* with no error handling during development, providing it's one I have used before... Let it *break* on any and all issues so I cn *fix* it then add appropiate error handling.

OK - this is weird, because when I go through the code line by line, it always hits PROC_EXIT, even if the code is thrown off by an error with a SQL statement. For the purposes of brevity, I left out a procedure at the top that adds the formname to an array used by the error handler, and the PROC_EXIT portion includes the code that removes it from the array, so I know that, at least in the case of every procedure I've developed, the PROC_EXIT lines are processed...

This is what the code actually looks like:

Public Sub TestSQLErrorHandling()
10     On Error GoTo PROC_ERR
20     PushCallStack "TestSQLErrorHandling"
 
100    DoCmd.SetWarnings False
110    DoCmd.RunSQL "DELETE * FROM tblNotThere WHERE fldNotThere = 'Nothing'"
120    MsgBox "Have a GREAT day!"
 
PROC_EXIT:
9000   PopCallStack
9010   DoCmd.SetWarnings True
9100   Exit Sub
 
PROC_ERR:
9200   GlobalErrHandler
9300   Resume Next

When this code is run, this message (created and called by the GlobalErrHandler procedure) pops up:

Procedure: TestSQLErrorHandling
Line: 110
Error: 3078 - The Microsoft Office Access database engine cannot find the input table or query 'tblNotThere'. Make sure it exists and that its name is spelled correctly.
​

If I click "OK" on the error message, my "Have a GREAT Day" message box pops up, so I know it goes to the next line.

Instead of clicking OK, if I type ctrl-break, I click F8 and go line by line through the rest of the error handler, and it eventually takes me back to this procedure's next line, which is the Great Day message box, and then through PROC_EXIT where the SetWarnings True statement is processed.

What am I doing differently that my PROC_EXIT is being hit, whereas you're saying it won't?

 

[willknapp]

Some other interesting information - it's only for this one database. When I open a different database (while leaving the bad one open) the error messages work as they should. Is there a "Current Database" setting for this sort of thing?

 

[GinaWhipp]

I can only tell what I have seen happen...

I too can get the message *Have a great day !* pop up which suggests to me it is doing the *Resume Next* portion is working and yes, it will process the balance of the code. However, I have seen it skip over it, no ryhme or reason and honestly, I never bothered to investigate why. I just know I have seen it happen, so much so that I add this...

Resume MyErrExit

...to my

MyErr:

...just to be safe.


There are Current Database option under Options which will be located under File>Options or maybe Tools > Options, depends which version of Access you are using.

 

[willknapp]

I can only tell what I have seen happen...

I too can get the message *Have a great day !* pop up which suggests to me it is doing the *Resume Next* portion is working and yes, it will process the balance of the code. However, I have seen it skip over it, no ryhme or reason and honestly, I never bothered to investigate why. I just know I have seen it happen, so much so that I add this...

Resume MyErrExit

...to my

MyErr:

...just to be safe.


There are Current Database option under Options which will be located under File>Options or maybe Tools > Options, depends which version of Access you are using.

You mean Access can, on occasion, act erratically? I'm shocked! Shocked, I tell you...

For the record, I'm on 2007 working in Win7 Pro.

I solved the issue...sort of. When I noticed I wasn't having any problems with any othe databases, I looked at the "OnOpen" procedure I have in place for the Display On Open form - no instances of SetWarnings anywhere in the procedure or any of the called procedures.

Just to be on the safe side, I did a shift+open to keep the form from loading, then tested the error messages - they worked! OK, so I've now discovered that it has something to do with the form being loaded. I then closed the database and reopened and...

The error messages are now working fine. Not sure what it was, but shift+opening seemed to reset it. My guess is there was something residual that got cleaned up during the process, but that's just a wild shot in the dark. Point is, it's now working fine, and I know how to fix it when it happens again.

Thanks for your help everyone!!!

 

[willknapp]

Interesting side benefit - a lot of forms that were taking a looooooooong time to open and save are now working much faster. I've gotta try this shift-open thing about as often as I compact and repair, I guess...

 

[willknapp]

How do I mark this as solved?

 

[GinaWhipp]

Found this posted by Bob Larson...
There is a drop down next to the subject line when you post from ADVANCED MODE. So, if you go back to your original post, click EDIT and then click GO ADVANCED, you should see a drop down to the left of the subject text box which has values of (no prefix), TIP, Question, Solved. Just change it there and save your post.

 

[willknapp]

Found this posted by Bob Larson...
There is a drop down next to the subject line when you post from ADVANCED MODE. So, if you go back to your original post, click EDIT and then click GO ADVANCED, you should see a drop down to the left of the subject text box which has values of (no prefix), TIP, Question, Solved. Just change it there and save your post.

Marvelous - thanks! Except when i did that, there was no drop down list. :-(