Limiting combo box choices using entry in another combo box

[raf]

I have a table of Distributors, with a related table listing the SalesReps for each distributor.

On a customer data entry form, I have a combo box to choose a Distributor. After picking a Distributor, I go to another combo box to choose a SalesRep. In the SalesRep combo box, I want to limit the choices to only those SalesReps listed for the Distributor chosen in the first combo box.

Right now, using the combo box wizard, the Distributor combo box works fine, but when I go to the second, SalesRep combo box, I always get ALL the SalesReps.

How do I limit the choices to only those SalesReps that go with the Distributor chosen in the first combo Box?

My SalesRep table has IndividualID (AutoNum), DistributorNum (relates it to Distributor Table), IndividualName, OtherIndividualInfo....

Thanks -- Renee [sig][/sig]

 

[Robertd]

hi Renee,

is DistributorNum the key field for Distributors?

if so make a query for the salesrep cbo(combobox), include the fields to display for the salesrep cbo, and the DistributorNum this does not have to be returned in the query, just use it for a criteria, in the criteria set this to the forms cbo, use the expresion builder you'll end up with something like

forms![YourMainForm]![YourDistributorCBO]

name the query something like SalesRepSrc
set the data source to your (SalesRepSrc) query
this assumes that the cbo for the distributors includes the Key (hidden).
in the afterUpdate event for the distrinutor CBO

put
' you most likely already have a findfirst method here
'
Me.SalesRep.Requery (change "SalesRep" for your CBO name)

and after you select your distributor the salesrep cbo should now only include the sales reps for that distributor

HTH

[sig]<p>Robert Dwyer<br><a href=mailto:rdwyer@orion-online.com.au>rdwyer@orion-online.com.au</a><br>[/sig]

 

[smedvid]

There is also a very good MS Knowledgebase Article that provides examples. I think u can serach on &quot;Synchronizing? Combo&quot; or something like that .... htwh [sig][/sig]