0

Table: tblNomenklatur 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?

forRnB
  • 15
  • 6
  • You do not need looping. You can achieve it in simple way. For `txtTyp` combobox create query to `Row Source` and set criteria for `Kategorie` column of your first combobox like `Forms![FormName]![txtKategorie]`. And in `After Update` event just requery second combobox. – Harun24hr Sep 03 '20 at 10:21
  • Im not sure what you mean with: set criteria for Kategorie column of your first combobox like Forms![FormName]![txtKategorie]. Could you please explain? Thank you. – forRnB Sep 03 '20 at 10:36
  • See my answer. You must change `Form1` and `Table1` name with your database `Form` and `Table` name. – Harun24hr Sep 03 '20 at 10:48
  • Thanks a lot for your detailed answer i will try it out and get back to you! – forRnB Sep 03 '20 at 11:00

1 Answers1

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.

enter image description here

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
Harun24hr
  • 30,391
  • 4
  • 21
  • 36
  • Your a genius, the query builder solution worked for me immediately. I often use VBA to solve a problem because i dont have much knowledge with the access query builder. Your solution shows you dont always need VBA or very little. Thanks a lot! – forRnB Sep 03 '20 at 20:42
  • Maybe one more question: How does this part [Forms]![Form1]![txtKategorie] has to look if the Comboboxes are on a subform? – forRnB Sep 04 '20 at 08:17
  • Then use `SubForm` name instead of parent form like `[Forms]![subFrm]![txtKategorie]` – Harun24hr Sep 04 '20 at 08:22
  • I tryed it but it only works when i open ONLY the subfrom. It says Enter parameter value when i click the txtTyp Combobox when the subform is open on the main form. – forRnB Sep 04 '20 at 08:30