How to Filter County Options in a ComboBox Based on State

[jrichardson10]

So I have a table with my options for state, and a second table with all the county options, sorted by state. On a third form where both pieces of data are recorded, how do I filter what county options are available based upon the state I have selected?



This will ultimately pull over to the form I have also, but I figured this was the best place to start. Thanks!

[pbaldy]

Try

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

[jrichardson10]

Paul,

That seems to be what I want to accomplish, but I'm not particularly well-versed in coding. I do everything the dummy way. How would I accomplish this without the coding part? The theory looks perfect, I'm just not great at the application part.

[pbaldy]

Well, I gave you the code. The least code is the first, where the second combo's source refers to the first and you just requery it. You could do that with a macro if you prefer.

[jrichardson10]

Paul,

You did, thank you. I tried the code you provided, both of them even. I altered the names to reflect my database's naming system but I'm not seeing the same result. I've attached the database as we were just working on it so you can see that I'm not having much luck. I've saved and reopened but nothing has changed. If it's an obvious fix I apologize but both my coworker and I are lost.

[pbaldy]

I don't see an attempt at cascading combos anywhere. There's no code behind the state combo on the parent form. You'd need to make sure you compared the numeric state value.

[jrichardson10]

Well that may be partially because I (wrongly) put it on the FKCounties instead of States. Brilliant. I moved it to no avail.

I checked and changed my variables to all number variables. I included the code this time, but I promise it's there, though maybe not where it's supposed to be... Sorry I'm kind of terrible at this. I'm self taught over the last few years and because I use access once every 5 or 6 months, I haven't learned or retained enough to be well-versed.

Option Compare Database
Option Explicit
Private Sub cboFKStates_AfterUpdate()
Me.cboFKCounties = vbNullString
Me.cboFKCounties.Requery
End Sub
Private Sub cboFKStates_AfterUpdate()
Dim strSource As String
strSource = "SELECT ID_County " & _
"FROM tblCounties " & _
"WHERE FKState = '" & Me.cboFKStates & "' ORDER BY FKCounties"
Me.cboFKCounties.RowSource = strSource
Me.cboFKCounties = vbNullString
End Sub

[pbaldy]

Ah, you must have changed the name of the combo. The code is not associated, and the name of the combo is just "FK_States". This appears to work:

  Dim strSource     As String

  strSource = "SELECT ID_County, var_CountyName " & _
              "FROM tblCounties " & _
              "WHERE FKState = " & Me.FK_States
  Me.FK_Counties.RowSource = strSource
  Me.FK_Counties = vbNullString