Forum Discussion

Gilmour10's avatar
Gilmour10
Copper Contributor
Jul 05, 2024
Solved

Error in cascading Combo Boxes

Hi Access community,   In a form, I'd like to filter choices in combo box cobStatus based on the value I select in combo box cobCountry. The code I copied from an online post is:   Private Sub co...
access
  • Gilmour10's avatar
    Gilmour10
    Jul 17, 2024

    Hi XPS35, thank you for your help, I think I'm on the right path! 

     

    cobStatus now presents only the choices in the 'Status' field of the data source that are available for the selected country! However, this happens when, after selecting the country I go and try to select the Status:

     

     

    The system asks me to re-enter the country manually.

    1. How do I eliminate that error? and

    2. How can I filter the two other combo boxes based on choices made in the first two? What would the After Update event look like for Concept and Project Type? 

     

    Thank you so much for your help!

arnel_gp's avatar
arnel_gp
Iron Contributor
Jul 06, 2024

Gilmour10 , you do not need [Tables]![Project Database] on your query string.

the name of the table is enough.

 

also country table have no relationship with status table.

 

Gilmour10's avatar
Gilmour10
Copper Contributor
Jul 06, 2024

Thank you arnel_gp,

 

I just started using some VBA in Access recently, so I'm not really sure of how to use certain terms.

I used [Table]! because there are also a report and a query called 'Project Database' in the file. 

 

Both Status and Country come from the same table. Here's what I'm trying to achieve:

 

Thank you for your help!

 

  • XPS35's avatar
    XPS35
    Iron Contributor
    Jul 06, 2024

    As Country is a text field you need
    "WHERE [Project Database].Country = '" & Me.cobCountry & "'" & _

    Also put a Debug.Print strSQL after the strSQL =........... (and press Ctrl-g after the error occurred).

    You will notice some spaces are missing.

    • Gilmour10's avatar
      Gilmour10
      Copper Contributor
      Jul 06, 2024

      Thank you XPS35,

       

      Actually, I rewrote the code and I'm getting a new error.

       

      After I select the country, when I click on the Project Status combo, I get this pop up:

       

       

       If I type in the name of the Country, the Project Status combo shows the values I was looking for. How do I eliminate this pop up? The code currently looks like this:

       

      Private Sub cobCountry_AfterUpdate()
      strSQL = "SELECT [Project Database].[Project Stage]" & _
      "FROM [Project Database] " & _
      "WHERE [Project Database].Country = " & Me.cobCountry & _
      " ORDER BY [Project Database].[Project Stage];"
      Me.cobStatus.RowSource = strSQL
      Me.cobStatus.Requery
      End Sub

       

      What change do I need to make for the pop up not to be needed?

       

      Thank you for your help!

      • XPS35's avatar
        XPS35
        Iron Contributor
        Jul 06, 2024
        Try
        strSQL = "SELECT DISTINCT [Project Database].[Status]" & _
        " FROM [Project Database]" & _
        " WHERE [Project Database].Country = '" & Me.cobCountry & "'" & _
        " ORDER BY [Project Database].[Status];"

Resources