By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,489 Members | 1,840 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,489 IT Pros & Developers. It's quick & easy.

limit two combo boxes with unrelated data??

P: n/a
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

Feb 21 '06 #1
Share this Question
Share on Google+
4 Replies


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

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"tlyczko" <tl*****@gmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...
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

Feb 21 '06 #2

P: n/a
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

Feb 21 '06 #3

P: n/a
"tlyczko" <tl*****@gmail.com> wrote in
news:11**********************@g43g2000cwa.googlegr oups.com:
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.

--
Bob Quintal

PA is y I've altered my email address.
Feb 22 '06 #4

P: n/a
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

Feb 24 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.