Filter subform using combobox

Question

This has taken me nearly 2 weeks and I don't know what else to do. I have a main form (UserSearch) that has a subform (TestUserSub). The associated table for both forms is tblusers. very simple; on the main form (UserSearch) I have a ComboBox associated with the fields in the tblusers eg cmbid, cmbname, cmbdept and so on. All I want, is for a user to make a selection from any of these comboboxes and for the associated fields to display in the subform (TestUserSub). I have tried several different versions of code in the after update event in a couple of the ComboBoxes and nothing is happening in the subform or in other instances I get error message. One example i have tried is filtering running an SQL command

Private Sub cmbid_AfterUpdate()

    Dim strSQL As String

    If IsNull(Me.cmbaccess) Then
        Me.RecordSource = "tblusers"
    Else
        strSQL = "SELECT tblUsers.[Team Member_ID] FROM tblUsers " & _
             "WHERE (((tblUsers.[Team Member_ID])= " & [form_testusersub].[txtid2]))& ";"       
        Me.RecordSource = strSQL
    End If

End Sub

The above didn't work... Can someone please help me with this. I have a sample database that I have been working off of and by some very strange way, they have managed to do this same thing without calling any code. Is this possible?

TT1611 TT1611 · Accepted Answer · 2009-06-25T19:57:36

I was able to figure out the code using the sample below

Private Sub yourcombobox_AfterUpdate()
    Dim LSQL  As String

    If IsNull(Me.yourcombobox.Value) Then
        Form_yoursubform.RecordSource = "tablename"
        Me.yoursubform.Requery
        requerysubform 'macro to requery the whole form
    Else
        LSQL = "select * from tablename"
        LSQL = LSQL & " where field= '" & yourcombobox & "'"

        Form_yoursubform.RecordSource = LSQL
        requerysubform 'macro to requery the whole form

    End If
End Sub

hope this helps.

Filter subform using combobox

1 Answers