2 Field Keyword Search

[Emma35]

Hi All,
I'm attaching a database i'm having a little trouble with. Basically i want to have a keyword search on the form frm_BOSSearch but i can only get 1 search to work. I can search by name but when i try to add a search to another field the whole thing stops working. Anyone any ideas how i'd do this ?. I think i need to change the code in the query but not sure how to do it. I'd appreciate any help

Thanks a lot
Emma

[ranman256]

Have a continuous form with all records showing.
Enter data into the 2 boxes (or 1 search box)
then click the button.

sub btnFind_Click()
dim sWhere

if not ISNull(txtFind1) then sWhere = "[field1]='" & txtFind1 & "'"
if not ISNull(txtFind2) then 
   if sWhere <> "" then sWhere = sWhere & " and "
   sWhere = "[field2]='" & txtFind2 & "'"
endif

me.filter = sWhere
me.filterON = true
end sub

[Emma35]

Thanks Ranman...though i'm not sure how to make this work with what i already have....i'm using a ListBox in the example attached. The results change as i type. If i use your method can i then dispense with the query altogether ?

[cyanidem]

You don't need to use separate text fields for other search criteria. Use first one only and change your query to something like this:


You can add it to as many fields as you want but every next one will slow it down. Also if you'd like to search numeric fields as well you'll need to convert strings to numbers etc.

[Emma35]

Thank you......i think that's the way i had it originally and it works fine up to a point. However, say i do a search by typing in a persons name and end up with a hundred records, how could i then narrow it down further ?

[cyanidem]

Well, if you want separate searches for name/position/whatever then yes - use different search boxes as you do now and use references to them in your search query. Remember to put them in separate rows (as above) so they're OR not AND.

[Emma35]

That's what i tried to do in the database attached but none of the search boxes work when i do it....there's some code in the OnChange event of the search boxes which might be the problem.
(I got this code from someone else as i'm hopeless with VBA). I'm overlooking something when i'm adding the extra search boxes

[cyanidem]

Code for search boxes looks OK and it should work if you set your query like this:

[Emma35]

I set it up exactly as you suggested and none of the search boxes are working....not sure what i'm doing wrong

[cyanidem]

Ok, two things:
1. I was wrong, your search criteria should be set as ...AND... for what you want to achieve, so put them back in one row please
2. I just opened your test database and nothing worked, I added button with simple msgbox and it didn't worked as well. Turned out database opened in disabled mode. To be precise it switched off all "dangerous" content such as vba events code. I had to switch it back on in "File" menu from ribbon and all works properly.
Try it please and let me know if it works. You may want to add database file to trusted folders or something similar in options.

[Emma35]

Yes it works perfectly now....thank you very much for taking the time to help me. I appreciate it very much

Emma x

[cyanidem]

Happy to help