Solved

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

Posted on 2012-04-10
9
622 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 know I can do this on a form. Is it possible to do this within the table definition itself?
0
Comment
Question by:shambalad
  • 6
  • 3
9 Comments
 
LVL 42

Accepted Solution

by:
dqmq earned 500 total points
ID: 37830507
This field is defined with a combobox lookup which queries the table names in tblDdTable.

Yes, Access supports that, but gives you grief in the long run.  Not recommended.  You can create a form for maintaining the table in a couple of clicks.

Also, you do not want both a fk reference to tblDdTable and tblDdField.   Only to tblDdField.



Is it possible to do this within the table definition itself?

I don't think so and that would not be a good idea, either.

You really don't want to
0
 
LVL 7

Author Comment

by:shambalad
ID: 37830640
OK - I hear you on the 'bad idea' bit, but humor me. You say it's possible.  
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
 
LVL 7

Author Closing Comment

by:shambalad
ID: 37832803
OK - I didn't ask the right question. I don't really want to know whether this is a good or bad idea. I want to know how it's done. I'll open up a new question and rephrase.
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 42

Expert Comment

by:dqmq
ID: 37833139
Must be some EE glitch, but I've responded twice with a detailed explanation that has found it's way to the bit bucket. Don't have time to write it up again.  FWIW, thanks for including the db, that helps a bunch for understanding your question.

Here's the shortened version.

I meant to say that technique is supported for the SourceTable column, but not for the SourceField column constrained by the SourceTable column as you want.

I've attached my design.   As per your request, I did not remove the column look ups, but improved them as best I could.

Finally, I also cleaned up the foreign key relationship issues that I suspected.  As a result, you may find it acceptable to maintain rules by drilling down from the tblDdTables or tblDdFields rather than lookups from tblRules.
Lookup-Question.accdb
0
 
LVL 7

Author Comment

by:shambalad
ID: 37833213
Thank you for getting back to me on this. I have attached an image of the dropdown for the FieldName combobox. Note how it is not being filtered for the value of the TableName field. This is what I was getting at. I would want to see only the fields for the tblElement table.
image 1
0
 
LVL 7

Author Comment

by:shambalad
ID: 37833261
After closing this question. I opened up a new one:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27670885
I do try to avoid opening multiple questions on the same topic. Anyway, I closed out the other one.
I appreciate your help on this. I hear you all loud and clear on the merits of this approach, but at this point I simply want to know how this is done.
0
 
LVL 7

Author Comment

by:shambalad
ID: 37833566
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 42

Expert Comment

by:dqmq
ID: 37834323
Within a table definition, there is no way to design a lookup rowsource that is "filtered" based on the value entered (but not yet updated) to another column of the same row.
0
 
LVL 7

Author Comment

by:shambalad
ID: 37834605
That's what I thought. In truth, that is what I was really trying to get at in my original question. Thank you for taking the time to follow up on this. I appreciate your help.
Regards,
Todd
0

Featured Post

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)

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
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.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

770 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