First, you can right click on a field and choose Filter by
Selection. Below I describe a button for removing the
filter, or you can do so from the toolbar. It depends on
who will be using the database.
With much help from this group, I came up with a form-
based solution to a similar problem (assuming I understand
you correctly). I used unbound combo boxes for the search
rather than using cotrols that are bound to a source in
the table or query. For instance, I placed in the form
header an unbound combo box (cboLookupSubject) to look up
the value in the field [Subject] in the form Detail
section. In the combo box's After Update event I placed
the following code:
DoCmd.ApplyFilter , "[Subject]='" & cboLookupSubject & "'"
[Subject] is a text field; I believe the code for a number
field would be:
DoCmd.ApplyFilter , "[Subject]= " & cboLookupSubject
Do the same for the rest of your fields. I put the combo
boxes in the header, and made the header invisible. A
command button on the form makes the header visible when I
want to conduct a search.
The row source for the combo box is a query. With the
combo box selected in design view, right click and select
Properties. This opens what is known as the Property
Sheet. Click the Data tab, and click Row Source. Click
the three dots on the right side of the row. Select the
table from the list, click Add, then Close. Double click
Subject (to use my example) in the top part of the
window. In the grid, click Sort and choose ascending. If
you have the same words in [Subject] for many records,
right click the top part (next to, not in, the table),
click Properties, and set Unique Values to Yes. Close the
window, and save when prompted. You can click Save and
give the query a name, if you like.
Similarly, when I say that code needs to go in the After
Update event, select the combo box and open the property
sheet. At the event tab, click After Update, click the
three dots, click Code Builder, then OK. Insert the code
at the blinking cursor.
You might want to put a button on the form, with its Click
event set to: Me.FilterOn = False. This will let you see
all of the records again after you have filtered them.
The combo box wizard might help. Make sure the magic wand
in the toolbox is highlighted, then click combo box, then
click the form and follow the prompts.
