Frequently asked questions for self help.

Knowledge base article 129

How 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

  1. Enter Form Design Mode (see View menu)
  2. Select Insert/Macro button
  3. Right-click over button and select Properties
  4. Click on Caption and type in "Find" (or whatever text makes sense) in the Text box
  5. Click on Macros and the select Click in Events
  6. Click on New Macro button and name it dbFindRec and OK the dialog

Step 2 - add the code

  1. Go back to Database Manager and click on Macros
  2. Right-click on dbFindRec and select Edit
  3. Paste in the following code (replacing what is there):

Sub dbSearch()
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"
End If
End Sub


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