Ed:
You can build a search form yourself quite easily with the need for only one
line of VBA, or you can even avoid writing the minimal code necessary by
getting the wizard to do it for you. Before dealing with that however, have
you thought about using a combo box or list box on the form rather than
separate text boxes. With a combo or list box the users don't need to guess
at a contact's name, just select one from the list. Whether you use a combo
or list box things are set up in exactly the same way.
First create an unbound dialogue form, frmSearchContacts say, and add a
combo or list box; I'll assume the former for now and call it cboContacts (in
the control's properties sheet change its Name property to that). I don't
have the Contacts database in front of me so I'll assume it has a table
called Contacts with columns ContactID (its primary key), FirstName and
LastName. The combo or list box would return the ContactID and the name in
the format 'Ken Sheridan', ordering the list by last name and then first
name. The list would hide the ContactID column so just the names are visible.
To do this the properties of the combo or list box are set up as follows:
RowSource: SELECT ContactID, FirstName & " " & LastName FROM Contacts
ORDER BY LastName, FirstName;
BoundColum: 1
ColumnCount: 2
ColumnWidths 0cm;8cm
If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.
The search form is going to open a form, which I'll call frmContact_Single,
to show the selected contact (I'll come back to the form in a moment). This
can be done in one of two ways; (1) by opening the form as soon as a
selection is made from the combo box or list box, (2) by clicking a separate
button on the search form. If you use a button you can get the button wizard
to write the code for you (you'll need to create the frmContact_Single form
first (see below). Normally a button would be used with a list box or if
there is more than one combo box, but selecting from a single combo box would
trigger the opening of the form immediately.
If you want it to open as soon as you select the contact in the combo box
then you'll need to put the code in the combo box's AfterUpdate event
procedure. Its only one line, though. To do this select the combo box in
design view, select the After Update event property in its properties sheet
and click on the 'build' button (the one to the right with 3 dots). Select
'Code Builder' in the next dialogue and click 'OK'. The VBA window will open
at the control's AfterUpdate event procedure with two lines in place like so:
Private Sub cboContacts_AfterUpdate()
End Sub
Just add a line between the two:
Private Sub cboContacts_AfterUpdate()
DoCmd.OpenForm "frmContact_Single"
End Sub
Before creating the frmContact_Single form you'll need to create a query to
base it on. So, create a new query based on the Contacts table and add all
its columns to the query design grid. In the 'criteria' row of the ContactID
column enter a reference to the combo box as a parameter like so (all on one
line)
Forms!frmSearchContacts!cboContacts OR Forms!frmSearchContacts!cboContacts
IS NULL
The reason for testing for NULL is that if a user leaves the combo or list
box blank (NULL) the query will return all rows from the table not just the
selected contact. The reason for including this is that you might want to
include other combo boxes on your search form and give the user the
opportunity to select from any of them, either singly or in combination.
Testing for NULL in the query in effect makes selection from a control
optional. If you do add other combo boxes, e.g. you might add ones for
Company or City or whatever is appropriate, you build them in exactly the
same way as for contacts' names, and add criteria to the relevant columns in
the query in the same way.
Save the query under a suitable name of your choice. BTW if you open the
query again in design view you'll find that Access has moved things around.
It will work jut the same, but it can make it a bit tricky to add additional
criteria in other columns if you want to reference other controls on the
form, so if you are intending to have multiply search controls on the form
its easier to set everything up from the start and design the query in one go
(its actually a lot easier to add more criteria if a query is written and
saved in SQL view, but I don't imagine you want to go down that road just
yet!).
Now create a form based on the query you've just made. You'll probably find
there is already a contacts form in the database, so you can make use of
that. Open it in design view and using 'Save As' on the 'File' menu save it
as frmContact_Single. Do this before making any changes to the form's
design; otherwise its all too easy to inadvertently change the original form
not the new 'saved as' version of it. Still in design view change its
RecordSource property to the name of the query you created above. Save the
form again, this time with the normal 'Save' option rather than 'Save As'.
That's it. It might sound long-winded, but its probably taken me longer to
describe it than it would to do it. To search for a contact open
frmSearchContacts, select from the list and the frmContact_Single form will
open at the selected contact. If you want to be able to print out the
contact details you could design a simple report based on the query you
created (the report wizard will do most of it for you), and add another
button either to your search form or to frmContact_Single to print the report
(again the button wizard will write the code for you).
If you are willing to dip your toes in the murky waters of VBA coding then
you can enhance the functionality of your form by enabling multiple
selections to be made in a list box so more than one contact, company, city
etc can be selected simultaneously and returned in a form or report. This
does require some VBA, but its pretty standard stuff, and I can give you the
code straight from my 'tool kit'. Let me know if you want to try it; you'll
find that writing code is a lot less scary than you might think.
Ken Sheridan
Stafford, England
