Sequential Combo Boxes

[Guest]

Need tp create a work log form where the choice selected in the first combo
box will determine the choices available in the second combo box. For
example, if the user selects "Put on Shoes" in the first combo box, the
choices in the second box are put on socks, tie shoelaces. Or if the first
box is Brush Teeth, the second box choices are Get toothpaste, Wet toothbursh.

Not sure about how to set tables up for that, as well as how to link the
combo boxes.

 

[Steve Schapel]

Paulm,

Try it like this...

- Make a table with 2 fields, let's say they are Task and Subtask, and
enter your data like this:
Task Subtask
Put on Shoes Put on socks
Put on Shoes Tie laces
Brush Teeth Get toothpaste
Brush Teeth Wet toothbrush

- Set the Row Source of the first combobox to a query, the SQL of which
will be like this...
SELECT DISTINCT Task FROM TasksTable

- Set the Row Source of the second combobox to a query, the SQL of which
will be like this...
SELECT Subtask FROM TasksTable WHERE Task =
[Forms]![YourForm]![FirstCombobox]

- On the After Update event of the first combobox, put code like this...
Me.SecondCombobox.Requery

 

[Guest]

Hi, Paul.

If the source records for the 2nd combo box are the many side of a
one-to-many relationship with records from the first, simply include a
foreign key in the second table:

Table1
---------------
ID AutoNumber or Integer (Primary Key)
Description Text

Table2
---------------
ID AutoNumber or Integer (Primary Key)
Table1ID Integer (Foreign Key to Table1)
Description Text

So, you might have the following records:

Table 1
-------------------------
ID Description
======== =====================
37 Put on shoes
49 Brush teeth

Table 2 Records
-------------------
ID Table1ID Description
======== ======== ===============
12 37 Put on socks
46 37 Tie shoelaces
69 49 Get toothpaste
78 49 Wet toothbrush

The form for entering these records (that is, initially loading these
tables) would be a main form based on table 1 and a continuous subform based
on table 2, linked by Table1.ID and Table2.Table1ID. If you use an
Autonumber key, the subform needs only a control bound to Description--Access
will autoassign the PK, and because you've established the link to the main
form, it will also fill in the foreign key.

On the form where you wish to set the choices for a 2nd combo box based on
the selection in the first, you set the combo box' Row Source property and
requery in the first combo box' AfterUpdate event:

Me![MySecondComboBox].RowSource = "SELECT Table2.ID, Table2.Description FROM
Table2 WHERE Table1ID = " & Me![MyFirstComboBox]
Me![MySecondComboBox].Requery

If you will also permit your users to scroll through existing records, you
will need the same code in the form's On Current event procedure.

Hope that helps.
Sprinks