Have you considered using combo boxes on a form for entering or selecting the
search criteria? Lets say for instance that you want to be able to search by
Company, Contact or City, or any combination of these. Create an unbound
form, frm PhoneSearch say, with three combo boxes, cboCompany, cboContact and
cboCity say. The RowSource property for cboCity would be along the lines of:
SELECT Company FROM Companies ORDER BY Company;
where Company is the name of the column and Companies the name of the table.
The other combo boxes would have similar RowSource properties to list
contacts and cities from Contacts and Cities tables. In query design view
the criterion for the Company column would be:
Forms!frmPhoneSearch!cboCompany OR Forms!frmPhoneSearch!cboCompany IS NULL
Testing for OR…..IS NULL makes it optional to select an item in the combo
box, so it can be left blank, i.e. Null.
Remember that if you have any spaces or other special characters in object
names you must wrap them in brackets [like this].
The Contacts and City columns would have similar criteria referencing the
other combo boxes. You'll find that after saving the query, if you open it
again in design view Access will have rearranged things quite a lot, but it
will work just the same. Its as well to test the query before saving it by
opening the form and making some selections and then switching the query to
datasheet view to see if its delivering the correct results. Making any
amendments after Access has rearranged things can be tricky.
Back in the form in design view add a command button with code in its Click
event procedure to open a form or report based on the query. This is better
than opening the query directly as you can format the form or report much
better. The code to open a form frmPhoneList based on the query would be:
DoCmd.OpenForm "frmPhoneList"
Provided that each combo box's AutoExpand property is True (Yes in the
properties sheet) a user can either select an item from the drop down list or
type in characters, in which case the first match will be found as each
character is typed. This means the user does not have to know the names of
the companies, contacts or cities as they are listed for them to select from.
A user can enter/select items from one or more of the combo boxes, or even
from none, in which case all rows would be listed. So they could select the
company ACME Widgets and get all the numbers for that company, or you could
select ACME Widgets and contact John Doe, which would list the number for
John Doe from that company, but not any John Does from other companies.
Similarly the results could be restricted to a particular city.
You can of course include as many combo boxes on the form as you wish, and
use them in any combination. Consequently you have a very flexible search
interface all contained in one simple form.
You'd probably want to be able to clear the combo boxes with a single button
click, for which button's Click event procedures the code would be:
ME.cboCompany = Null
Me.cboContact = Null
Me.cboCity = Null
Ken Sheridan
Stafford, England
