Verifying data is in a field before exit or close

[lcox400w]

I have a field (DRNo) which is a key field and MUST be typed in before the
user can do anything else on the form. Its the first field on the form. The
field must be 9 digits and can not be left blank. The only exception is if
the user closes the form (there is a close button). If the field is blank
and the user closes the form, thats ok,but other than that, they must put in
9 digits. I have 2 events I am tyring to do this with, along with some folks
from this forum who have helped me along the way, but still the code doesnt
work and is conflicting between the 2 events.

The before update works fine if the user closes the form, but it also allows
the user to tab out of the field without putting anything in it. The on exit
works if the user tries to exit the field and the length is less than 9
digits, however if the user types in a number, then erases it causing a ""
(blank) field, and the user then tries to close the form, the on exit code
kicks in and says your DRNo is less than 9 digits, and wont let the user
close the form. I know I'm close to getting this to work ,but cant figure it
out.

hope someone and point me in the right direction.

thanks

Private Sub DRNO_BeforeUpdate(Cancel As Integer)
If IsNull(DRNo) Or DRNo = "" Then
If MsgBox("You did not put a DR number in. A DR is required. Do you want
to close the form and lose any data you have entered?", vbYesNo) = vbYes Then
Cancel = True
Me.Undo
DoCmd.Close acForm, Me.Name, acSaveNo
Exit Sub
Else
Cancel = True
DRNo.SetFocus
Exit Sub
End If
End If

End Sub

****
Private Sub DRNO_Exit(Cancel As Integer)
If Len(DRNo) < 9 Or IsNull(DRNo) Then
MsgBox "DR number must be 9 digits. If this is a CR number, you must
add extra zero's to make the CR number 9 digits", vbOKOnly, "DR number too
short"
DRNo.SetFocus

Else
Cancel = False
End If

End Sub

 

[John W. Vinson]

I have a field (DRNo) which is a key field and MUST be typed in before the
user can do anything else on the form. Its the first field on the form. The
field must be 9 digits and can not be left blank. The only exception is if
the user closes the form (there is a close button). If the field is blank
and the user closes the form, thats ok,but other than that, they must put in
9 digits. I have 2 events I am tyring to do this with, along with some folks
from this forum who have helped me along the way, but still the code doesnt
work and is conflicting between the 2 events.

The before update works fine if the user closes the form, but it also allows
the user to tab out of the field without putting anything in it. The on exit
works if the user tries to exit the field and the length is less than 9
digits, however if the user types in a number, then erases it causing a ""
(blank) field, and the user then tries to close the form, the on exit code
kicks in and says your DRNo is less than 9 digits, and wont let the user
close the form. I know I'm close to getting this to work ,but cant figure it
out.

hope someone and point me in the right direction.

One way I'd suggest doing this is to have this be the ONLY control on the form
that is enabled (or even visible, if you prefer). In the textbox's
beforeupdate event check for valid entry (or just use an input mask of
"000000000" to force entry of nine digits). Then in its Afterupdate enable, or
make visible, the rest of the controls on the form.

 

[Dennis]

If len(Nz(DRNo)) < 9 then
Error
end if


You can place this code snip anywhere you like, where it will provide the
best "fit".