How to use msgbox

[JIM]

I've been messing around with this for hours and can't make it work. Here's
my code:
Private Sub txtWORep_AfterUpdate()
If txtWORep <> "B" And txtWORep <> "M" And txtWORep <> "R" Then
Beep
If vbOKOnly = MsgBox("Please enter B, M, or R", vbOKOnly) Then
Me.txtWORep = " "
Me.txtWORep.SetFocus
Else
Me.txtTenant.SetFocus
End If
End If
End Sub

When OK is clicked I'd like to blank out control and then reset focus to
control but instead it just goes to next input control and doesn't blank
field. What am I missing?
TIA

 

[Albert D. Kallal]

The after update event is for when the control is all good...and you want to
do some other "post" procesing...

but instead it just goes to next input control

Yes, what happens is AFTER your code runs, then ms-access says,
ok..everything is good and fine, lets move to the next control!
So the ms-access stuff that decides to move to the next control runs AFTER
all is said and done.

However, the correct event to use for hits is the before update event..and
it has a cancel variable.

Hence, try:

If txtWORep <> "B" And txtWORep <> "M" And txtWORep <> "R" Then
Beep
MsgBox "Please enter B, M, or R", vbOKOnly
Cancel = True
end if

So, the setting of cancel = true will prevent the user from leaving the
control. Place the above code in the BEFORE update event, as that event
has the cancel feature you need.

 

[Douglas J. Steele]

Since you're specifying vbOKOnly as a parameter, your message box is only
going to have the one OK button on it. That means there's no other way to
close the message box other than clicking on the OK button, so there's
really no point in checking whether the user clicked on the OK button!

If txtWORep is a bound control, my recommendation would be to use its
BeforeUpdate event to validate the value

Private Sub txtWORep_BeforeUpdate(Cancel As Integer)
If txtWORep <> "B" And txtWORep <> "M" And txtWORep <> "R" Then
Beep
MsgBox "Please enter B, M, or R", vbOKOnly
Me.txtWORep.Undo
Cancel = True
End If
End Sub

although if you're limiting their choices to only three values, I'd
personally use a combo box.

 

[JIM]

Thanks to you both for the help. I think I will use a combo box. That's a
great idea-working till 5am leaves one a little blurry eyed and unrational.
JIM