Cascading combobox based on related table

[rhewitt]

I'm trying to use cascading comboboxes to filter data. The user will use the boxes to choose a project to run a report.



I have a combobox (cboState) that queries all the distinct states from a related table. Once the user makes a selection there it should filter the second combobox to only contain projects in that state. In the example below, I've selected Alabama in cboState. This project, however, only covers one state which is Louisiana. How do I need to format my query to return one record if the value of cboState is included in the related State table

Just to clarify the cascading combobox portion works fine. I have another value (filter by region) which is in the project table as opposed to a related table. When I make a selection in that box, the project combobox is properly filtered.

Here's the code on for the after update event on cboState

Private Sub cboState_AfterUpdate()
    cboProject.RowSource = "SELECT Project.ProjectID, Project.ProjectTitle, Project.FWSregion, State.State " & _
        "FROM Project, State " & _
        "WHERE  State.State = '" & cboState & "'; "
    cboProject = Null
    
    Debug.Print cboProject.RowSource
End Sub

[orange]

I think you have a table design issue or an sql issue.
The sql for your row source shows 2 tables, but you have no join. Based on your sample data, I would guess you have 1 project in Alabama, and you are getting repeated data for each Project*State in your table.

Can you please post a copy of your database (no confidential info) in zip format? Also, highlight an example of what you want to see based on some data and combo selections -- to focus the investigation.

[rhewitt]

Thanks -- I'd been copying and pasting from some of my other code and forgot to add the join. Everything is working as expected with the following code:

Private Sub cboState_AfterUpdate()
    cboProject.RowSource = "SELECT Project.ProjectID, Project.ProjectTitle, Project.FWSregion, State.State " & _
        "FROM Project INNER JOIN State ON Project.ProjectID = State.ProjectID " & _
        "WHERE  State.State = '" & cboState & "'; "
    cboProject = Null
End Sub

[orange]

Glad you have it sorted out. Thanks for posting the solution.