Searching specific fields vba

[kitty77]

I have a form with 10 fields. I want to create a command button that only searches 5 of the fields.
I want it to be "any part of field" too.

Is this possible?

 

[Cronk]

I presume the 5 fields are text. If so, you can use Like "*<search string>*" to search for the occurrence of a string in the field. Use the OR operator for multiple fields.

....WHERE Field1 Like "*abc*" OR Field2 Like "*abc*" OR Field3 Like "*abc*" .....

 

[kitty77]

Yes all text. But I want to be able to enter a value, not have it there already. Can you provide me with a full example?

Thanks!

 

[Pat Hartman]

Create an unbound control on the search form and enter the search value there. Then the query can reference that control.

Where Field1 Like "*" & Forms!yourform!yourcontrol & "*" AND Field2 Like "*" & Forms!yourform!yourcontrol & "*" AND ...

 

[kitty77]

Can you provide me with a complete example? Not following it, sry...

 

[kitty77]

I'm really just looking to use the Access search but restrict it to a few fields (any part of field) instead of the whole document.

 

[Gasman]

As a picture is meant to be worth a thousand words.

https://www.youtube.com/results?search_query=access+search+form+multiple+fields

 

[isladogs]

See http://allenbrowne.com/ser-62.html

 

[Mike Krailo]

The attached database is an example of three column search from one search box. Uses a variation on the concept in the Access Learning Zone video pointed out by Gasman.

 

[kitty77]

Yes, I've seen a few of those but I just want to use the built in search from Access. Create a VBA button that works the same but let's me use only a few fields.

 

[Mike Krailo]

The builtin search does not work the way you requested. You have to roll your own.

 

[kitty77]

I'm using the following now... So, no way to do what I'm talking about here?
Can't make it look through specific fields here?

Me.Search.SetFocus
RunCommand acCmdFind
SendKeys "{Tab}{Tab}{Up}", True

 

[Mike Krailo]

Using SendKeys is a bad idea. There is no way for us to even know what that is doing since you have not provided us with form or a sample of it. You should use a query to do what you want. Did you even look at the example I gave you. It does exactly what you want provided you have a continuous form.

 

[kitty77]

Yes, I did. I do not have a continuous form. Like I said, I was hoping to simply create vba version of the build in Access search.

 

[Mike Krailo]

Well kitty, I don't know what to tell you. The example I gave you does exactly what you asked for even if you switch it to Single Form View. You would then have to step through each record found in the search. If the search is over five different fields then you could have quite a few records to step through. I looked at the download views for the example database I provided and it say zero downloads FYI.

I get the feeling that you are not describing what you are looking for clearly enough. That little search box on the bottom of any form searches all fields in the form. The one I provided searches all the fields you specify in the query. So not sure how to help you any better than that.

 

[isladogs]

@kitty77
Did you look at the example by Allen Browne? See link in post #8

 

[kitty77]

Mike, been working with your suggestion. I guess I just did not understand it at first. Looks like it working for me now.
One question, when you enter a value that is not there, I get a blank record. Any way to get a message box instead saying nothing found or something other than a blank record?

Thanks again...

 

[kitty77]

Is that possible?

 

[Dreamweaver]

Is this something like your looking for

If so it's available here

Database Dreams

databasedreams.createaforum.com

 

[arnelgp]

so you changed to filtering instead of Finding.
why not.