more search woes

[mattbland]

ive got a combo box and a textbox both unbound for finding a particular field. what i want to happen is select either 'product code' or ingredient name' from the combo box, and then search either of these fields as the filter i am using only searches either a numeric field (product code) or text field (product name). This is my code:

Private Sub Txtsearch_afterupdate()
Dim Comboval As String

Comboval = Searchby.Value

If Comboval = "Product Code" Then
If Nz(Me.Txtsearch, "") = "" Then
Me.FilterOn = False
Else
Me.Filter = "[product_code] = " & Me.Txtsearch
Me.FilterOn = True
End If

ElseIf Comboval = "Ingredient Name" Then
If Nz(Me.Txtsearch, "") = "" Then
Me.FilterOn = False
Else
Me.Filter = "[product_name] = """ & Me.Txtsearch & """"
Me.FilterOn = True
End If
End If

End Sub

The 'Product Code' bit works fine, but trying to search for an ingredient by name just brings up a blank field. HELP! (please)

 

[charityg]

Try:
Private Sub Txtsearch_afterupdate()
Dim Comboval As String

Comboval = Searchby.Value

If Comboval = "Product Code" Then
If Nz(Me.Txtsearch, "") = "" Then
Me.FilterOn = False
Else
Me.Filter = "[product_code] = " & Me.Txtsearch
Me.FilterOn = True
End If

ElseIf Comboval = "Ingredient Name" Then
If Nz(Me.Txtsearch, "") = "" Then
Me.FilterOn = False
Else
Me.Filter = "[product_name] = '" & Me.Txtsearch & "'"
Me.FilterOn = True
End If
End If

End Sub

Your quotes look funny in your post. I think that might be the problem.
To translate (because it's a little difficult to see without crossing your eyes):
DoubleQuote[product_name]=singlequote doublequote & Me.Txtsearch & DoubleQuote singlequote DoubleQuote

Hope this helps!

 

[mattbland]

hey,

thanks for the help, but still just opens a new record. Any other ideas??

Matt

 

[Fizzio]

Try this

Me.Filter = "[product_name] = """" & Me.Txtsearch & """

 

[mattbland]

hey fizzio,

tried it and im getting an error msg sayin i cant assign a value to that object. thanks anyway - any other ideas?

 

[Jeff Bailey]

There is only one thing I can suggest ...

Me.Filter = "[product_name] = """ & Me.Txtsearch & """"

change to

Me.Filter = "[product_name] = """ & Me!Txtsearch & """"

i.e. change the dot in Me.Txtsearch to a bang (!)

Untested theory!

 

[mattbland]

nope, its just changed back to goin to a blank record. what does the '!' do anyway??

plus, with the filter, it wont find partial matches. does anybody know how to find partial matches??

 

[Jeff Bailey]

Me.Filter = "[Surname] = """ & Me!txtSearch & """"
Me.FilterOn = True

works on my machine (A97).

Are you quite certain that the form is not set to Data Entry?
and that Allow Filters is set to Yes?

Can't think of anything else (assuming you are entering a valid search criterion in txtSearch)

 

[Fizzio]

To find partial matches you need to add a wildcard. This may be why your search is throwing up nothing if you have not typed the name exactly

Try Me.Filter = "[Surname] = ""*" & Me!txtSearch & "*"""

 

[mattbland]

Hey, still no luck. Filters are allowed, tried setting data entry to both yes and no, but still just opens new record. spelling things right or wrong still does the same!! Its wierd cos searching for product code is working fine (numeric). Heres my updated code -

Private Sub Txtsearch_afterupdate()
Dim Comboval As String

Comboval = Searchby.Value

If Comboval = "Product Code" Then
If Nz(Me.Txtsearch, "") = "" Then
Me.FilterOn = False
Else
Me.Filter = "[product_code] = " & Me.Txtsearch
Me.FilterOn = True
End If

ElseIf Comboval = "Product Name" Then
If Nz(Me.Txtsearch, "") = "" Then
Me.FilterOn = False
Else
Me.Filter = "[product_name] = ""*" & Me!Txtsearch & "*"""
Me.FilterOn = True
End If

End If

End Sub