Filter a combo box

[Ivor Williams]

I have a form with two combo boxes - cboCustomer and cboDestination. Each
customer has multiple destinations. Once I've chosen a customer in
cboCustomer, I would like to have cboDestination populated with only that
customers destinations, much like a form with a subform. Is there a way to
do this without creating a form/subform?

Ivor

 

[John Vinson]

I have a form with two combo boxes - cboCustomer and cboDestination. Each
customer has multiple destinations. Once I've chosen a customer in
cboCustomer, I would like to have cboDestination populated with only that
customers destinations, much like a form with a subform. Is there a way to
do this without creating a form/subform?

Ivor

You must have a table with CustomerID and DestinationID to do this, of
course. I presume you do.

If so, create a Query based on the table, used as the rowsource for
the cboDestination combo; with a criterion of

=[Forms]![formname]![cboCustomer]

on the CustomerID field. You'll need to requery cboDestination in the
AfterUpdate event of cboCustomer.

John W. Vinson[MVP]

 

[Nonprofit Tech via AccessMonster.com]

In the underlying query of the combo box 'cbodestination', for the field
cboCustomer, set the criteria to
=[Form]![<YourFormName>]!cboCustomer

In the combo box 'cboCustomer', place the following code under the On
Change event:

'Clear the destination combo box of its previous value
me.cboDestination.value = null
'Requery the underlying data based on the value of cboCustomer
me.cboDestination.requery