By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,946 Members | 773 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,946 IT Pros & Developers. It's quick & easy.

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

P: 99
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub SaveChanges_Click()
  2. On Error GoTo SaveChanges_Click_Err
  4.     On Error Resume Next
  5.     DoCmd.RunCommand acCmdSaveRecord
  7. SaveChanges_Click_Exit:
  8.     Exit Sub
  10. SaveChanges_Click_Err:
  11.     MsgBox Error$
  12.     Resume SaveChanges_Click_Exit
  14. 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?
Feb 17 '10 #1

✓ answered by TheSmileyCoder

To make it a bit clearer. If you have a combobox with values such Spades, Hearts and so on, and you start typing into the combobox "Spa", then the .Text would be "Spa"

Share this Question
Share on Google+
18 Replies

Expert Mod 100+
P: 2,321
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.
Feb 17 '10 #2

P: 99
Hello again, Smiley!

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

Expand|Select|Wrap|Line Numbers
  2. Private Sub Addr1_AfterUpdate()
  3.         'if user fills in field Addr1, and field AddrCountry is blank, 
  4.      If (Len(Nz(Me.Addr1)) <> 0) And (Len(Nz(Me.AddrCountry)) = 0) Then
  5.              'put the default value "UK" into AddrCountry
  6.              Me.AddrCountry.SetFocus
  7.              Me.AddrCountry = "UK"
  8.     End If
  9. 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 ...
... 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!


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!!!
Feb 17 '10 #3

Expert Mod 100+
P: 2,321
Well it might be somewhat related to this, my first post in Bytes.

Try running some debug/msgbox statements to determine whether the addr_afterupdate is runned before or after the save command.
Feb 17 '10 #4

P: 99
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!
Feb 17 '10 #5

Expert Mod 100+
P: 2,321
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 :)
Feb 17 '10 #6

P: 99
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?
Feb 17 '10 #7

Expert Mod 100+
P: 2,321
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.
Feb 17 '10 #8

Expert 2.5K+
P: 3,532
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_Click_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!
Feb 17 '10 #9

P: 99
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_Click_Err" label cose.

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

Expert 100+
P: 234
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.
Feb 17 '10 #11

Expert Mod 2.5K+
P: 2,545
Hi all. The second On-error statement simply overrides the first:

Expand|Select|Wrap|Line Numbers
  1. On Error Goto {some err handler} ' has no effect, as it is overridden by...
  2. On Error resume next
For so long as resume next is active then clicking save will appear to do nothing if the DoCmd.RunCommand 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).

Expand|Select|Wrap|Line Numbers
  1. IF Me.Dirty THEN 
  2.   Me.Dirty = False
  3. End If
Feb 17 '10 #12

P: 99
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_Change ...

Expand|Select|Wrap|Line Numbers
  1. Private Sub SuitCombo_Change()
  2.     Dim pos As Integer
  3.     pos = SuitCombo.ListIndex
  4.     If SuitCombo.Column(2, pos) = "Red" Then
  5.         SuitText.ForeColor = vbRed
  6.     Else
  7.         SuitText.ForeColor = vbBlack
  8.     End If
  9.     'next line raises error 2185
  10.     SuitText.Text = SuitCombo.Column(1, pos)
  11. 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.Column(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.
Feb 18 '10 #13

Expert Mod 100+
P: 2,321
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.
Feb 18 '10 #14

P: 99
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!
Feb 18 '10 #15

Expert Mod 100+
P: 2,321
You can set the .value without the combobox having focus.

You can use the Text property to set or return the text contained in the text box portion of a combo box. Read/write String.


expression A variable that represents a ComboBox object.


You can set the Text property to the text you want to display in the control (control: A graphical user interface object, such as a text box, check box, scroll bar, or command button, that lets users control the program. You use controls to display data or choices, perform an action, or make the user interface easier to read.). You can also use the Text property to read the text currently in the control.

To set or return a control's Text property, the control must have the focus (focus: The ability to receive user input through mouse or keyboard actions or the SetFocus method. Focus can be set by the user or by the application. The object that has focus is usually indicated by a highlighted caption or title bar.), or an error occurs. To move the focus to a control, you can use the SetFocus method or GoToControl action.

While the control has the focus, the Text property contains the text data currently in the control; the Value property contains the last saved data for the control. When you move the focus to another control, the control's data is updated (update: To accept changes to data in a record. The changes are saved in the database when you move to another record on a form, datasheet, or data access page, or when you explicitly save the record.), and the Value property is set to this new value. The Text property setting is then unavailable until the control gets the focus again. If you use the Save Record command on the Records menu to save the data in the control without moving the focus, the Text property and Value property settings will be the same.


The following example uses the Text property to enable a Next button named btnNext whenever the user enters text into a text box named txtName. Anytime the text box is empty, the Next button is disabled.

Visual Basic for Applications
Expand|Select|Wrap|Line Numbers
  1. Sub txtName_Change()
  2.     btnNext.Enabled = Len(Me!txtName.Text & "")<>0
  3. End Sub 
Feb 18 '10 #16

Expert Mod 100+
P: 2,321
To make it a bit clearer. If you have a combobox with values such Spades, Hearts and so on, and you start typing into the combobox "Spa", then the .Text would be "Spa"
Feb 18 '10 #17

Expert Mod 15k+
P: 31,342
You really don't want to catch him on a bad day :D
Feb 19 '10 #18

Expert Mod 15k+
P: 31,342
That's a sign that you're learning. Very gratifying.
Feb 19 '10 #19

Post your reply

Sign in to post your reply or Sign up for a free account.