Problem with unbound search page

[Pierre]

I am having a problem with an unbound form that I have create. I’m not
actually sure where I am going wrong, but here is a brief synopsis of what I
have done so far.
- Made an unbound from call “SearchPage†(to include a Subform called “Cat
Codes Subformâ€)
- placed a text box called “Text49†on the SearchPage
The table and/or subform has only two fields: Cat Codes and Definition
Other then the codes I have posted, that is the extent of the form. I would
really appreciate assistance getting this form(code) fixed so that I can
Search for a Cat Code or Definition and have it display any and all results
on the Subform. Any and all recommendations are greatly welcomed.

‘here is the full code used
Private Sub Text49_AfterUpdate()
Dim strSql As String
strSql = “Definition’’â€& me.text49 &â€*â€â€
Me.Cat Codes subform.Form.RecordSource = strSql
End Sub

 

[Allen Browne]

You need to assign a complete query statement to strSql.
It will be something like this (all on one line):
strSql = "SELECT * FROM Table1 WHERE [Definition] Like """ & me.text49 &
"*"";"

For an example of the query statement you need, mock up a query in Query
Design, and then switch to SQL View (View menu, when in query design.)

For an explanation of the quotes above, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

 

[Pierre]

Mr Browne,
I reviewed the Quotation link and tried the code you provided. After making
the changes noted in the code below. I received a Compile error: Method or
data member not found. Upon clicking ok I’m back to MVB window and the line
Private is highlighted in yellow and the word “.From" in the line starting
Me.[Cat Codes subform].From.......is highlighted in Blue. Can you please
inform me of what I’m over looking or doing wrong.

NOTE: I changed Table1 to Table2 b/c Definition is the 2nd table on the
table used in the subform. And should there be a (,) comma or (semi comma
on line starting with strSql

'here is code as it looks in MVB.
Private Sub Text49_AfterUpdate()
Dim strSql As String
strSql = "SELECT * FROM Table2 WHERE [Definition] Like """ & Me.Text49 &" *
"";"
Me.[Cat Codes subform].From.Cat Codes = strSql
End Sub

 

[Allen Browne]

1. "From" needs to be "Form".

2. Since Cat Codes contains a space, you need squeraar brackets around the
name.

Me.[Cat Codes subform].Form.[Cat Codes] = strSql

 

[Pierre]

Ok it didn't give me the error, but now when i typed in an entry into Text49
field. It replaced the first Cat Code to:
SELECT * FROM Table2 WHERE [Definition] Like "99200" * ";

I then changed Cat Codes To Definition on Line
Me.[Cat Codes subform].Form.[Cat Codes] = strSql

I then attempted a new search and it changed the definition to the first Cat
Code on my list to:
SELECT * FROM Table2 WHERE [Definition] Like "Personalities" * ";

Is there something I should have done in the properties of the subform to
allow it to display just the records (Codes) from the search field (text49).
If so, please point me in the right direction.

 

[Pierre]

Mr Browne,
is it possible to start over. I was just reading information from your
website. I'm reading something that seems as if it would work with what I
have. I'm referring to the Filter. Can't I just make the information typed
in text49 be the filter. So when i type in something like "Related", it
filters the definition by that word. Based on what you recall about my form.
Can you review this code or inform me of an easy way to apply this filter to
the afterupdate of Text49.

Code from your Site:
Private Sub Text49_AfterUpdate()
If IsNull(Me.Text49) Then
Me.FilterOn = False
Else
Me.Filter = "Definition = """*"" & Me.text49 & ""*"""
Me.FilterOn = True
End If
End Sub

Note: I changed the code a little but I'm not sure how it should be.--
Work is sometimes hard....but someone has to do it.

 

[Allen Browne]

You can use the filter of the form.

You still need to use Like (not =) with wildcards (such as *.)

Also, the quotes are not right yet. Perhaps:
Me.Filter = "Definition Like ""*" & Me.text49 & "*"""

 

[Pierre]

Ok Mr Browne, you have me as happy as a child at the playground. Thank you
very much.
There is a small change that I would like to add so that the user has more
options. How would I add Cat Codes to the search box (text49). It correctly
searches for WORDS listed in the Definition field of the subform. But if the
user knows the code they are looking for, and need the Correct Definition of
that code. I would like them to be able to type the Code in and have the
definition displayed. Or would you say that it would be easier to just make
a new text box for just the code.

The name of the code field is [Cat Codes] listed on the Cat Codes subform.
This is what I currently have:

Private Sub Text49_AfterUpdate()
Me.[Cat Codes subform].Form.Filter = "Definition Like ""*" & Me.Text49 & "*"""
Me.[Cat Codes subform].Form.FilterOn = True
End Sub

 

[Allen Browne]

See:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

Download the sample database, and see how the filter string is built up. You
will use this technique in lots of places.