MS Access Form Cascade Combo Box

Question

Let me preface this by acknowledging I'm a database, SQL and VBA newb. I've been tasked with hacking together something for which there is not external budget.

I'm having issues setting up a simple combobox based on a simple 2 column query called "Immeubles" (buildings in French).

The query features 2 columns that look for data in 2 different tables. One's called "Immeuble" (building in French), the other is called "Adresse" (address in French).

In a form, I've set up the first Combo box to only show distinct results from the "Immeuble" column. This combobox's name is "cboListe_Immeuble".

I'd like for the second combo box to only show the address that corresponds to the "Immeuble" value in the first combo box. I've named this second combobox "cboListe_Adresse".

For this example, the "Immeuble" is called "ROM 1", which has 3 corresponding adresses.

I've tried 2 different ways:

1st way.

Using a simple query builder on the second combo box :

SELECT DISTINCT Immeubles.Adresse
FROM Immeubles
WHERE (((Immeubles.Adresse)=[Forms]![SELECTION]![cboListe_Immeuble]))
ORDER BY Immeubles.Adresse;

2nd way.

Coding in visual basic code.

Me.cboListe_adresse.RowSource = "SELECT Immeubles.Adresse " & _
                          "FROM qryImmeubles " & _
                          "WHERE Immeubles.Immeuble = " & (Me.cboListe_Immeuble) & _
                          "ORDER BY Immeubles.Adresse"

The first way doesn't work at all.

The second way generates an error

Syntax error (missing operator) in query expression 'Immeuble = ROM 1ORDER BY Adresse'

At the end of the sub, I've added a requery line

"Me.cboListe_adresse.Requery"

What am I doing wrong?

Answer 1

Try using the correct spacing and quoting:

Me.cboListe_adresse.RowSource = "SELECT Immeubles.Adresse " & _
    "FROM qryImmeubles " & _
    "WHERE Immeubles.Immeuble = '" & Me.cboListe_Immeuble & "' " & _
    "ORDER BY Immeubles.Adresse"

Answer 2

I had added a "qry" that broke the code.

This is the working code for my example :

Me.cboListe_adresse.RowSource = "SELECT Immeubles.Adresse " & _
    "FROM Immeubles " & _
    "WHERE Immeubles.Immeuble = '" & Me.cboListe_Immeuble & "' " & _
    "ORDER BY Immeubles.Adresse"

Thank you @HansUp for your time and effort!