Advanced Search in Form

[agehoops]

I've got some code allowing me to search through any given field, which works fine. HOWEVER, what i am aiming for is the following:

A search field where you can enter the criteria, and it goes off, looks for any matches or LIKE matches, and returns multiple results into a popup box, allowing the user to select the result they want, which closes the popup box and loads that record. Is this at all possible? If so, how so?

Thanks very much

 

[edtab]

One possible approach:

- Create a query using the table where you want to do record searches. In the criteria of the field you want to search on,
enter the name of the text box from your form plus the wild character "*" (i.e. forms!formName!textboxName & "*").
I believe this will bring up exact matches or like matches.

- Create a listbox on a pop-up form and use the above query as the recordsource. Bind the field you are interested in as your search field.

- Create a report whose recordsource criteria = listbox value

- In the On Click event of your listbox, open the report you have created which should contain the record selected from the listbox.

In a nutshell, that is what you need to do. Hope this helps you.

 

[gemma-the-husky]

Here's a different idea

you can search for info you want with the binoculars but they are fiddly to use,

so, offer your user a search string text box (to emulate the binoculars.)

after he enters the search string, he clicks a find first or find next button. In the btnclick event you set the focus to the field you want to search, and then search for a matching string by code - one line of code - see below

you have a find first, find next button, and if you have a bound form (single item or continuous), it will automatically place you on the correct record.

if you want to search say, a whole name (first name and last name) you can concatenate these in your query, put this in a text box on your form, and then search the concatenated box, as long as you search as formatted.

Its really quick on anything other than massive files.


txtsearch is your searchstring textbox for the search
only difference is finffirst searches all records (acsearchall) and findnext just carries on (acsearchdown). Check out the otyher parameters

FIND FIRST
searchfield.setfocus
DoCmd.FindRecord txtSearch, acAnywhere, False, acSearchAll, False, acCurrent, True

FIND NEXT
searchfield.setfocus
DoCmd.FindRecord txtSearch, acAnywhere, False, acDown, False, acCurrent, False

 

[agehoops]

Ok, i've got it partially working now. Edtab's idea was the exact sort of thing i was looking for, except for a couple of differences.

What i've got now is the search box, with a button. When the user clicks the button, it checks if any search string has been entered, and if not throws back an error. If there has, it pops up the results form, with the list box, but what i want it to do now, is when i click on a result, i want it to close the form, and make the main form jump to that record. The results are showing the staff ID, name and surname, and i want it to make the main form jump to the selected result with Staff ID. How do i do this??

Thanks

 

[agehoops]

Ok no worries, got it working now. Thanks for your help though you 2

 

[agehoops]

Ok, i know i probably look stupid replying yet again, however, the solution i have, although working, has a slight problem. I have been able to go to the record based on the selection from the list box, however, what it is actually doing is simply filtering all of the records to just the one, so i am then unable to scroll through records unless i remove this filter. Is there a way of simply going to the record from the selection instead of a filter?

 

[gemma-the-husky]

my solution didn't filter your data - it just found the first (or next) matching record, and positioned your form AT THAT RECORD

 

[agehoops]

Thanks for the response. Is there a way of putting that code into the selection of the list box. I really would prefer to have it as a pop up to actually show a list of the results, rather than clicking through them. I already have the list funtioning, i just need the code.

 

[gemma-the-husky]

just write a query, with a parameter equal to the target field on your form,and use THAT query to drive your listbox

In the after update event of the target field, put listbox.requery

that will repopulate the listbox, by re-running the query with the correct parameter.

Is that what you mean?

 

[agehoops]

Not quite no, i've got the list box to display all the information i need, i just need to get it now so that when i select the result from the list box, it goes to the main form and jumps to that record, instead of filtering all the records down. I was just wondering if there was any code that would make it jump to the record selected from this list box.

 

[gemma-the-husky]

in the code that fires the popup box, use the id of the selected row to set a global variable. Make the code wait until the popup box is closed.

Have a textbox on your main form showing the id (enabled, locked). After the popup is closed you can then use my findfirst idea, by setting focus in the id field, and then running the findfirst code.

You make the code wait by opening the popup as acdialog, although there are other techniques.

You don't really need to open another form though, you could do all this with a combo or listbox on your main form. It depends what "look and feel" you want

 

[agehoops]

ok, i think i'm being thick here. I really don't understand all the record commands so i'm getting confused. Which command should i be using in order to take the ID from the selection on the list, and using it to go to that record in the main form? Sorry i know you've probably explained loads.

 

[agehoops]

oo wait i've figured it out and it's working properly! Thanks for all of your help, you've been great. THANK YOU! :-D