Set Focus causes RunTime Error 2108

[ResearchRN]

I have three text boxes to record tumor dimensions in millimeters. I want them entered from largest to smallest going left to right. I’ve added code to the BeforeUpDate event in the second text box that checks to make sure the number entered is smaller than the number entered in the first text box. If it is not, I want to cancel the update, display a message, and set the focus back to the first text box.



My code looks like this:

If Me.tumorDim2 > Me.tumorDimLargest Then
   Me.Undo
   MsgBox "Tumors dimensions must be entered from largest to smallest" & vbCrLf & "Please enter the   tumor dimensions in the correct order.", _
   vbInformation, "Data Validation Check Failed."
   Me.tumorDimLargest.SetFocus
End if

The last line causes Runtime Error 2108 “You must save the field before you execute the GoToContol action, the GoToControl method, or the SetFocus method.”
I’ve tried saving the field with DoCmd.RunCommand acCmdSaveRecord. That doesn’t work.
I’ve tried inserting “If Me.Dirty Then Me.Dirty = False” . That doesn’t work.
I’ve tried moving the code to the BeforeUpDate event on the form. This causes RTE 2115 “The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing Database from saving the data in the field.”
I've tried replacing the Me keyword. That doesn't work.

I'm out of options. Can someone please help?

Thanks

[RayMilhon]

Where is this piece of code located?

[John_G]

Hi -

Since your code is in the Before_Update event, you must also cancel the event with Cancel = -1 as well as using me.undo.

Note that me.undo resets the whole form, not just the field with the error - is that really what you want?

John

[Rod]

Some thoughts.

Me.Undo resets only the bound controls, although as John_G points out, it resets all the bound controls on the form and not just the one that triggered the event.

Using the Before Update event means that VBA must complete the update action or be cancelled before any other action/event can take place. This means you cannot move away from the control (e.g. go to another control, set focus on another control) until the Before Update event has completed. You cannot change the value of the control - Me.Undo is an exception - until the update action has completed.

Thus the only useful things you may do within a Before Update are to undo/reset all bound control values and/or cancel the event. If you issue only a Me.Undo then I assume the update takes place with the original value - i.e. no effect - and a cancel is not strictly necessary.

Personally I rarely use a control's before update event preferring to check all data validation at the form's before update event at which time much more flexibility is afforded.

[ResearchRN]

Hi -

Since your code is in the Before_Update event, you must also cancel the event with Cancel = -1 as well as using me.undo.

Note that me.undo resets the whole form, not just the field with the error - is that really what you want?

John

Thanks for your relply. I tried adding Cancel = -1. Same message. I would prefer to reset the field with the error and not the whole form. But I'm not sure how to do it.

[ResearchRN]

Rod,
Thanks for your reply. I tried placing the code in the form's before update event but it causes RTE 2115: “The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing Database from saving the data in the field.” I have no idea why.

[ResearchRN]

Hi -

Since your code is in the Before_Update event, you must also cancel the event with Cancel = -1 as well as using me.undo.

Note that me.undo resets the whole form, not just the field with the error - is that really what you want?

John

Okay, I think I've learned my lesson regarding the limitations of a control's Before UpDate event. I entered the following code in the After UpDate event of the second text box and it works:

If Me.tumorDim2 > Me.tumorDimLargest Then
    MsgBox "Tumors dimensions must be entered from largest to smallest" & vbCrLf & "Please enter the tumor dimensions in the correct order.", _
    vbInformation, "Data Validation Check Failed." 

    Me.tumorDim2.Value = ""
    Me.tumorDimLargest.SetFocus

End If

Only the text box that is in error is reset as opposed to resetting the whole form with Me.undo in the Before update event. Also, the Set Focus method works. I'm not sure if Me.tumorDim2.Value="" is the right way to undo the erroneous entry but it seems to work.

[Rod]

I'm not sure if Me.tumorDim2.Value="" is the right way to undo the erroneous entry but it seems to work.

Nothing wrong with that. If you try Me.tumorDim2.Undo then you will find the new value has been 'saved' to the control and nothing happens.

A mute point however, and nothing that harms this solution, but Me.tumorDim2.Value triggers another pass through the After Update code. This can be a subtle way of invoking infinite recursion. Beware! Why bother to reset the control value? Let the user do it.

[ResearchRN]

Nothing wrong with that. If you try Me.tumorDim2.Undo then you will find the new value has been 'saved' to the control and nothing happens.

A mute point however, and nothing that harms this solution, but Me.tumorDim2.Value triggers another pass through the After Update code. This can be a subtle way of invoking infinite recursion. Beware! Why bother to reset the control value? Let the user do it.

Good advice. Thanks very much.