Why does 'On error resume next' make me click Save twice? (No error?)

[julietbrown]

Can you boost my understanding of Access? I don't like things I can 'cure' but don't understand why!

I've converted the Save macro on my form to VBA. It looks like this ... as I'm sure you already know.

Private Sub SaveChanges_Click()
On Error GoTo SaveChanges_Click_Err
 
    On Error Resume Next
    DoCmd.RunCommand acCmdSaveRecord
 
SaveChanges_Click_Exit:
    Exit Sub
 
SaveChanges_Click_Err:
    MsgBox Error$
    Resume SaveChanges_Click_Exit
 
End Sub
 

For some reason, with the code as it stands, you have to click the Save button twice. The first time nothing happens at all (no error message, and the button doesn't do the "I've been clicked" little wobble) and you have to click it again ... then it "wobbles properly" and saves the record. However, if I comment out the line
"On Error Resume Next"
the problem goes away. I've read about Resume Next, and I think I understand it. I can see that if there WAS an error the code would/might behave as I've described(?), but there doesn't seem to be anything wrong. The changes I make to the record could be as little as removing one word from a "Comments" field. Surely there can't be anything wrong, otherwise Save wouldn't work the second time? Baffled (again)

Can you guys explain this?

[TheSmileyCoder]

Im going to guess you might have some code in your before_update event of the form? Im guessing something in there might be causing an error the first time the code is run, therefore not saving the record.

If you have code in your before_Update, try posting it.

[julietbrown]

Hello again, Smiley!

You pointed me in the right sort of direction, but I tracked the problem down to

 
Private Sub Addr1_AfterUpdate()
        'if user fills in field Addr1, and field AddrCountry is blank, 
     If (Len(Nz(Me.Addr1)) <> 0) And (Len(Nz(Me.AddrCountry)) = 0) Then
             'put the default value "UK" into AddrCountry
             Me.AddrCountry.SetFocus
             Me.AddrCountry = "UK"
    End If
End Sub
 

I worked out the problem is HERE by testing Save on different bits of the form and seeing that I had to click save twice only if the last thing I did was modify Addr1. (The code above has been modified since that discovery to minimise the occasions on which the two "Me." lines need to execute.) I can see what's happening ... the 'focus' has left the Save button, and even if I add another line to the above ...
Me.SaveChanges. SetFocus
... I still have to click Save twice because it 'lost the focus' in between.

I'm sure I can sort this out in a different way. But I'm still curious ...

Can you explain to me why Access won't let one change the value of a field on a form in code unless it has 'the focus'? I've never been able to see a good reason for that ... I'd find it less irritating if you could tell me the reason!

Thanks
Juliet

PS I've found out why I need 'Resume Next'! Without it, if BeforeUpdate does a Cancel/Exit I get a message to say "The RunCommand was cancelled'. Things become a little clearer day by day!!!

[TheSmileyCoder]

Well it might be somewhat related to this, my first post in Bytes.

Try running some debug/msgbox statements to determine whether the addr_afterupdat e is runned before or after the save command.

[julietbrown]

Oh my God! I haven't laughed so much for ages!! (Just read your first post! I'm still laughing!) Honestly, if my first one had provoked such a shoolmasterly string of admonishments and chiding I don't think I'd ever have dared come back! You were brave to persist and I like your style. I got told off a few times, too, but not so ferociously.

Yes, you are quite right, your problem there was EXACTLY the one I've been having. Anyway, I've solved it now by making "UK" the default value for that field ... I set up the table a few weeks ago, when I was really an Access beginner, so that simple solution didn't occur to me back then. (I note Bytes has now changed me from 'Newbie' to 'Member', so I'm feeling very grand ... all ready to be slapped again soon, I expect.)

Best wishes ... keep smiling!

[TheSmileyCoder]

Well the only annoying thing here is that I thought it was related to the dirtying of the form, because for me it would only happen if the form was not allready dirty (and the code would dirty the form). Im not sure, but it doesn't sound like you have the "exact" same issue.

So either it means I was wrong in how the button lost its "click" focus/event, which bugs me. I do like to know why things do what do they :)

[julietbrown]

No, you were not wrong ... if I understood your first Byte Q. And yes, it was the same problem as I was having.

I reckon this is how it is:

In my case, it was Addr1_exit that caused the problem, but that code could have just as well been in BeforeUpdate for the form. The effect would have been exactly the same. (I haven't tried it, because I now understand what's happening and I KNOW the same thing would happen.)

The problem will be caused by any code that runs after Save has been clicked which grabs the focus away from the Save button. When you click it the first time it gets the focus BACK so is NOW prepared to let you click to Save the record! In my case, the problem only occured when my 'exit' from Addr1 was the last thing I did before clicking save. Addr1 only knew I'd 'exited' when I clicked Save, so it then did it's code (taking the focus off Save and making me have to click it again).

I worked this out when I noticed that if I updated Addr1 and then did some other change to the record and THEN clicked Save the problem didn't arise.

I don't think I explained this very well! I hope you understand, because, like you, I really HATE not knowing why things happen.

I also thought it was something to do with dirtying the form, and I wasted ages trying to see why that would cause the problem once but not again and again.

What is STILL bugging me is why code has to give the focus to a field before it's allowed to update it. I've never been able to find a good reason for that. Do you know?

[TheSmileyCoder]

Well im not sure it has to give focus to the field. A field that has been disabled and locked for instance, you cannot set focus to it, but you can update it through code.

If I "combine" your facts with mine, it seems if a AfterUpdate event of a textbox(or similar) is run as you click the button, AND that afterupdate sets another field then Access "forgets" you clicked the button, or the button never gets focus. Maybe I will find time to play around with it more.

[missinglinq]

I've gotten dizzy trying to follow all of this, but the reason for having to click the Save button twice is obvious, as is curing the problem by commenting out

On Error Resume Next

With the statements

On Error GoTo SaveChanges_Cli ck_Err

On Error Resume Next

you're telling Access to do two different things if an error occurs, which is exactly what it was doing!

[julietbrown]

Wot?!
No, that's NOT the reason. If it was, the problem would occur whichever field I last edited, not just for the particular one that has an on-exit routine.

Also, the routine with "Resume ... " etc in it wasn't ME telling Access to do anything, it is the virgin code (i.e. I never touched it, guv, honest!) generated when you tell Access to convert the Save macro to VBA. If you comment out the "Resume Next" line, then if the AfterUpdate routine does a "Cancel = True, Exit Sub" this Save routine gives you an error message saying "The RunCommand was cancelled". (Note that in this case it hasn't even attempted to actually execute the "RunCommand ".) It gives the OTHER message when it actually tries to do the save and fails for some reason ... That's when it "goes to SaveChanges_Cli ck_Err" label cose.

I know I'm being pretty damn cheeky about this (as only just magically turned from a newbie into a member!)