Solved

Table Definition - Set lookup values based on another field in table

Posted on 2012-04-11
8
452 Views
Last Modified: 2012-04-11
I am working on an Access 2010 database (32 bit, accdb). Within this database I am attempting to create a dynamic data validation process, wherein the user can identify fields in tables they want validated, and define what they want those fields validated against.
I have two tables in this database listing the tables in the database that will be validated plus the table(s) that will be used for validation, and all of the fields in these selected tables (tblDdTable and tblDdField, respectively).
I am now creating a new table named tblDdRules. One of the fields in this new table is "TableName". This field is defined with a combobox lookup which queries the table names in tblDdTable. The next field is "FieldName". I would like to define a combobox lookup which queries tblDdField using the value in the prior field "TableName" as the key.
I am attaching a small database with the three tables mentioned in the first post.
Here is an image of the lookup defined for the TableName field. How would I set up the query for the FieldName lookup?
image 1Lookup-Question.accdb
0
Comment
Question by:shambalad
[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
  • 3
8 Comments
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 37833083
Is your goal here simply to select a table from one combobox, and have a second combobox display the associated fields?
If so then see the attached sample.


As was stated by the first expert.
This is a *Nightmare* in tables.. , this is typically done in a form
See here for more info:
http://access.mvps.org/access/lookupfields.htm

JeffCoachman
Database115.mdb
0
 
LVL 7

Author Comment

by:shambalad
ID: 37833180
Duly noted. I know how to do this on a form.
Won't someone simply tell me how this is done in a table definition?
0
 
LVL 7

Author Comment

by:shambalad
ID: 37833236
I have to apologize. I've managed to get two questions running concurrently on the same topic. I closed out the other, but then the Expert responded with more input. I am going to close out this question and focus the discussion on the earlier question.
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27670012
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Author Closing Comment

by:shambalad
ID: 37833237
0
 
LVL 7

Author Comment

by:shambalad
ID: 37833575
I just posted this in the other question:
FWIW:
Some years ago I was supporting multiple Access databases for a major Health insurer. The individual who originally created the databases had extensively used lookups in the table definitions, so I know just how much of a major PITA they can be.
I still want to know how I can create a lookup which filters based on the value of another field in the table.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37833919
Then this will take a bit of work.
Unfortunately, I just can't spare the time to do this as a volunteer here..

Sorry, Perhaps another Expert will help out
0
 
LVL 7

Author Comment

by:shambalad
ID: 37834003
You're right. It's not worth it. As I develop, I sometimes digress into what amount to more intellectual efforts than anything else. This would fall into that category.
I know these lookups are impractical for client databases, but it could be a useful tool for me personally.
Thanks anyway.
th
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37834128
<I know these lookups are impractical for client databases, but it could be a useful tool for me personally.>
That is what I though as well, ...at one time...

In addition to what the link states...
You cannot paste the visible text or the numerical value directly into the combobox/field.
Alos, this "Datatype" is not currently supported by SQL Server, so it will be worthless, when you need to upsize


It is far beter "for your own understainding" to keep the data as "raw" as possible in the tables...


In this same vein, do as much research as you can before using other "Access only" features:
Multivalues fields
Hyperlink Fields
OLE fields
Attachment fields
...etc

These all have their place *In MS Access* , but when it comes time to upsize, they may have to be tossed...

JeffCoachman
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

691 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