limit two combo boxes with unrelated data??

[tlyczko]

I read about limiting combo boxes' list data, for example, limiting the
list of values in the second combo based on values in the first combo,
and I found the Dev Ashish site link.

I have two combos based on lookups of very small tables, Audit
Descriptions and Programs.

Combo 1: Audit Descriptions has Comprehensive, Fire Safety, Medical,
MSC, Snapshot.
Combo 2: Programs has A, B, C, D, E, F, G, H (for example).

Not every single program can get all the different kinds of audit
descriptions.
For example Program A can have all five kinds of audits, Program B can
only have Comprehensive or Fire Safety, Program C can only have
Comprehensive or Medical, Program D can only have all but Snapshot,
etc. etc.

It can also be expressed the other way, for example, Comprehensive can
be for Program A, B, C, but not D, etc. etc.

Is there any way with this information I can limit the list of Combo 2
based on Combo 1??

Is it better to ask the end user for the program first, then limit the
audit descriptions??

Should I write code in Combo 2 AfterUpdate event to examine the program
to audit description correlation??

Should I add fields to the lookup tables to facilitate the combo box
filtering??

tblAuditDescriptions has:
Comprehensive
Fire Safety
Medical
MSC
Snapshot

tblPrograms has:
A Apple
B Boy
C Cat
D Dog
E Egg
F Fur
G Games
H Hats

Thank you, Tom

 

[Douglas J Steele]

You'd need to introduce a 3rd table that indicates which programs are valid
for each Audit Description.

 

[tlyczko]

Hello Doug...
Ugh, I was afraid of this.
Actually maybe I can add another column into the Audit Description
table, called Program, which would contain one program item per
description, and just have a two-column primary key in that table, use
a distinct value type of query for the first lookup, then use a query
on Audit Description to feed in the second set of values for the second
combo box.
Or alternatively to the Programs table, I'll see what happens, I first
have to have people tell me which Audit Descriptions go with which
Programs.
Thank you, Tom

 

[Bob Quintal]

Hello Doug...
Ugh, I was afraid of this.
Actually maybe I can add another column into the Audit
Description table, called Program, which would contain one
program item per description, and just have a two-column
primary key in that table, use a distinct value type of query
for the first lookup, then use a query on Audit Description to
feed in the second set of values for the second combo box.
Or alternatively to the Programs table, I'll see what happens,
I first have to have people tell me which Audit Descriptions
go with which Programs.
Thank you, Tom

NO! A Big Emphatic NO! What you describe is creating the third
table, then adding the fields from the Audit Descriptions table,
then deletingg your audit descriptions table

It will be a pain to add or remove AUDIT-Program combinations.

Just keep your two tables, and add the third with only two
columns, Audit ID and Program ID.

You originally asked some questions

Is there any way with this information I can limit the list of
Combo 2 based on Combo 1??

Yes, use a third table, programsAudits which contains only the
primary keys of your other two tables.

Is it better to ask the end user for the program first, then
limit the audit descriptions??

If you use the third table, this question becomes irrelevant as
it is equally easy to do either one first.

Should I write code in Combo 2 AfterUpdate event to examine ?
the program to audit description correlation??

No, you just need to replace the rowsource in one combobox with
a query that uses your table and the third table.

Should I add fields to the lookup tables to facilitate the
combo box filtering??

The additional fields required are in the third table. Use a
query to make the two tables appear as a virtual table.

 

[tlyczko]

Hello Bob,
Thank you for replying.
Once I know which programs go with which Audit Descriptions (other
people have to tell me this), I can use your idea and query the third
table to generate the second combo box based on the value of the first
combo box.
Thank you, Tom