How do i use Dlookup for Multiple Criteria to create a dynamic Combobox

Question

I have two Comboboxes on a from: txtKategorie and txtTyp. The values for the frist Combobox (txtKategorie) are fix! I want the values of the second Combobox (txtTyp) to change according to what the user chooses in the first one. If the user chooses "Datalogger" the second combobox should only contain "Base Layer Classic" and "Base Layer Plus" as can be seen in the image. The same idea is true for "Accelerometer".

I've put my code in the AfterUpdate Event of the first Combobox:

 If txtKategorie.Value = "Datalogger" And txtTyp.ListCount = 0 Then   
    i = 1
    Do While txtTyp.ListCount < DCount("ID", "tblNomenklatur", "[Kat] = 'K'")
    txtTyp.AddItem DLookup("[Typ]", "tblNomenklatur", "[ID] =" & i And "[Kat] = 'K'")
    'And "[Kat] = 'K'"
    i = i + 1
    Loop

When the form opens only the first Combobox "txtKategorie" has Values. When the user chooses Datalogger the code checks how many records in the table have the [Kat] = "K" to define how long the Do While-Statement will run. Then the "txtTyp.AddItem"-Statement should add "Base Layer Classic" and "Base Layer Plus" to the "txtTyp" Combobox. But unfortunately the Code doenst work. There is a problem with the Dlookup-Statement containing tow criterias. If i remove either one of the two criterias the Code works but delivers wrong results for the second Combobox obviously. If i leave it like this the second Combobox stays empty. Does someone know what im doing wrong?

Answer 1

You can do it easily by below code. Change table name with your table name.

Private Sub txtKategorie_AfterUpdate()
    Me.txtTyp.RowSource = "SELECT DISTINCT Table1.Typ FROM Table1 WHERE Table1.Kategorie='" & Me.txtKategorie & "'"
    Me.txtTyp.Requery
End Sub

Or you can do it graphically from row source query builder. Below are steps.

1. Combobox txtKategorie is fix. Fine!
2. For second combobox txtTyp follow the below steps.
3. Select combobox txtTyp. From property windows select Row Source then click on query builder ... small three dot. See screenshot.
4. In query builder window build a query from your data table like screenshot and set criteria for Kategorie column is [Forms]![Form1]![txtKategorie] Save and close the query bulder window.

Now for Combobox txtKategorie in After Update event write below line to requery txtTyp. You are done!

Private Sub txtKategorie_AfterUpdate()
    Me.txtTyp.Requery
End Sub