A Friendly Advanced Search Utility By Louis H. Knight Knight-at-Frogpond Database Solutions

1. A Friendly Advanced Search Utility By Louis H. Knight Knight-at-Frogpond Database Solutions KEGS Access SIG October 2001

2. A FRIENDLY SEARCH UTILITY PROVIDES • Flexibility and Ease of Use • Search on any field of data tables. • Wide variety of ways the data can be searched. • User chooses how to search data including “like” criteria. • Search using multiple criteria.

3. A FRIENDLY SEARCH UTILITY PROVIDES • Search using ranges or multiple ranges of data. • Display list of hits • Drill down for details on hits • Display number of search hits. •  Save Searches for future re-use.

4. LIMITATIONS • Does not provide OR criteria searches. • Does not provide combination AND OR criteria searches

5. REFERENCE Altinisik, Hakan, 2000, A User Friendly Search Utility: Smart Access Newsletter, Pinnacle Publishing, Inc., Roswell, GA, August 2000. You can obtain a free subscription to Smart Access for 90 days and download the article and sample database. http://www.smartaccessnewsletter.com

6. Original User Friendly Search Example Used alone this returns number of hits only Select Table to Search Select Saved Searches

7. Original User Friendly Search Example Launch search from main form to display records Launches main search form

8. Original User Friendly Search Example Choose criteria

9. Original User Friendly Search Example Search result

10. MAJOR COMPONENTS • Tables • tblMySearches – holds saved search criteria • tlkpTables – table names to be available for search • Modules • basSearch - applies the selected search to the form's record source as a filter. • Forms • The main Search form with tabs for each table to be searched. • A modified form for displaying hits for each table

11. EXTENDING THE SEARCH UTILITY • A continuous form for displaying hits for each table from which to drill down • A report for each table that shows various fields for each hit.

12. FURTHER EXTENDING THE SEARCH UTILITY Extend to multiple tables Use a temporary table generated by make table query. This would allow use of search criteria on the many side of one to many relationships. OR criteria Could be added by programmatically changing the AND in the SQL statement buildup within basSearch module. An option button could be added to search form for choosing between AND and OR.

13. STEPS TO MODIFYING FOR YOUR APPLICATION • Copy basSearch to a new module – this needs no modification

14. STEPS FOR MODIFYING (Continued) Copy tlkupTables – modify TableNames and References for your application Choose a brief reference that is not a reserved word for Access for each table. Original Customized example

15. STEPS FOR MODIFYING(Continued) Copy tblMySearches – Copy from sample application, delete all but first record of existing records

16. STEPS FOR MODIFYING(Continued) Construct main search form – This contains a tab control with one form for each main table per tab. Tab style is set to none to display only one form at a time. Change style to “Tabs”. Tab style set to “none”

17. STEPS FOR MODIFYING(Continued) Copy the main search form frmSearch from the sample application. You will reuse all of the code behind the form without changes. In design view select the tab control and change style to “tabs” so you can see the various forms. Rename each tab for one of your data tables, such as Companies, Projects, Contacts. Layout unbound textboxes/combo boxes for each field you want to search on each table, one table per tab.

18. STEPS FOR MODIFYING(Continued) Set properties for each textbox using conventions like: Textbox names will have a prefix “txt” and a suffix that is the reference value from the table tlkpTables corresponding to the specific table. For example, fields on the form for the Customers table would have a form like txtCompanyNameCus.

19. STEPS FOR MODIFYING(Continued) Here are the settings for each control type.

20. STEPS FOR MODIFYING(Continued) Specify the Tag property for each control using the corresponding field name in the associated table. In this example it would be “CompanyName”. Then reset tab control style to “none” and save. Specify Tag property for each unbound control

21. STEPS FOR MODIFYING(Continued) Create a form for each of the tables you wish to search. And include several major fields. This should be constructed in continuous mode with a header. It will be used to drill down to detailed records. Copy the controls indicated below from any of the main forms in the sample application and paste onto the header of each new form. Also copy all of the code behind the sample form and paste into the code module for each of your new forms. Copy these controls, cboSearchesFrm, cmdShowall, cmdSearch

22. STEPS FOR MODIFYING(Continued) Paste the controls onto header and code into form module Relink controls to appropriate event properties on form module.

23. STEPS FOR MODIFYING(Continued) • To drill down on found records add code like the following to the form’s On Double Click event (example is for ProjectFind form) • Private Sub Form_DblClick(Cancel As Integer) • On Error GoTo Err_Form_DblClick • DoCmd.OpenForm "ProjectFind", , , "ProjectKey=" & Me!ProjectKey • Exit Sub • Err_Form_DblClick: • MsgBox Err.Description • Resume • End Sub • This will launch the ProjectFInd form opened to the chosen record.

24. Choose record to display details Double click to drill down for details

25. Check out the Friendly Search form utility article at Smart Access and make your own version. http://www.smartaccessnewsletter.com The result will be a versatile, easy to use search form for your database application.

26. THAT’S ALL FOLKS! • -The Prince- • Knight-at-Frogpond Database Solutions • 13009 129th Ave, NE • Kirkland, WA 98034 • (425) 820-2038 • FAX (810) 963-5387 • E-Mail  louk@knight-at-frogpond.com Exit