Filtering a listbox using a text box

[mdex]

Afternoon,

I have a form on which I use combo boxes to filter a listbox using the following code.

------------------------------------
Private Sub FilterpartsList()

Dim strRS As String

' Filter the list box appropriately based on the combo box selection(s)
strRS = "SELECT partsquery.partname, partsquery.Heritage, partsquery.Description FROM partsquery"

If Not IsNull(Me.cbomodelID) Then
strRS = strRS & " WHERE modelID = " & Me.cbomodelID
ElseIf Not IsNull(Me.cbomakeID) Then
strRS = strRS & " WHERE makeID = " & Me.cbomakeID
ElseIf Not IsNull(Me.cbodeviceID) Then
strRS = strRS & " WHERE deviceID = " & Me.cbodeviceID
End If

strRS = strRS & " ORDER BY partsquery.heritage;"

Me.lstpartsID.RowSource = strRS

Me.lstpartsID.Requery

End Sub
------------------------------------
What I would like to do is also have a text box so if I have a part number if returns that row in the list box.

I tried amending the above code but no records are returned.

-----------------------------------
Private Sub Filterpartsnumber()

Dim strRS As String

' Filter the list box appropriately based on the combo box selection(s)
strRS = "SELECT partsquery.partname, partsquery.Heritage, partsquery.Description FROM partsquery"

If Not IsNull(Me.partnumbertxt) Then
strRS = strRS & " WHERE partname = " & Me.partnumbertxt
End If

strRS = strRS & " ORDER BY partsquery.heritage;"

Me.lstpartsID.RowSource = strRS

Me.lstpartsID.Requery

End Sub
-----------------------------------

I'm very much a novice so am I missing something obvious?

Cheers

 

[pbaldy]

If the partname field is text:

strRS = strRS & " WHERE partname = '" & Me.partnumbertxt & "'"

 

[MarlaC]

Also, assuming partname is text,

If Not IsNull(Me.partnumbertxt) Then
strRS = strRS & " WHERE partname = " & Me.partnumbertxt
End If

may work better as

If Len(Trim(Me.partnumbertxt)) > 0 Then
strRS = strRS & " WHERE partname = " & Me.partnumbertxt
End If

or

If (Me.partnumbertxt) > " a" Then
strRS = strRS & " WHERE partname = " & Me.partnumbertxt
End If

as IsNull can be tricky with strings.

 

[mdex]

Sorry for the late reply.

Cheers Pbaldy thats sorted it.

Also thanks MarlaC.

 

[MarlaC]

You're welcome. Good luck.

 

[pbaldy]

Happy to help!