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:
To ensure I have related each room to a ward I included the “rWardID” field.
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:
Let me know if you have any questions.
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