How create a search field on a data entry form

I have a data entry form and would like to create a field on that form and allow the user to enter a string of characters and then click a command button that will then display all of the records that have that string of characters in either the name field, (txtName), OR address field, (txtStreetAddress).

I understand that it is possible that more than one record will be displayed but that is ok.

In other words, if for example there is a record with John Doe in the txtName field and the user enters "Doe" in the search field and clicks the command button, then that records would be displayed.  If there is a Jane Doe and a John Doe, then both records would be filtered and displayed.

What would the code look like to do this?

--Steve
SteveL13Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rspahitzCommented:
First, make sure that the form Detail section has all of the fields you want to display, preferably as individual fields. Also set the Default View property to Continuous Forms. Also set the form Record Source to the table with the data.
Then make sure that the search criteria is in the Header section, along with the button.
In your code behind the button, do something like this:

FilterOn = True
Filter = "Name = '" & txtName.Value & "' AND StreetAddress = '" & txtStreetAddress.Value & "'"

Open in new window

0
SteveL13Author Commented:
First of all it has to be a single view form.  I've altered the code to reflect the real names of the fields and form field names.  But I get an error wen I click on the command button... "Undefined function 'SellersName' in expression"

Here's my current code:

Private Sub cmdSearch_Click()

    FilterOn = True
    Filter = "SellersName = '" & txtSellersName.Value & "' AND StreetAddress = '" & txtStreetAddress.Value & "'"

End Sub

Open in new window

0
Rey Obrero (Capricorn1)Commented:
try

   FilterOn = True
    Filter = "[SellersName] Like '*" & txtSellersName.Value & "*'"
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Rey Obrero (Capricorn1)Commented:
try

   
    me.Filter = "[SellersName] Like '*" & txtSellersName.Value & "*'"
    me.FilterOn = True
0
rspahitzCommented:
>"Undefined function 'SellersName' in expression"

This is often caused by a name conflict.  As Rey indicated, you can solve that by surrounding DB field names in brackets [...]

So does this work? (also changing exact matches to wildcards)
Private Sub cmdSearch_Click()

    FilterOn = True
    Filter = "[SellersName] LIKE '*" & txtSellersName.Value & "*' AND [StreetAddress] LIKE '*" & txtStreetAddress.Value & "*'"

End Sub

Open in new window

0
SteveL13Author Commented:
I tried:

FilterOn = True
Filter = "[SellersName] Like '*" & txtSellersName.Value & "*'"

and

me.Filter = "[SellersName] Like '*" & txtSellersName.Value & "*'"
me.FilterOn = True

and

FilterOn = True
Filter = "[SellersName] LIKE '*" & txtSellersName.Value & "*' AND [StreetAddress] LIKE '*" & txtStreetAddress.Value & "*


and none worked.  Still get  "Undefined function 'SellersName' in expression"
0
PatHartmanCommented:
Then SellersName must not be the name of the column.

PS using the Me. qualifier when referring to form and report controls, methods, properties, and fields gives you intellisense and is also more efficient for the compiler since the compiler knows immediately where the object is defined.

Me.Filter = "[SellersName] LIKE '*" & Me.txtSellersName & "*' AND [StreetAddress] LIKE '*" & Me.txtStreetAddress & "*'"
Me.FilterOn = True
0
rspahitzCommented:
>Still get  "Undefined function 'SellersName' in expression"

Clearly there's either something missing or a name conflict. (Is there a space in the field name? Not plural?)

Try manually putting "SellersName" in the form's Filter property to see if it complains when you try to view the records.
0
Rey Obrero (Capricorn1)Commented:
@steve,

in the vba window, do a Debug >Compile

post here the errors you get..
0
SteveL13Author Commented:
Using this I get no compile error.  But I got no compile errors with any.

    Me.Filter = "[SellersName] LIKE '*" & Me.txtSellersName & "*' AND [StreetAddress] LIKE '*" & Me.txtStreetAddress & "*'"
    Me.FilterOn = True

But when I click the command button I stay on the original record, not the one I expect to see.
0
rspahitzCommented:
Check in Access to make sure there are no other filters on for the record/form.
0
SteveL13Author Commented:
Attached... a very stripped down version of the database.  Please enter something in the search field and click the command button.  The two records that are in the table are:

Records
So if you enter "123" both records should be available to look at.  But if you enter "sam" then just one record should be there.
Test.accdb
0
Rey Obrero (Capricorn1)Commented:
open the form in design view
make sure the Filter property is BLANK
if there is an entry in the property, delete it and save the form

try again
0
Rey Obrero (Capricorn1)Commented:
change this

'    Me.Filter = "[SellersName] LIKE '*" & Me.txtSellersName & "*' AND [StreetAddress] LIKE '*" & Me.txtStreetAddress & "*'"
 with

    Me.Filter = "[SellersName] LIKE '*" & Me.txtSearchField & "*' OR [StreetAddress] LIKE '*" & Me.txtSearchField & "*'"
    Me.FilterOn = True
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SteveL13Author Commented:
Worked!  Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.