Combo box to filter a combo box

[svrich]

I'm designing a form with two combo boxes "Country" and "Region"



I would like for my combo box "Country" to filter out the available options for "Region" based on the selection of the "Country" combo box.

I know that this is possible. I have seen the code for it. But I cannot seem to apply the code to my own form.

Any and all help, much appreciated.

-S

[pbaldy]

How about

http://www.baldyweb.com/CascadingCombo.htm

[svrich]

i'm going to try this code (again) in the morning. but the set up youve shown is different from what I've seen. Mine is currently set up like the second option but it has the requery at the end instead of the vb null. Either way its a little Greek to me. I can't figure out how to write mine to work with yours. Sorry. Any chance you could explain this a little more in detail?

[pbaldy]

Why don't you post what you're trying and how it fails?

[svrich]

This is the code I found on http://www.databasedev.co.uk/filter_combo_boxes.html

I attempted to modify it so that it would work in my situation (replace Store with "Country" and Manager with "Region")

However I cannot figure out if I need to code all the table fields as is shown in the sample code. If I do its going to be a massive amount of coding bc I have nearly 30 fields of data being returned.

Thanks for you help and understanding.

Private Sub cboStore_AfterUpdate()
Dim sManagerSource As String

sManagerSource = "SELECT [tblManager].[lngManagerID]," & _
" [tblManager].[lngStoreID]," & _
" [tblManager].[strManagerName] " & _
"FROM tblManager " & _
"WHERE [lngStoreID] = " & Me.cboStore.Value
Me.cboManager.RowSource = sManagerSource
Me.cboManager.Requery
End Sub

[svrich]

Hey,

I looked at your codes. Downloaded the sample you posted. Tried it on mine. and it doesn't work. My second field doesn't update.

What am I doing wrong??

[pbaldy]

The sample should certainly work. Is the db in a trusted location or have you enabled code?

[svrich]

Yes. The db is trusted. And your sample works fine. I just can't figure out why my versions of your code is failing.

[pbaldy]

Can you post the db here?

[svrich]

Sorry for being out for so long. I finally figured out the reason why I was getting the syntax error and why the second field wasn't updated. Turns out I was referencing a Column Name that didn't exist.

But now for a second problem. I am having duplicate values returned in my second combo box. But the Row Source is set for "Select Distinct"

Any ideas? Thanks for all your help Paul.

[pbaldy]

DISTINCT operates on all fields in the SELECT clause; perhaps that's what's happening?

[svrich]

Its possible. But the "Select Distinct" is set as the row source for my second box. It only draws from one column on the table. Should I change how my row source is gathered for the second Combo Box?

[pbaldy]

How is it gathered? If there's only one field, the DISTINCT should work.

[svrich]

Well prior to doing the Cascading box the DISTINCT was functioning. I actually had the DISTINCT on box boxes for the Row source and it was working fine.

And I'm not sure what you're asking about how it's gathered.

[pbaldy]

What's the code to change its row source?