Conditional Lookup Table?

[Guest]

Hello:

In another database program I've used for years, you could set up a
conditional lookup table without any programming. For example, if Field A has
2 choices, "Corporate" and Individual", then the lookup table for Field B
would be based upon the choice in Field A without writing any code. In other
words, if the user enters Corporate in Field A, then the corporate lookup
table would be available for Field B. And if the user entered Individual in
Field A, then the Individual lookup table would be available for Field B.

Can I have Access select the correct lookup table without doing any
programming as I can do with my current database program? Or must I use
Visual Basic to do such?

Thanks,
Robert

 

[Albert D. Kallal]

You could base the 2nd combo box on a query that is filtered by the first
combo box.

You can use no code, but your lookup table will have to be:

tableB:

MyType: MyTypevalue
Corporate CEO
Corporate VP
Corporate President
Individual Mr.
Individual Mrs.
Individual Dr.


The beauty of the above system is that for each new "type", you don't have
to create a new table.

In other
words, if the user enters Corporate in Field A, then the corporate lookup
table would be available for Field B. And if the user entered Individual
in
Field A, then the Individual lookup table would be available for Field B.

Modern database systems generally don't work that way, nor do they allow
what you
are asking. In MySql, Oracle, Sql server and yes even the JET engine that
ms-access uses, you can't do "conditional" join to a *different* table.
Relational databases simply don't work this way!!

If the data is relatonal, then you have to join to ONE table (you can't
export data, create repots and use sql joins if you break this rule). I
suppose in your case you could store the actual value from lookup table.
However, then you can't extend the design. If you need to add a new type to
the selection process, then you have to create a new table! Creating new
tables for each new type is not very maintainable. (and, you have to kick
everyone out to add that new table also).

With the above design, you could actually make the system self maintain, and
*allow* end users to create new types, and you would not have to create new
tables for each new type.

So, .yes, you can accomplish this selection process without having to write
one lone of code (actually, you might need one line of code). So, just use a
single table with a similar design to the above...

The idea of using a different table for the selection means you can't use
sql to join your data, and your thus throwing out the whole concept of a
relational database.

In your case, you might be storing the actual "value" from that lookup table
(so, it not really relational anymore). However, what happens when you want
to add something like the language to filter the title, or perhaps location,
or some other information. If you have ONE table, then you not have to
update and modify all of the tables structures for that additional
information....

It is certainly possible to write code that in the combo1 after update event
it changes the table that the combo 2 is based on....

combo1 after update:

Me.Combo2.RowSource = "select * from " & Me.Combo5

so, you can do this, but I strongly recommend against this type of design...

 

[Guest]

Hello Albert:

Thanks for your response. I just go into work and haven't had an opportunity
to carefully read and digest your suggestion. However, as soon as I have some
free time, I'm going to give it a try. If I have any problems, I might have
some more questions.

Thanks,
Robert

 

[Guest]

Hello Albert:

I still haven't had time to try and implement your strategy, but I just read
it, and I love the idea of using a 2nd combo box query that is filtered by
the value in the first combo box. I'm not sure I can figure out how to do
such, but the idea is very intriguing.

Robert

 

[Albert D. Kallal]

Hello Albert:

I still haven't had time to try and implement your strategy, but I just
read
it, and I love the idea of using a 2nd combo box query that is filtered by
the value in the first combo box. I'm not sure I can figure out how to do
such, but the idea is very intriguing.

Robert

How to do this is explained here:

http://www.mvps.org/access/forms/frm0028.htm

Note that the above is not 100% clear in that TWO separate examples are in
that link....


what you need: (from above

<quote>
To filter records in a combo/listbox based on the value selected in another
combo/listbox, you can use a stored query which uses the first control's
value as a parameter. For example,
Select PeopleID, PeopleName from tblPeople Where PeopleID =
Forms!FormName!NameOfFirstControl;

Then all you need to do is issue a Requery on the second combo/listbox in
this first control's AfterUpdate event.

Private Sub NameOfFirstControl_AfterUpdate()
Me!NameOfSecondControl.Requery
End Sub

</quote>

If you look at the above, then you need only write ONE line of code...to
"requery" the 2nd combo....

 

[Guest]

Hello Albert:

Thanks so much, you are truly special. Unfortunately I don't have time to
try it out today. Hopefully I can work on it tomorrow.

I don't mind writing one or two lines of code, I'm just not in the mood to
sit down for 2-4 hours of code writing to get a lookup working the way I want.

Robert T

 

[mromyn]

Hi Albert -

Do you have a working example of this? The article didn't really help. I
have been struggling with this and have tried various methods. With one
example I had the combo boxes working, but the record values wouldn't save.
With the update of a new records all of the previous values entered would
change.

Specifically what I need to do is have ComboBox1, when entered, determine
the possible values for ComboBox2. When the record is entered it needs to be
saved to a table. This table will then be used for reports.

I wouldn't think it's that difficult, but I cannot get this to work. If you
have a working example that would be so very helpful.

Thanks!
Mel