Changing the rowsource of a combo box based on another combo box

Question

I have a forms frmDatasheet and frmComboHolder contained in NavigationSubform. frmDatasheet is bound to table tblBuilding. Form frmComboHolder contains the two combo boxes.

The combo boxes are used to filter records to make the database more user friendly.

I want to change the rowSoruce of combo2 based on the filtered results displayed on frmDatasheet.

Private Sub Form_Load()

Dim Qry1 As String As String
Dim Qry2 As String String

Qry1 = "SELECT DISTINCT Room FROM tblBuilding WHERE Room IS NOT NULL"
Qry2 = "SELECT DISTINCT TenancyCode FROM tblBuilding WHERE TenancyCode IS 
NOT NULL"

With Me.cobo1
Me.combo1.RowSource = Qry1
End With

With Me.combo2
Me.combo2.RowSource = Qry2
End With

End Sub


Private Sub cobo1_AfterUpdate()

Dim Qry3 As String
Dim Qry4 As String

Qry3 = "SELECT * FROM tblBuilding WHERE Room = combo1.Value  ORDER BY 
ItemName ASC"

With Me.Combo1
Me.frmDatasheet.Form.RecordSource = Qry3

Me.combo2.RowSourse = "SELECT DISTINCT TenancyCode FROM [Qry3] WHERE 
TenancyCode IS NOT NULL" 

End With

End Sub

Gustav Gustav · Accepted Answer · 2019-07-30T14:12:41

Try to concatenate the value:

Qry3 = "SELECT DISTINCT TenancyCode, Room FROM tblBuilding WHERE Room = " & combo1.Value & " ORDER BY 
ItemName ASC"

or, if value is text:

Qry3 = "SELECT DISTINCT TenancyCode, Room FROM tblBuilding WHERE Room = '" & combo1.Value & "' ORDER BY 
ItemName ASC"

Changing the rowsource of a combo box based on another combo box

1 Answers