Filter subform using combobox

Question

This has taken me nearly 2 weeks and I dont 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 comboboxes 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 afterupdate 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 didnt work..Can someone please help me with this. I have a sample database that i have been working off and by some very strange way, they have managed to do this same thing without calling any code. Is this possible?

Answer 1

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.