Data Validation

[Bunky]

I have a Combo Box on a form that, depending on the value selected, populates
other txtboxs. All works great as long as the operator selects a value from
the Combo Box. I have tried to put in VB Code that I found at this site but
I can't seem to get it to fire. Here is my code.

Private Sub User_BeforeUpdate(Cancel As Integer)
If IsNull(User) Then
MsgBox "Please enter data"
User.SetFocus
Cancel = True
End If

End Sub

User is the name of the combo box on the form. Any idea how I can get this
condition to stop and give an error?

Kent

 

[Tom van Stiphout]

On Thu, 9 Oct 2008 07:11:01 -0700, Bunky

If I understand you correctly you want this code to fire if the user
tabs over the combobox without selecting a value. Well, that won't
work because BeforeUpdate only fires if a value is selected and the
control updates. The LostFocus event would fire, but I am not a big
fan of that.
Why not set visibility of all other controls to False, or the Enabled
property, until a value is selected? That should drive the issue
home.

-Tom.
Microsoft Access MVP

 

[Bunky]

You are understanding correctly. However, I do not understand your response.
I'm sorry but I am not a VB Coder and do not know where to do this. Help!

Kent

 

[Klatuu]

You are using the wrong event. Combo boxes have a Not In List event that
fires if you select a value not in the combo's row source. You can use that
event to handle the situation to suit your business rules. You may just
want to warn them the did not select a valid value and not let them proceed
or you may want to allow them to either create a new record using the value
or cancel and try again.

 

[Bunky]

Thank you for your response. However, I am not certain what to do. I put
the code I previously posted in an event procedure for the 'Not In List'
event. Nothing happened. You stated this fires if you select a value not in
the row source but what happens is they do NOT select anything? In my
testing, it does not stop with an error. Ideas

 

[Klatuu]

The not in list event will not fire if no value is entered. In that case,
you need to test in the form's Before Update event.

If IsNull(Me.Phone) Then
MsgBox "A Phone Number is Required", vbExclamation
Cancel = True
End If

 

[Bunky]

Thank you so much! Unfortunately, that does not work either. (See earlier
posting!) I use this combo box to determine the values of three other
textboxes. Could that be my problem? How else can I do this with code?

 

[Klatuu]

It does work, you just aren't doing it right

I suggest you run in debug mode and see what the value of the combo box is
in th After Update event of the Combo box.

 

[Bunky]

I'm sure you are right; I just don't have the knowledge I need to write VB.
I do not even know how to run in debug mode. Pretty sick, huh? I'll keep
trying. Thanks for all your assistance!

 

[Klatuu]

To run your code in debug mode, open the module.
put your cursor on the line where you want to stop.
press F9 - It will cause the line to change color.
now use the combo box. When the running code gets to that line, the VB
editor will be open and the line will be yellow, meaning it is the next line
to execute. pressiong F8 will cause the line to execute and move to the
next line.
There are several ways to see values of variables or objects. For some
things you can hover the cursor over them and you will get a control tip box
show the value. You can also set watches from the debug menu which will
allow you several options, or you can use the immediate window to look at
values. For example, if you want to see the value of the combo box you
would type in
?Me.MyComboBoxName

 

[Bunky]

SUCCESS!!!!! My problem was I was putting the code on the data element I
wanted to test (User in this case) and not the Form. Now things are better.
Thanks again for all your help!