Validation Rules for Textbox input

[Eric]

I have a textbox on a form requiring an entry for a
customer id that must be verified.

As it is, it will allow you to enter a number that is not
in the database. I want it to give a message when you try
to enter a customer id number that is not in the customer
table. The entry must already be in the customers table.

All help would be greatly appreciated!

 

[Dirk Goldgar]

I have a textbox on a form requiring an entry for a
customer id that must be verified.

As it is, it will allow you to enter a number that is not
in the database. I want it to give a message when you try
to enter a customer id number that is not in the customer
table. The entry must already be in the customers table.

All help would be greatly appreciated!

Probably the simplest solution is to use a combo box instead of a text
box, with its rowsource set to a query of the customers table and its
LimitToList property set to Yes. If you have an enormous number of
customers however (more than 65,536), or if for some reason it must be a
text box, then you could use code in the text box's BeforeUpdate event
like this:

'----- start of example code -----
Private Sub CustomerID_BeforeUpdate(Cancel As Integer)

If Not IsNull(Me!CustomerID) Then

If DCount("*", "Customers", _
"CustomerID=" & Me!CustomerID) _
= 0 _
Then
MsgBox("This customer ID is not on file!")
Cancel = True
End If

End If

End Sub
'----- end of example code -----

Note that the above example assumes that CustomerID is a numeric field.
For text, quotes must be included to surround the value of
Me!CustomerID:

"CustomerID='" & Me!CustomerID & "'") _