Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2011-02-21
15
Medium Priority
?
556 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 4
  • +1
15 Comments
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 268 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 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 268 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 93

Expert Comment

by:Patrick Matthews
ID: 34946198
slow fingers :)
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 85
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
 
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 264 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 85
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 85
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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

650 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