How do I validate one field in Access to another field in another table?

I am using Access 2010 and am creating an database that has two tables.  As an example of what I'm trying to do, the following is a similar scenario:

Table1 is an inventory of parts.  One of the fields is labeled Part No.
Table2 is an list of orders.  One of the fields is also labeled Part No.

When the sales rep fills out Table2 to create a new order, he is required to fill in the Part No. field.  As soon as he tabs to the next field, Access is supposed to validate what he put in the Part No. field.  It compares that entry with the list of Part No. in Table 1.  If it is valid, it lets the sales rep move on to the next field.  If not, it displays a custom error message.

What I have entered in the Validation Rule for the Part No. field of Table 2 is the following:
[Part No]=("[Part No]","[Table1]")

However, it is not working.  Does anyone know how to make this happen correctly?  Thank you.
mc3mcintyreAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
In many cases, it's best to use a Combo that shows ONLY those items in your Parts table, so the user is required to select only valid parts. This takes care of your validation, assuming you set the combo's LimitToList property to Yes.

0
 
Patrick MatthewsConnect With a Mentor Commented:
Instead of having your sales rep key in an item number, why not simply have your form use a dropdown (combobox) populated with the list of items?  That eliminates your need to validate that the item number exists, because if it's in the list, then it exists.

:)
0
 
Patrick MatthewsCommented:
slow fingers :)
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
And GMTA :)
0
 
AkAlanCommented:
There are a couple of ways you can do this but I would suggest using a dropdown list which is populated by the partnumbers from table 1 and set it's Limit To List option to true.

Try using the DLookup funtion


dim partNo as string
partNo = textBox1
DLookup("Part NO", "Table1", "Part No = " & partNO & ")"
0
 
mc3mcintyreAuthor Commented:
I've tried to create a lookup wizard and that works fine.  However, Table1 has other Lookup Wizards too.  Each part being ordered in the Table2 has four lookup wizards (relationships) and I want to allow the sales rep to list atleast 50 items.The combination of all of these Lookup wizards, maxes out the limit of 32 relationships per table that Microsoft has set on Access.   Thus, I don't know how to create more than 32 relationships.  Instead of creating lookup wizards, I am trying to create a text box for these fields that have validation rules.  Unless someone knows a better way around this.
0
 
mc3mcintyreAuthor Commented:
AkAlan,

How would I go about adding this code to Access?  Where would I put it?  I tried to use a DLookup earlier today, but I got an invalid function error when I tried to save the table.
0
 
AkAlanCommented:
If you want to use it as a validator I would put it in the On Exit event of the text box

Dim partNo As String
partNo = textBox1

If ISNULL(DLookup("Part NO", "Table1", "Part No = " & partNO & "") ) Then
     MsgBox"You Must Enter a Valid Part Number"
End IF

A couple of things. You really should use a drop down unless there are good reasons not to.
Also, never name your fileds (Like "Part No") with spaces, it will bite you every time. Use Part_No or PartNo
Good Luck
0
 
mc3mcintyreAuthor Commented:
I would love to use drop-down lists, but aren't those lookup wizards?  Also, I don't want to retype the same drop-down list for ever item field (Item1, Item2, Item3, etc) that is in this order table.  There's going to be around 300 records in Table1 (Parts Table).

Thanks for the tip about field names, I will start removing the spaces now.
0
 
AkAlanConnect With a Mentor Commented:
I think you really need to take a look at dropdowns first, they are very valuable and I think you are misunderstanding their use. If you need to restrict a user to entering only parts that are in the Parts table, you give them a dropdown which only displays to them them the available parts from the table. They aren't bothered with having to remember or look up the part from another source. You only give one dropdown box, not one for every record.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
We're referring to two entirely different things:

The Experts are referring to Comboboxes, which are controls you place on an Access FORM. These are good, solid interface components, and you should get accustomed to using them rather than trying to kludge a solution.

You're referring to "Lookups", which are defined in the Table Design view. These are bad, and should be avoided at all costs. They tend to hid the true relationship between data, can eat up your indexes (as you've seen) and can become a real headache when you begin to design complicated reports.

You don't have to retype everything needed for a Combo. If you set the Rowsource correctly for your form Combo, Access will load all the items you specify in your SELECT statement. These are "data aware" controls and can be tied directly to a data source, like a table or query - which means they will retrieve their data from those sources.

I'd strongly encourage you to do some more research on the basics of Access before moving forward with this project. Access ships with the Northwinds sample database, which shows you quite a bit about the way things work with Access, and there are a lot of samples available on the Office website.
0
 
AkAlanCommented:
LSM is correct, I go between asp.net and Access so much that I mix up my terminology. It's ComboBox in Access.
0
 
mc3mcintyreAuthor Commented:
Well, I'm not sure who is better deserving of the points.  All of you helped a lot out.  LSMConsulting, I didn't see your last post until I had already solved my dilemma.  You are right, I was confusing what everyone else was referring to as ComboBoxes to what I'm used to as Lookups.  After a bit of looking around in my database, I was able to change my text boxes to ComboBoxes.  Now it is working exactly as I want it.

By the way, I have used Access before and made a few databases before.  However, this is by far the most complicated database that I've done.  The three of you really did trigger the way I was thinking about it and AkAlan was very insightful.  Thank you guys for your help.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Glad you got it fixed. I'd still encourage you to go through your database and remove all those Lookups that you've defined at the table level. They really have no use except when users are directly viewing tables (and they shouldn't be doing that, of course).
0
 
mc3mcintyreAuthor Commented:
Thank you, LSMConsulting, I will do that.  I think I've already removed most of them, but will go through again.  Thank you for your valuable advice.  I appreciate the time.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.