Keep cursor at a subform control that was just just deleted with module code

[gg80]

Using A2007.
I have a Subform wherein a user enters a number in a field. If the number is outside of a permitted range, he gets a message and his entry is deleted. The range is dependent on other factors, so can’t do it with simple table validation rules.



I am using VBA in a module (several subforms with same procedure) to do this. How do I get the cursor to stay in the field that was just deleted so that user can insert a proper number without using his mouse?. I tried a goto command to another field in the same record followed by goto field just deleted. It works most of the time, but not always. I sometimes get a message saying that there is no such field on the first goto. Very puzzling. Any help much appreciated.

[June7]

Are you using code in the control BeforeUpdate event? Here is an example:

Private Sub tbxLabNum_BeforeUpdate(Cancel As Integer)
If IsNull(DLookup("LabNum", "Submit", "LabNum='" & Me.tbxLABNUM & "'")) Then
MsgBox Me.tbxLABNUM & " is not valid Lab Number", , "EntryError"
Cancel = True
Me.tbxLABNUM.SelStart = 6
End If
End Sub

[gg80]

Embarrassed to say wasn't using BeforeUpdate- Was trying to do it AfterUpdate. Also sorry it took me so long to get back after your quick reply.

Still have a problem.

If I put a verison of your code in the subform, it does what I want. that is, keeps user on same field until he enters a proper value.

If Isnull(me.sort) then
Msgbox "Enter a valid sort number."
cancel=true
end if

However, when I put same code in a module:

In subform: Call NullS(me.sort)

In Module:
Public Sub NullS(Srt)
If IsNull(Srt) Then
MsgBox "Enter valid sort number"
Cancel = True
end If


I get an error message saying "Variable Not Defined"

Obviously, I am weak on using modules. Any help much appreciated.

Are you using code in the control BeforeUpdate event? Here is an example:

Private Sub tbxLabNum_BeforeUpdate(Cancel As Integer)
If IsNull(DLookup("LabNum", "Submit", "LabNum='" & Me.tbxLABNUM & "'")) Then
MsgBox Me.tbxLABNUM & " is not valid Lab Number", , "EntryError"
Cancel = True
Me.tbxLABNUM.SelStart = 6
End If
End Sub

[June7]

"Cancel" is defined by argument in the BeforeUpdate event. It is not defined in the general module procedure. I don't really know why Access recognizes Cancel in the BeforeUpdate event and acts to abort record update when Cancel is set to true. I just know it works.

[gg80]

Thanks much for taking time and for rapid response. Hard to understand why micorsoft doesn't change this. I am just going to keep old module code: goto other field, then goto this field, then add "if err.number= 2109, exit sub. Error only occurs occasionally for some certain condition that I haven't been able to define, so not a big inconvenience. Thanks again. I now know how to handle Cancel=true.