Combo Box

[Guest]

I would like to use a chosen value from one combo box as criteria to a form
that will be used to retrieve values for another combo box.
For example, in a given form one combo box displays the list of customers.
Based on chosen value from this combo box, I would like to display in another
combo box the list of the products that are belongs to the chosen customer.
Your help is appreciated.

Joseph

 

[Guest]

Hi Joseph,

In the after update event of the 1st combobox have something along the lines
of;

Me.cboProducts.RowSource = "SELECT * FROM tblProducts WHERE CustomerID = " &
Me.cboCustomer.Column(0) & ""

obviously change control, field and table names as required, and make sure
the column number corresponds to the column containing CustomerID (1st column
is 0).

hope that helps,

TonyT..

 

[fredg]

I would like to use a chosen value from one combo box as criteria to a form
that will be used to retrieve values for another combo box.
For example, in a given form one combo box displays the list of customers.
Based on chosen value from this combo box, I would like to display in another
combo box the list of the products that are belongs to the chosen customer.
Your help is appreciated.

Joseph

Leave the 2nd combo box rowsource property blank.

There may be more than one customer with the same name but the
CustomerID field should be unique.
In Combo1 make the [CustomerID] field as the bound column.
The CustomerName field should be displayed as the second column.

Code the First Combo Box AfterUpdate property:

Me![Combo2].RowSource = "Select TableName.[Product] from TableName
Where TableName.[CustomerID] = " & Me![Combo1] & "Order By [Product];"

The above assumes [CustomerID] is a Number datatype.
If [CustomerID] is Text datatype, then use:

Where TableName.[CustomerID] = '" & Me![Combo1] & "' Order By
[Product];"

 

[Van T. Dinh]

For another example, see The Access Web article:

http://www.mvps.org/access/forms/frm0028.htm