Solved

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

Posted on 2011-02-21
15
503 Views
Last Modified: 2012-08-13
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.
0
Comment
Question by:mc3mcintyre
  • 5
  • 4
  • 4
  • +1
15 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 67 total points
ID: 34946181
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
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 67 total points
ID: 34946188
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
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 34946198
slow fingers :)
0
 
LVL 84
ID: 34946224
And GMTA :)
0
 
LVL 6

Expert Comment

by:AkAlan
ID: 34946249
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
 

Author Comment

by:mc3mcintyre
ID: 34946252
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
 

Author Comment

by:mc3mcintyre
ID: 34946263
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 6

Expert Comment

by:AkAlan
ID: 34946451
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
 

Author Comment

by:mc3mcintyre
ID: 34946495
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
 
LVL 6

Assisted Solution

by:AkAlan
AkAlan earned 66 total points
ID: 34946570
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
 
LVL 84
ID: 34947659
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
 
LVL 6

Expert Comment

by:AkAlan
ID: 34947705
LSM is correct, I go between asp.net and Access so much that I mix up my terminology. It's ComboBox in Access.
0
 

Author Closing Comment

by:mc3mcintyre
ID: 34947707
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
 
LVL 84
ID: 34950935
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
 

Author Comment

by:mc3mcintyre
ID: 34950958
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

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now