Need custom search ability

[ctieppo]

I support a database for some users who don't find the built in search function (ctl+f) to be useful enough.

The main data entry form of the database has fields for subdivision, lot number and address, any of which they may use to find the record they want. They're requesting that I add two types for searching:
* select sub and/or lot number as search criteria and have the selected record populate the screen
* type in a freeform address and have the record populate the screen

I'm debating about what the best approach would be to do this. Should I have a command button which opens a pop up form with the fields? If so, what is the code I will need to take the data selected/entered on the popup, run a query and then populate the underlying form? I think I need to pass parameters but I'm inexperienced at this so I need some guidance.

Also thinking that if I do create a popup form that I'll use it in the open event on the data entry form as well.

Thanks.

 

[Mile-O]

There are search examples in the Sample Database forum.

 

[godofhell]

I do this all the time, the best thing to do is create a Master form, use the Auto form generator and have the data come from a master query, then remove every field in the form that was created. Now copy the master query and paste it as Master2, create a new form using this query then select only the fields that you want to search by, set the properties to display as datasheet, no record selector, no navigation buttons, and default view as spread sheet. Save the forms, then add the Master Form 2 as a sub form to the Master Form. Now add the search fields to the Master Form and set the Name property on each. Write a procedure on each for After Update and enter the following line of code in the sub. Docmd.Runcommand.accmdrefresh
Now select the Master2 form and edit the query, with the Master form open, click on the query criteria field you want to search by, right click on it, select build, then from the popup box, select Opened Forms, select the Master form, then the control you created on form Master, the wizard will add the proper string pointing to the control so press ok. Now copy the string created and copy it to an empty colum of the Master2 query you currently have open. Paste it, then on the criteria, enter IS NOT NUll, this will allow records to only show when the search field is not empty. Close and save the query. Now save the form, open the form out side design mode, and try out the new control. If you will like to see a working database with this search fields email me at armando@kavalpewter.com

 

[ctieppo]

searching

Thanks for the suggestion. I looked at the sample databases and actually found one that was perfect for my users. Thanks!
CRT