Validation Rule

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

perplexed - 12 Mar 2007 20:31 GMT

Any Ideas? I have two tables, one table a journal containing a customer
field, that field reference another table called "Customers" where I maintain
a list of customers. The user get's a dropdown list to fill in the field,
but some users type the customer name in which I want to prevent. I want to
set a validation rule on my journal table in the customer fields to only
accept customer names that are in my customer table. And if the user types in
a customer name that doesn't exist in my list of customer then the following
alert appears. ALERT: Either Pick Customer From List or if they are not in
list, then please Add Customer To List By Clicking Button Above. I have a
button on my form that allows the users to add new customers to my customer
table. The customer table secures that the customer name is entered 1 way not
numberous ways like: Federal Express, Fed-X, FE... and so forth.

Reply to this Message

missinglinq - 12 Mar 2007 20:50 GMT

Goto the Properties Box for your combobox, click on the Events tab, then goto
the On Not In List property. Double-click to the right of the box and click
on Code Builder. This will take you to the combobox's NotInList sub. Place
the code here for a message box to pop up giving the Alert information you
mentioned. Anytime a user enters a name not in the source for you combobox
(not in the Customers table) this code will display the messagebox.

Signature

There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000

Reply to this Message

perplexed - 12 Mar 2007 22:26 GMT

YOU'RE FABULOUS!!! THANK YOU. I will go do this right now. It wasn't as
intuitive as I had thought it would be.

> Goto the Properties Box for your combobox, click on the Events tab, then goto
> the On Not In List property. Double-click to the right of the box and click
> on Code Builder. This will take you to the combobox's NotInList sub. Place
> the code here for a message box to pop up giving the Alert information you
> mentioned. Anytime a user enters a name not in the source for you combobox
> (not in the Customers table) this code will display the messagebox.

Reply to this Message

perplexed - 12 Mar 2007 22:42 GMT

Okay, I'm trying what you suggested, although, I'm pretty green with VBA
code: Here's my code but it's not working, can you tell what I have done
wrong?

Private Sub Customer_NotInList(NewData As String, Response As Integer)
MsgBox Err.Description([ALERT:Either Pick Customer From List or if they are
not in list, then please Add Customer To List By Clicking Button Above.])
End Sub

Reply to this Message