Populate Combo Box Based on Another Combo Box Selection

[jovuco]

How do I populate a combo box with a list of items, that would be based on the item I chose for another combo box?

thanks, jo

 

[WildHare]

Use the first combo box's selection as the CRITERIA in a query that extracts the second box's values.

Use that query as the recordsource for the 2nd combo box.

When you change the first C/B, make sure to REQUERY the 2nd box:

Sub MyFirstCombo_AfterUpdate()
me!mySecondCombo.Requery
End Sub
Remember, you're unique - just like everyone else
You're invited to visit another free Access forum:

http://www.forumco.com/jmhare

or my site,

http://www.jmhare.com

 

[RegionsRob]

Jim (or anyone else who can help) -
How do I then take the value of the second combo box and get it into a field in the table that underlies the form? (since the recordsource has to be a query and not the field in the table)

 

[WildHare]

It sounds like your 2nd C/B is an UNbound guy, that's OK. If you are positioned at the record you want to change after you have made your selection in the 2nd combo box, it's relatively simple to assign the value you've selected to a field in the FORM's recordsource. The FORM should be based on a query as well, BTW, at least that's what nearly all the gurus say.

Assuming you have a field called ZOT, and it is in the recordsource for the form, just place an assignment statement in the after-update event of C/B #2:

Sub CB2_After_Update()
... {whatever else you need}
Me!Zot = Me!CB2
... { whatever else you need}
End Sub

Remember, you're unique - just like everyone else
You're invited to visit another free Access forum:

http://www.forumco.com/jmhare

or my site,

http://www.jmhare.com

 

[RegionsRob]

I was afraid that was what I was going to have to do. Turns out it wasn't difficult at all. However, even that doesn't get me where I want to go. What I need is this:

I need a subform in Datasheet view where the fields are formatted as combo boxes. I want to select a state in one combo box, which will display only the counties for that state in the 2nd combo box. (The situation explained above.) Also, I want the Text for the State and county names to display, and the code for the state/county gets stored in one of the tables in the query underlying the subform. I have this working on a form where only one record shows at a time. The problem is that unbound fields in datasheet view carry the current value through all records.

I've also tried making the fields in the base table lookup fields. That works for the State, but not for the county combo box.

Have I explained this well enough? Will I be able to do it at all?

 

[WildHare]

Rob, not sure you can do this on a rather brain-dead "Datasheet", but I know you can make a CONTINUOUS form look very much LIKE a datasheet...Your trick is going to be requerying the 2nd combo box after you make a change in the first one. That wouldn't have been possible in a datasheet, AFAIK.

Remember, you're unique - just like everyone else
You're invited to visit another free Access forum:

http://www.forumco.com/jmhare

or my site,

http://www.jmhare.com