Multiple combo boxes- Need Direction

[lewis33]

Hi- Looking for some pointers or direction on this problem.
I think it is pretty straight forward but here goes:

1. In my main form I want to have a drop down list of states.
When one is chosen I want the other drop down list to show the applicable counties in the chosen state.

2. Similarly, on a second form, I want to do a drop down of states, then show applicable cities and show applicable zip codes.

For the either one of these, I'd like to allow multi-select on the last drop down list.

First- Do I have to have two or three drop down combo boxes to do this? I have used one drop down box that shows the state and county or the state, city and zip but I think the object has to be set to only one value and I want my query to pull in both or all three of the parameters.

In advance- Dan from Cleveland says thanks. It's 10 degrees with 70 mile per hour winds around here so any help would warm up my situation.

 

[DCCoolBreeze]

There are several methods you can use to address this. Some easier then others. It looks like ultimately you would like to use on drop down instead of multiple. I would first make it work with multiple and them combine them into one.

First create two drop downs. The second (example county combo) would consist of dymanically generated SQL statement instead of an imbedded data source. You will generate the SQL statement in the first drop down and them insert that into the second drop down using vba (something like control.datasource = sqlStatement) and be sure to do a control.requery or control.refresh (it has been a while since I have worked with MS Access). You second combo will be ready to use. I have done this many times.

Second. You can have two combos. One exactly on top of the other. Do what I mentioned above only now toggle their control.visible = true/false property

Finally, If you really want to just use one, then you will have to replace the current data source in the combo with the new one and perform the control.requery and/or control.refresh (control.repaint may be one of the items too).

Of course this will have to be done under vb/vba on events.

Good luck and let us know if it works!

 

[lewis33]

DC CoolBreeze,

Thanks a ton- those are great ideas. I will start with the first version and go from there. I think that may work better as I want to keep them both visible at least initially.

I want the first combo to darken after click so I will have to play around with that a bit so that it is still visible but the focus goes to the next combo.

 

[lewis33]

Got it mostly working- I just added a requery option in macro on the got focus property of the second combo. Thanks

Still seeing an odd thing on these multi-column drop down lists- whenever I choose a value in the drop down list - the drop down list then holds the value but it only displays the first column in the multi-column drop down.

For example, I select
OREGON | CROOK COUNTY

When I click off, only Oregon shows - if I hit the drop down list OREGON | CROOK COUNTY shows up again. Must be doing something wrong in the properties of the combo box.