Multiple Filter Search Form - Combo Box?? 1

[Elvis72]

I have a great multiple filter search form:

Private Sub Command12_Click()

Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"


If Not IsNull(Me.txtfirstname) Then
strWhere = strWhere & "([First Name] Like ""*" & Me.txtfirstname & "*"") AND "
End If


If Not IsNull(Me.txtlastname) Then
strWhere = strWhere & "([Last Name] Like ""*" & Me.txtlastname & "*"") AND "
End If

If Not IsNull(Me.txtexperience) Then
strWhere = strWhere & "([Experience] Like ""*" & Me.txtexperience & "*"") AND "
End If

If Not IsNull(Me.txtexperience1) Then
strWhere = strWhere & "([Experience] Like ""*" & Me.txtexperience1 & "*"") AND "
End If
If Not IsNull(Me.txtexperience2) Then
strWhere = strWhere & "([Experience] Like ""*" & Me.txtexperience2 & "*"") AND "
End If
If Not IsNull(Me.txtexperience3) Then
strWhere = strWhere & "([Experience] Like ""*" & Me.txtexperience3 & "*"") AND "
End If

If Me.ckplantservices = -1 Then
strWhere = strWhere & "([Plant Services] = True) AND "
ElseIf Me.ckplantservices = 0 Then
strWhere = strWhere & "([Plant Services] = False) AND "
End If

If Not IsNull(Me.txtyrsexp) Then
strWhere = strWhere & "([Years Experience]>='" & Me.txtyrsexp & "') AND "
End If
If Not IsNull(Me.txtyrsexpend) Then
strWhere = strWhere & "([Years Experience]<='" & Me.txtyrsexpend & "') AND "
End If

If Not IsNull(Me.txtplanttype) Then
strWhere = strWhere & "([Plant Type] Like ""*" & Me.txtplanttype & "*"") AND "
End If

If Not IsNull(Me.txtplanttype1) Then
strWhere = strWhere & "([Plant Type] Like ""*" & Me.txtplanttype1 & "*"") AND "
End If

If Not IsNull(Me.txtplanttype2) Then
strWhere = strWhere & "([Plant Type] Like ""*" & Me.txtplanttype2 & "*"") AND "
End If

If Not IsNull(Me.txtplanttype3) Then
strWhere = strWhere & "([Plant Type] Like ""*" & Me.txtplanttype3 & "*"") AND "
End If

If Not IsNull(Me.memoresume) Then
strWhere = strWhere & "([Resume Memo] Like ""*" & Me.memoresume & "*"") AND "
End If


lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
Debug.Print strWhere

Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

But the Experience Txt boxes I want to change to combo boxes because it is based on a list table with a Experience ID and Experience Name. When I try and do that it doesn't work.

Is it possible to make Experience a Combo Box and apply the filter?

 

[MajP]

My guess is that you have it bound to the ID field not the Name field but your search is looking for the name field.
[Experience] Like ""*" & Me.txtexperience3 & "*""

so this is returning
[Experience] Like "*1*"

Change the bound column

 

[Elvis72]

OK...well I got this far:

I put the Control Source as:

=TableExperience!Experience

The Row Source is:

SELECT TblExperience.Experience FROM TblExperience;

Which shows me the list but does not let me select one?

 

[Elvis72]

I'm a dork...Got Rid of Control Source...duh