Software

General discussion

Locked

Access 2000

By sivickers ·
I am trying to set up a search macro that will search a database form then show the required results. e.g. if I want to search for a name, it would search the database and then show all forms with that name. The question is How do I do it? I am stuck could somebody please help me.....

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

Access 2000

by Seagull Data Services In reply to Access 2000

If I read you correctly, macros won't do the job. What you need is a command button, a text box and a combo box. Put them in the form header.
In the combo box, list the fields you want to search on, e.g. LastName, Phone, etc.
Set the default value to the most frequent field searched. Call it cboField.

Call the text box txtFind
Display the text box's property sheet and find the AfterUpdate event. Click into the text entry area and click on the build button [...] which appears.
In the text box's AfterUpdate event complete the following code

Private Sub txtFind_AfterUpdate()
DoCmd.ApplyFilter "[" & Me.cboField & "] Like '" & Me.txtFind & "*'"
End Sub

Call the command button cmdShowAll
In the button's OnClick event procedurecomplete the following code:

Private Sub cmdShowAll_Click()
DoCmd.ShowAllRecords
End Sub

On the Debug Menu click Compile <databasename> There may be errors, check your typing and if that's not the problem, contact me through Peers.
Click the save button and on the file menu click Close and return to Micrsoft Access.

By entering text in txtFind and pressing enter you the form will filter out the records matching whatever is input.

Clicking the button will remove the filter and show all records.

Note that the * in the SQL statement in txtFind_AfterUpdate allows you to enter the first few letters of a name. For example typing the letter J will return all records with the selected field beginning with J, JO will bring up all Js with O as the second letter, etc. Also it's not case sensitive.
Hope this helps

Collapse -

Access 2000

by sivickers In reply to Access 2000

Poster rated this answer

Collapse -

Access 2000

by sivickers In reply to Access 2000

This question was closed by the author

Related Discussions

Related Forums