Text Box Data Validation against a field in another table (1 Viewer)

johansena · Jun 15, 2013

Hello,

I am a novice Access 2007 user.

I am working on a form that registers new training attendees. One of the fields in my form asks for dealer number.

I have a separate table where all the dealer information is stored. One of the fields is a Customer Number field

I want to ensure that entries in the Dealer Number textbox on my New Registration form matches a valid Customer Number in the Dealer Database Table

I would like this verification to occur either upon a Click event with a button or on an After Update event.

Any help would be great.

Thanks!

rzw0wr · Jun 15, 2013

I don't know exactly what you had in mind to verify that the compare was true.
However you can use a WHERE clause in query to show that the number is there and matches.

WHERE [Dealer Number text box] = [Customer Number]
BTW if your actual field names have spaces it would be of great benefit to you and anyone troubleshooting your db if you were to remove the spaces and any Special characters.

Dale

johansena · Jun 15, 2013

Thanks, I had thought about this, but I wasn't really sure where to build the query.

Can I use the expression builder through the event tab in the properties window for the Dealer Number textbox, or do I have to build a specific query?

Also if I use this meathod, if the data entered in the Dealer Number textbox doesn't equal any record in the Customer Number field is it possible to display an error message?

MarkK · Jun 15, 2013

In this situation it's common to use a combobox. A combo's row source is drawn from a table, and each item in the combo's list is then a candidate, when selected, to be the "Value" of the control.

rzw0wr · Jun 15, 2013

If you want to type in the number then add you table(s) to a query and in the field that holds the data.
Enter, Forms!YourFormName!YourTextBoxName
In after update event of your text box enter Requery.

Dale

johansena · Jun 15, 2013

I don't know if a combo box is appropriate here because there are over 600 records wich would make for a very long combo box. Also the values are arbitrary, and wouldn't be easy to identify.

johansena · Jun 15, 2013

I actually ended up using a combo box. While it is not exactly what I was looking for I was able to fiddle with it enough to make it meet my needs.

Thanks.

Text Box Data Validation against a field in another table (1 Viewer)

johansena

Registered User.

rzw0wr

I will always be a newbie

johansena

Registered User.

MarkK

bit cruncher

rzw0wr

I will always be a newbie

johansena

Registered User.

johansena

Registered User.

Similar threads

Users who are viewing this thread