Microsoft Access MsgBox: Yes or No?
How to get a Yes or No answer from your user.
Q: I would like a button the user has to click on that
checks the values in a form and determines whether or not it's OK to
close the form. The user must click on my button and can't just shut the
form down.
A: First, make yourself a
simple form. I have a form here with two fields on it - a name and age.
Let's assume for the purposes of this example, the person's age has to
be between 21 and 50. If not, the user gets an error.
So I'm going to right-click on the Close button in Design View to create
a BUILD EVENT with some VBA code in it. Here's what I'm going to say:
Private Sub
Command0_Click()
Dim myReply
If myAge > 50 Or myAge < 21 Then
MsgBox ("Age must be between 21 and
50")
Exit Sub
End If
myReply = MsgBox("Are you sure?", vbYesNo)
If myReply = vbYes Then
DoCmd.Close
End If
End Sub
The first IF block checks to see if the age that was entered in is
valid. If you enter something like 62, you get the error message and
you're forced back to the form to correct it.
Now, if you get to the second IF block, we can assume your age is
correct, and you're asked if you now want to exit the form. If you click
on YES, the form exits, saving your record.
Now, of course, there's nothing to stop your user from CLOSING the form
with the [X] close button. So we need to turn off that button in the
Form Properties. I'll also turn off the Control Box and Max/Min buttons
while I'm at it.
You can also prevent the user from moving from record to record
(scrolling ahead or back) by turning off the Navigation Buttons too.
I'll also turn off the Record Selectors to prevent them from deleting a
record as well.
Now we have a form that is bullet-proof. The user HAS to follow our
rules. He can't close the form without using our button, and he can't
move from record to record or delete anything without using a button
that we would have to create.
I cover all of these concepts in my various Access tutorials. Form
properties such as the Record Selectors, Navigation Buttons, etc. are
covered in
Access 103. The VBA programming starts in my 300-level classes
starting with
Access 301.
By Richard Rost
Click here to sign up for more FREE tips
| 
