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!)

[topher23]

Sorry, linq, but a second On Error statement simply overrides the previous On Error statement. I've used the method in subroutines where different errors need to be handled differently depending on what code segment is running. While good programming practice may indicate that a different error handler should mean a different subroutine, writing 4 or 5 subroutines that get called, in sequence, only once when a single event fires is overkill.

Juliet, Smiley, I think linq is just grumpy because he looked at Smiley's first post and realized you might be poking some good-natured fun at him. Unfortunately, I'm not sure linq is good-natured, as he appears to be a razorback gorilla.

[Stewart Ross]

Hi all. The second On-error statement simply overrides the first:

On Error Goto {some err handler} ' has no effect, as it is overridden by...
On Error resume next

For so long as resume next is active then clicking save will appear to do nothing if the DoCmd.RunComman d fails. as Resume Next will execute the statement immediately after the DoCmd - the Exit Sub.

Re your comment in post 7 Juliet It is not necesary to set focus before setting a field value in code - you can set any control you like via VBA as long as it would be valid to do so. Be aware that the After Update event is occurring after the control has lost focus, so the event sequence for your Address update to Save Button click will be something like Lost Focus - After Update - Set Focus on Save button - Set Focus on Adress Country (not sure which of these will win!). My guess is that it is a clash of setfocus requests which is causing the first failure you experience.

Rather than use the RunCommand option to save a record I'd go for the simpler one of testing the form's Dirty property and resetting it (it is set true if the record has changed, and setting it false saves the current record).

IF Me.Dirty THEN 
  Me.Dirty = False
End If

-Stewart

[julietbrown]

How the plot thickens!

That's good stuff, Stewart, thanks. It prompted me to do some experiments, and the results indicate (a) I was wrong about something pretty basic, and (b) it's not really surprising!

I made a little form with a combo box called SuitCombo, based on a table containing ...
1 "Spades" "Black"
2 "Hearts" "Red" .... etc, four suits

I put a text box on the form called SuitText.
Then I wrote this code for SuitCombo_Chang e ...

Private Sub SuitCombo_Change()
    Dim pos As Integer
    pos = SuitCombo.ListIndex
    If SuitCombo.Column(2, pos) = "Red" Then
        SuitText.ForeColor = vbRed
    Else
        SuitText.ForeColor = vbBlack
    End If
    'next line raises error 2185
    SuitText.Text = SuitCombo.Column(1, pos)
End Sub

Error 2185 says "You can't reference a property or method for a control unless the control has the focus."

Coming from a nonVB programming background (insofar as I have anything so glorious as a 'background' in programming) I've always had to set/get properties of objects (not "whole objects"), so have always assumed that if I wanted to set the value of a text box in code I needed to reference its .Text property, as in the code above.

However, if I change the last line of the code to ...
SuitText = SuitCombo.Colum n(1, pos)
then there is no error message

There appears to be some inconsistency in Access's requirements of the coder, doesn't there? Because referencing the .ForeColor property of the text box doesn't raise this error, nor as it turns out (another experiment) does referencing the .Value property. What's so special about the .Text property?

Anyway, I've obviously been under a misapprehension , and can now simplify some of my existing code to refer simply to a text box object by name without the qualifier .Text.

On a separate point, what you say about setting "dirty" to false is interesting, because a while back I was writing my own Save routines, and using this little "trick", but one of the Bytes experts ticked me off and told me to use the Wizard/macros. I have changed to doing this, partly because I was a bit uneasy about telling Access whether the form was dirty or not, in case the user had snuck in and dirtied something "while the code wasn't looking" (a bit anthropomorphic ?) But I will remember your hint about this, and do some experiments with it some time soon.

[TheSmileyCoder]

While im a bit unsure what the .Text property actually does, I know you need to use .Value for what you were trying to do.

If you reference a textbox or combobox without adding the .Value, the .Value is assumed to be what you want. While somewhat inconsistent as you write, it does make for easier code, with less text to be written.

[julietbrown]

I suppose the .Text property is there for just the situation you describe: i.e., both MyCombo and MyCombo.Value return the bound column of the combo box, but MyCombo.Text returns the "writing" showing up in the box? I still don't know why I can't get at it without putting the focus onto the combo box!

Oh well, maybe it's time to find something new to get confused about!