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

  • 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
    



    • Edited by Javx1 Friday, May 12, 2017 6:39 PM
    Friday, May 12, 2017 2:45 PM

Answers

  • I think you want

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


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

    • Marked as answer by Javx1 Friday, May 12, 2017 5:21 PM
    Friday, May 12, 2017 3:46 PM

All replies

  • I think you want

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


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

    • Marked as answer by Javx1 Friday, May 12, 2017 5:21 PM
    Friday, May 12, 2017 3:46 PM
  • 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
    


    Friday, May 12, 2017 6:15 PM
  • 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)

    Friday, May 12, 2017 6:52 PM
  • 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. 

    Friday, May 12, 2017 8:08 PM