Transcript Document

A Friendly Advanced Search Utility
By
Louis H. Knight
Knight-at-Frogpond Database Solutions
KEGS Access SIG October 2001
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.
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.
LIMITATIONS
• Does not provide OR criteria searches.
• Does not provide combination AND OR criteria searches
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
Original User Friendly Search Example
Used alone this returns number of hits only
Select Table to
Search
Select Saved Searches
Original User Friendly Search Example
Launch search from main form to display records
Launches main search
form
Original User Friendly Search Example
Choose criteria
Original User Friendly Search Example
Search result
MAJOR COMPONENTS
Tables
1.
tblMySearches – holds saved search criteria
2.
tlkpTables – table names to be available for search
Modules
1.
basSearch - applies the selected search to the form's record source as a
filter.
Forms
1.
The main Search form with tabs for each table to be searched.
2.
A modified form for displaying hits for each table
EXTENDING THE SEARCH UTILITY
1.
A continuous form for displaying hits for each table from which to drill
down
2.
A report for each table that shows various fields for each hit.
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.
STEPS TO MODIFYING FOR YOUR APPLICATION
Copy basSearch to a new module – this needs no modification
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
STEPS FOR MODIFYING(Continued)
Copy tblMySearches – Copy from sample application, delete all but first record
of existing records
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”
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.
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.
STEPS FOR MODIFYING(Continued)
Here are the settings for each control type.
Field
Content
Control
Combo box
Control
name Prefix
Decimal
Places
Property
Format
Property
all
cbo
Fixed
0
Text
txt
blank
auto
Date
From and To
Short Date
auto
Numeric
From and To
Fixed
0
Money
From and To
Standard
2
Yes or no
chk
none
none
Textboxes
Checkbox
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
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
STEPS FOR MODIFYING(Continued)
Paste the controls onto header and code into form module
Relink controls to appropriate event properties on form module.
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.
Choose record to display details
Double click to
drill down for
details
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.
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 [email protected]
Exit