Multiple Combo box in a form

[Guest]

i'm have a form. i want to do three combo box. one is customer, one is partID
and one is process. this three combo box are from three different table.
there are customer table, part master table and process table. i want to do
like this. if i choose one of the customer from the combo box, then when i go
to the partID combo box, then i want it just show out whatever the part which
belong to that customer only. others customer's part wont be in the list.
then when i click on the process combo box, then i just want the combo box
show out the process which is belong to that partID which i choose only.
example: i have 10 customer, when i choose A from the combo box, then it just
show out the partID which are belong to A, then the process combo box just
show the process for the partID. can anybody help me?

thanks!

 

[Ron2006]

i'm have a form. i want to do three combo box. one is customer, one is partID
and one is process. this three combo box are from three different table.
there are customer table, part master table and process table. i want to do
like this. if i choose one of the customer from the combo box, then when i go
to the partID combo box, then i want it just show out whatever the part which
belong to that customer only. others customer's part wont be in the list.
then when i click on the process combo box, then i just want the combo box
show out the process which is belong to that partID which i choose only.
example: i have 10 customer, when i choose A from the combo box, then it just
show out the partID which are belong to A, then the process combo box just
show the process for the partID. can anybody help me?

thanks!

do a search on 'Cascading combo boxes"

Here is one result I got for just such a search:

==================================
You need to put criteria in the SQL for the Row Source of each combo
box
that refers to the selection in each of the other two combo boxes. In
the
AfterUpdate event of each combo box, you need to issue a Requery to
each of
the other two combo boxes. The criteria should accept the fact that
either
or both of the other two combo boxes may be Null, in which case it
should
not filter the entries for that field.

Example criteria:


=Forms!frmMyForm!cboCombo2 Or IsNull(Forms!frmMyForm!cboCombo2)


The reason this works is because if you make a selection, the right
side of
the Or will be False, but the left side will then limit the results by
what
was entered in Combo2 (only one side of an Or statement needs to
return True
for the statement to be True). The left side of the statement will be
True
for all results that match the selection. If Combo2 is Null, then the
right
side of the Or is True and will always be True, regardless of the
value in
the field, so all values are returned.


--
Wayne Morgan
MS Access MVP


=====================================

 

[Wolfgang Kais]

Hello Irene.

i'm have a form. i want to do three combo box. one is customer,
one is partID and one is process. this three combo box are from
three different table. there are customer table, part master table
and process table. i want to do like this. if i choose one of the
customer from the combo box, then when i go to the partID
combo box, then i want it just show out whatever the part which
belong to that customer only. others customer's part wont be in
the list. then when i click on the process combo box, then i just
want the combo box show out the process which is belong to that
partID which i choose only. example: i have 10 customer, when i
choose A from the combo box, then it just show out the partID
which are belong to A, then the process combo box just show the
process for the partID. can anybody help me?

You can configure the rowsource properties of the comboboxes as
follows (you must adjust the names appripriately):

CustomerComboBox:
Select CustomerID, CustomerName From Customer Order By CustomerName

PartComboBox:
Select PartID, PartName From Part Where CustomerID =
[Forms]![TheForm]![CustomerComboBox]

ProcessComboBox:
Select ProcessID, ProcessName From Process Where PartID =
[Forms]![TheForm]![PartComboBox]

Then you should ensure that after a costomer is selected, (in the
AfterUpdate event of the CustomerComboBox), that the other two
comboboxes are requeried and set to Null.
AfterUpdate for the PartComboBox, this has to be done only for the
ProcessComboBox.

Setting the ComboBoxes to Null has the following effect:
You choose an other customer, so the Part and Process boxes are set
to Null, no value is selected in those boxes.
The list os the pasrts displays the parts assiciated with the selected
customer and the list of processes is empty, since you did not yet
select a part. After also selecting a part, the list of processes
displays all processes that are associated with the selected part.

 

[Guest]

hi Wolfgang Kais,

thanks for helping me. but i still have some question. i have try to use the
way which you teach me. you say that in the AfterUpdate event of the
CustomerComboBox, that the other two comboboxes are requeried and set to
Null. how to do? can you show me?

thanks!

