How To Create Dynamic Combo Boxes In Access

This post will show you how you can create dynamic combo boxes in MS Access.

In our example we have 2 tables, a list of wards and a list of rooms in those wards.
We want to be able to select a ward and then get the rooms available in that ward.

This is fairly straight forward, but sometimes you just need to break the concept down to its simplest form and see it work, then let your imagination take it to the next level and implement it as part of your own project.

First the 2 tables:

tblWards

To ensure I have related each room to a ward I included the “rWardID” field.

tblRooms

Now I can select a Ward and be shown the Rooms for that ward. When I click the button “Report Selection”, I just get whatever was selected in the boxes.

Here’s the code:

Private Sub btnReport_Click()
    MsgBox "Ward: " & Me.cboWard.Column(1) & vbCrLf & "Room: " & Me.cboRoom.Column(1)
End Sub

Private Sub cboWard_AfterUpdate()
    Dim strSQL As String
    
    strSQL = "SELECT * FROM tblRooms WHERE rWardID = " & Me.cboWard
    
    Me.cboRoom.RowSource = strSQL
    
    
End Sub


Here is the link to the database so you can see how its put together:

How_To_Create_Dynamic_Combo_Boxes_In_Access.accdb

Let me know if you have any questions.

Click here for more information on comboboxes and filter related posts.



Now watch how it’s done:

How To Create Dynamic Combo Boxes In Access,
dynamic combo box access,
dependent drop down list,
populate combobox vba,
access vba combobox value,
access form drop down list


Comments are closed.