MS Access 2016 Form with sub-form using Multiple drop down boxes to link both does not work

Question

Found code to use a drop-down to pull detail records on a sub-form. I use 2 tables both tables have the same column, same field type. Found code on video to link both but got a "mismatch type" error. change the code by adding quotes on the query now I get to errors but the drop-down does not get the select records. See code below: 

Private Sub CmbStateCode_AfterUpdate()
Dim myStateCode As String
myStateCode = "Select * from tblAddendumB where ('[StateCode] = " & Me.CmbStateCode & "')"
Me.tblAddendumB_subform1x.Form.RecordSource = myStateCode
Me.tblAddendumB_subform1x.Form.Requery
End Sub



Answer 1

I think you want

myStateCode = "Select * from tblAddendumB where ([StateCode] = '" & Me.CmbStateCode & "')"

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 2

Sorry I responded too soon. The final form will have multiple Drop Down boxes the user can filter the results.

I am now having problems selecting from multiple drop-down boxes and keeping the selected values on the screen, thanks for your help see below.  

I added code for 2 more comboBoxes but now every time I select from the second or third combo Box the results revert back to a default. 

When I select a Sate code using combo Box#1 I get the records for the selected State.

Next, when I try to use the second or third Drop Down box to narrow down the results set say I want records for the selected State but only the ones with Status = "Closed", the selected State from the first Drop Down boxe changes to a different code  

Option Compare Database

Private Sub CmbStateCode_AfterUpdate()
Dim myStateCode As String
myStateCode = "Select * from tblAddendumB where ([StateCode] = '" & Me.CmbStateCode & "')"
Me.tblAddendumB_subform1.Form.RecordSource = myStateCode
Me.tblAddendumB_subform1.Form.Requery
End Sub

Private Sub CmbStatus_AfterUpdate()
Dim myStatus As String
myStatus = "Select * from tblAddendumB where ([Status] = '" & Me.CmbStatus & "')"
Me.tblAddendumB_subform1.Form.RecordSource = myStatus
Me.tblAddendumB_subform1.Form.Requery
End Sub

Private Sub CmbTable_AfterUpdate()
Dim myTable As String
myTable = "Select * from tblAddendumB where ([TableNum] = " & Me.CmbTable & ")"
Me.tblAddendumB_subform1.Form.RecordSource = myTable
Me.tblAddendumB_subform1.Form.Requery
End Sub

Answer 3

Try this:

Private Sub CmbStateCode_AfterUpdate()
    Call NewSource
End Sub

Private Sub CmbStatus_AfterUpdate()
    Call NewSource
End Sub

Private Sub CmbTable_AfterUpdate()
    Call NewSource
End Sub

Private Sub NewSource()
    Dim strSQL As String
    Dim strWhere As String
    strSQL = "SELECT * FROM tblAddendumB"
    If Not IsNull(Me.CmbStateCode) Then
        strWhere = strWhere & " AND StateCode='" & Me.CmbStateCode & "'"
    End If
    If Not IsNull(Me.CmbStatus) Then
        strWhere = strWhere & " AND Status='" & Me.CmbStatus & "'"
    End If
    If Not IsNull(Me.CmbTable) Then
        strWhere = strWhere & " AND TableNum='" & Me.CmbTable & "'"
    End If
    If strWhere <> "" Then
        strSQL = strSQL & " WHERE " & Mid(strWhere, 6)
    End If
    Me.tblAddendumB_subform1.Form.RecordSource = myTable
    Me.tblAddendumB_subform1.Form.Requery
End Sub

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 4

This makes a lot of sense was thinking somehow you have to add to the SQL WHERE criteria. The video I based this on looked like it worked under MS Access 2013 my guess. 

Thank you and will try this.