Hello Irene.

i'm have a form. i want to do three combo box. one is customer,
one is partID and one is process. this three combo box are from
three different table. there are customer table, part master table
and process table. i want to do like this. if i choose one of the
customer from the combo box, then when i go to the partID
combo box, then i want it just show out whatever the part which
belong to that customer only. others customer's part wont be in
the list. then when i click on the process combo box, then i just
want the combo box show out the process which is belong to that
partID which i choose only. example: i have 10 customer, when i
choose A from the combo box, then it just show out the partID
which are belong to A, then the process combo box just show the
process for the partID. can anybody help me?

You can configure the rowsource properties of the comboboxes as
follows (you must adjust the names appripriately):

CustomerComboBox:
Select CustomerID, CustomerName From Customer Order By CustomerName

PartComboBox:
Select PartID, PartName From Part Where CustomerID =
[Forms]![TheForm]![CustomerComboBox]

ProcessComboBox:
Select ProcessID, ProcessName From Process Where PartID =
[Forms]![TheForm]![PartComboBox]

Then you should ensure that after a costomer is selected, (in the
AfterUpdate event of the CustomerComboBox), that the other two
comboboxes are requeried and set to Null.
AfterUpdate for the PartComboBox, this has to be done only for the
ProcessComboBox.

Setting the ComboBoxes to Null has the following effect:
You choose an other customer, so the Part and Process boxes are set
to Null, no value is selected in those boxes.
The list os the pasrts displays the parts assiciated with the selected
customer and the list of processes is empty, since you did not yet
select a part. After also selecting a part, the list of processes
displays all processes that are associated with the selected part.

 

[Wolfgang Kais]

Hello Irene.

i'm have a form. i want to do three combo box. one is customer,
one is partID and one is process. this three combo box are from
three different table. there are customer table, part master table
and process table. i want to do like this. if i choose one of the
customer from the combo box, then when i go to the partID
combo box, then i want it just show out whatever the part which
belong to that customer only. others customer's part wont be in
the list. then when i click on the process combo box, then i just
want the combo box show out the process which is belong to that
partID which i choose only. example: i have 10 customer, when i
choose A from the combo box, then it just show out the partID
which are belong to A, then the process combo box just show the
process for the partID. can anybody help me?

You can configure the rowsource properties of the comboboxes as
follows (you must adjust the names appripriately):

CustomerComboBox:
Select CustomerID, CustomerName From Customer Order By CustomerName

PartComboBox:
Select PartID, PartName From Part Where CustomerID =
[Forms]![TheForm]![CustomerComboBox]

ProcessComboBox:
Select ProcessID, ProcessName From Process Where PartID =
[Forms]![TheForm]![PartComboBox]

Then you should ensure that after a costomer is selected, (in the
AfterUpdate event of the CustomerComboBox), that the other two
comboboxes are requeried and set to Null.
AfterUpdate for the PartComboBox, this has to be done only for the
ProcessComboBox.

Setting the ComboBoxes to Null has the following effect:
You choose an other customer, so the Part and Process boxes are set
to Null, no value is selected in those boxes.
The list os the pasrts displays the parts assiciated with the
selected customer and the list of processes is empty, since you did
not yet select a part. After also selecting a part, the list of
processes displays all processes that are associated with the
selected part.

thanks for helping me. but i still have some question. i have try
to use the way which you teach me. you say that in the AfterUpdate
event of the CustomerComboBox, that the other two comboboxes are
requeried and set to Null. how to do? can you show me?

Of course.
In form design view, select the PartComboBox, open the properties
window (if not already visible), click the event tab, clic in the
After Update property, select [event procedure], click "...".
This will open a code window than contains the following:

Private Sub PartComboBox_AferUpdate()

End Sub

This has to be modified such that it reads like this:

Private Sub PartComboBox_AferUpdate()
ProcessComboBox.Requery
ProcessComboBox = Null
End Sub

For the CustomerComboBox, do the same steps and modify the sub
procedure such that it reads like this:

Private Sub CustomerComboBox_AferUpdate()
PartComboBox.Requery
PartComboBox = Null
Call PartComboBox_AferUpdate
End Sub

That's it.