Extending Q209595 to multiple possible choices in second table

  • Thread starter Thread starter Richard G. Harper
  • Start date Start date
R

Richard G. Harper

I found Q209595 (How to Synchronize Two Combo Boxes on a Form) and
understand the basic concept but I need to extend it just a little more and
am having problems wrapping my brain around the solution.

Say we have two tables - Table1 is auto-numbered and contains a list of
items a user can select from using ComboBox1. Table2 contains many items
that may apply to more than one item from Table1, something like this:

WARNING1 - applies to item 1 in Table1
WARNING2 - applies to items 1 and 2 in Table1
WARNING3 - applies to item 3 in Table1
WARNING4 - applies to items 1, 2 and 3 in Table1
WARNING5 - applies to items 1 and 3 in Table1

and so on.

What I want to do is set up Table2/ComboBox2 so that when the user makes a
selection in ComboBox1, only those items from Table2 that apply to their
ComboBox1 selection would then appear in ComboBox2.

If I were doing this in VisualBASIC I'd set up Table2 like this:

WARNING1 1
WARNING2 12
WARNING3 3
WARNING4 123
WARNING5 13

and use InStr to search Table2 for matches, populating ComboBox2 with those
that do match ... but this seems rather kludgy to me and it feels like there
should be a more elegant method of doing this. I've googled the subject but
I'm either choosing bad keywords or no one else has tried to do this
before. :-)

Thanks in advance for your consideration.
 
Hi Richard

What you have here is a many-to-many relationship between two sets -
warnings and records in Table 1. I suggest, if you have done so already,
that you set up a table of Warnings, with a primary key WarningNumber and
other fields as required (WarningName, message, help context, etc).

Now, implement the many-to-many relationship by creating a third table with
two fields as follows:
WarningNum ItemID
1 1
2 1
2 2
3 3
....etc

Now, create a query, qryItemWarnings, with this table joined to the Warnings
table. Include the fields you want in your second combobox, and also the
ItemID field.

In the AfterUpdate event of ComboBox1, set the RowSource of ComboBox2 as
follows:

ComboBox2.RowSource = "Select * from qryItemWarnings where ItemID=" _
& ComboBox1
 
Ah ... I knew there had to be a better way. Thanks for helping me see
around the corner and find it!


Graham Mandeno said:
Hi Richard

What you have here is a many-to-many relationship between two sets -
warnings and records in Table 1. I suggest, if you have done so already,
that you set up a table of Warnings, with a primary key WarningNumber and
other fields as required (WarningName, message, help context, etc).

Now, implement the many-to-many relationship by creating a third table with
two fields as follows:
WarningNum ItemID
1 1
2 1
2 2
3 3
...etc

Now, create a query, qryItemWarnings, with this table joined to the Warnings
table. Include the fields you want in your second combobox, and also the
ItemID field.

In the AfterUpdate event of ComboBox1, set the RowSource of ComboBox2 as
follows:

ComboBox2.RowSource = "Select * from qryItemWarnings where ItemID=" _
& ComboBox1

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


Richard G. Harper said:
I found Q209595 (How to Synchronize Two Combo Boxes on a Form) and
understand the basic concept but I need to extend it just a little more and
am having problems wrapping my brain around the solution.

Say we have two tables - Table1 is auto-numbered and contains a list of
items a user can select from using ComboBox1. Table2 contains many items
that may apply to more than one item from Table1, something like this:

WARNING1 - applies to item 1 in Table1
WARNING2 - applies to items 1 and 2 in Table1
WARNING3 - applies to item 3 in Table1
WARNING4 - applies to items 1, 2 and 3 in Table1
WARNING5 - applies to items 1 and 3 in Table1

and so on.

What I want to do is set up Table2/ComboBox2 so that when the user makes a
selection in ComboBox1, only those items from Table2 that apply to their
ComboBox1 selection would then appear in ComboBox2.

If I were doing this in VisualBASIC I'd set up Table2 like this:

WARNING1 1
WARNING2 12
WARNING3 3
WARNING4 123
WARNING5 13

and use InStr to search Table2 for matches, populating ComboBox2 with those
that do match ... but this seems rather kludgy to me and it feels like there
should be a more elegant method of doing this. I've googled the subject but
I'm either choosing bad keywords or no one else has tried to do this
before. :-)

Thanks in advance for your consideration.
Click to expand...
Click to expand...
 
Just a follow-up to let you know I've successfully incorporated your
suggestions into the database and it's working perfectly. Thank you once
again for your assistance.

--

Graham Mandeno said:
Hi Richard

What you have here is a many-to-many relationship between two sets -
warnings and records in Table 1. I suggest, if you have done so already,
that you set up a table of Warnings, with a primary key WarningNumber and
other fields as required (WarningName, message, help context, etc).
Click to expand...

<snipped>
 
Back
Top