Frequently asked questions for self help.
Knowledge base article 129How to create a simple database search button
The Find button on the toolbar is a general purpose tool to search any table of form. You might want to make an even simpler version and put a button directly on the form itself. Here is how to do it.
Step 1 - create the button
- Enter Form Design Mode (see View menu)
- Select Insert/Macro button
- Right-click over button and select Properties
- Click on Caption and type in "Find" (or whatever text makes sense) in the Text box
- Click on Macros and the select Click in Events
- Click on New Macro button and name it dbFindRec and OK the dialog
Step 2 - add the code
- Go back to Database Manager and click on Macros
- Right-click on dbFindRec and select Edit
- Paste in the following code (replacing what is there):
strSearch = InputBox("Enter text to search for")
Set activeForm = DBForms(CurrentObjectName)
activeForm.Filter = "FieldName LIKE '" & strSearch & "*'"
If (activeForm.RecordCount = 0) Then
MsgBox "No matches found"
ElseIf (activeForm.RecordCount > 1) Then
MsgBox "Warning: more than one record found"
Make sure that you put in your own field name in place of "FieldName" in the above filter statement.
The above implements a soft search on a text field - entering "Sm" will find Smith and Smithson. The following would change it to exact match:
activeForm.Filter = "FieldName = '" & strSearch & "'"
And the following to search numeric fields:
activeForm.Filter = "FieldName = " & strSearch