Search combobox not working

[Guest]

Dear All
I am kind of stuck and can't figure out whats wrong with this code of mine
so any pointer to the cause will be very helpful.

I have a form with 5-10 subforms. In order to enable users to search
records, I have created a combobox, a textbox and a Search comand button in
the form header. Combox lists about 6 field names after choosing one of which
the user types in the search in the txtbox and clicks Search to filter the
records. The search click triggers the following code:

Private Sub cmdSearch_Click()
Dim str1 As String
Dim str2 As String
Dim stLinkCriteria As Variant
str1 = Me.cboSearch & "=" & "'"
str2 = Me.txtSearch & "'"
stLinkCriteria = str1 & str2

DoCmd.ApplyFilter , stLinkCriteria
Me.lblFilter.Caption = Me.cboSearch & " " & Me.txtSearch
End Sub


When looking for a text field, the code simply does not filter(eg looking
for a Surname Davis). However, looking for a number (eg Subject code
001-01234), the code reports a syntax error: missing '.

I have tried different combinations of str1 & str2; adding ' before str 2
and after str1. Still it reports a syntax eror.

Thanks in anticipation.

Raza

 

[Guest]

My only thought is that when putting these SQL statements together, numbers
should not have the apostrophes around them, e.g.
Surname='Davies' but Subject Code=234

However, if your number is 001-01234 then it has to be text (unless you are
using a mask or format of some sort and in fact the number is 00101234) so it
remains a mystery.

Have you tried:
str1 = Me.cboSearch & "=" & """
str2 = Me.txtSearch & """

or
str1 = Me.cboSearch & "=" & Chr(34)
str2 = Me.txtSearch & Chr(34)

The advantage of using full quote marks for the surname is if you have a lot
of Irish people on the list (e.g. O'Grady) in which case the single quote
goes horribly wrong.