Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2014
    Posts
    7

    Unanswered: AutoFill combo box based on value of previous field selected

    Auto-Fill a ComboBox field on a form based on the value of a previous ComboBox field on the same form – both fields come from the same table;

    I have two Tables; HoofDoc and Koper.
    HoofDoc contains two fields called; StuurAan and Adres
    Koper contains two fields called; Koper_Ontvanger and Bestemming

    I created a form “Data Toevoer” to add records to Hoofdoc where StuurAan = Koper_Ontvanger and Adres = Bestemming. For the StuurAan field I created a ComboBox called cboStuurAan where I select Koper_Ontvanger from the Koper table. For the Adres field I created a a ComboBox called cboAdres.

    When I select the Koper_Ontvanger in the cboStuurAan combobox I want to Autofill the cboAdres combobox with the correct Bestemming from Koper.

    I created the following code in cboStuurAan;

    Private Sub cboStuurAan_AfterUpdate()
    'Create a new rowsource for the Adres Combo box after selection of Koper_Ontvanger
    'Verify that User selected a Koper_Ontvanger
    If Len(cboStuurAan) > 0 Then
    With cboAdres
    'set the Adres Rowsource to find only Adres for selected Kopr_Ontvanger
    .RowSource = _
    "SELECT ID,Bestemming " & _
    "FROM Koper WHERE Koper_Ontvanger = " & cboStuurAan
    'Repopulate the Adres Combo Box
    .Requery
    'Add Dazzle by displaying list for the user
    .SetFocus
    .Dropdown
    End With
    End If
    End Sub

    The result I get is an empty cboAdres combo box to select the Bestemming from.
    Can you please help me. I am struggling for a week now , just to get this working.

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    Do not fill combo boxes with code. All it takes is a query.

    To do the multi-combo box
    each combo box has a query for its recordsource.
    Here the user picks a state, then in the next combo picks a Company (in that state)

    The 1st combo,(say cboStates)... User picks a state, then picks a company from the cboCo box.
    The cboStates AfterUpdate event will trigger when the user picks it, and this will update the next combo.

    Code:
    sub cboStates_AfterEvent()
     cboCo.requery
    end sub

    The cboCo query (say qsCoViaState) will reference the cboStates in the query sql
    Select * from tCompany where [ST] ='" & forms!frmMain!cboStates & "'"

    The CO combo must be refreshed (cboCo.requery action) after user picks the state so it can deliver the resulting dataset.

    If there is another combo after this say cboEmps to pick employees, then the cboEmp must be refreshed after user picks cboCo.
    Code:
    sub cboCo_AfterEvent()
     cboEmps.requery
    end sub

  3. #3
    Join Date
    Oct 2014
    Posts
    7
    Ranman, thank you for your proposal which I implemented.
    I do get a list of Company’s now in my cboCo. The problem now is cboCo returns all records and not only the one’s associated to the State I selected in cboStates?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •