Hi, Julian.
There are also a couple of other ways to accomplish the "auto-complete" on a
form, and while they don't require any coding on your part, you are required
to think about your database design and the design of your form.
One method uses a form and subform, each based upon a different table. The
subform would be bound to your table that contains the information about
your contractors. The main form would be bound to another table, such as
invoices, and would contain the foreign key field that links the two tables
together in the relationship between contractors and invoices that you've
previously defined.
The usage of subforms is so common that I won't go into the steps here, as
the information is readily available elsewhere.
Another method of "auto-complete" on a form uses an autolookup query.
Decide what you want your form to display and examine your database design
to see what it is modeling to determine whether this is something that you
can take advantage of.
Presumably, your form contains more information than just your contractor
information. Perhaps this form will be used for inputing information for an
invoice, where you would also need to record items such as the invoice
number, invoice date, customer ID, location, price, et cetera.
Presumably, you already have each of the contractors' names, vendor #'s and
phone #'s saved in a table, and you just want to display this previously
entered information while you add information about the invoice to a table
that contains invoice information. (Of course, displaying this contractor
information that has already been saved in a record in the database --
instead of typing this same information yet again -- is part of proper
relational database design. We want to avoid duplicating information,
because we don't want to have to make changes in multiple places.)
Presumably, you want to save this other information on the form in a second
table containing invoice information. (Contractors and invoices are two
different entities, so proper relational database design requires that
information about contractors be saved in one table and information about
invoices be saved in another table.)
Presumably, each of these invoices has only one contractor listed and any
one contractor can be listed on any number of invoices. (This is a
one-to-many relationship, with the contractor on the "one" side and the
invoices on the "many" side.)
That's a lot of presumptions, I know, but there are good reasons for these
presumptions. If all of these presumptions are correct (even if you aren't
using "invoices," as in this example, but something else your database is
modeling that has the same relationship and circumstances I've described),
then you can use the following example to create your "auto-complete" form
without any coding:
Here are the two tables and their attributes used for this example:
Table Name: tblContractors
Field Name Data Type
ContractorID Autonum (Primary key)
ContractorName Text
VendorNum Text
PhoneNum Text
Table Name: tblInvoices
Field Name Data Type
InvoiceNum Text (Primary key)
ContractorID Long (Foreign key)
InvoiceDate Date/Time
(For this example, make sure that there are already a few records in the
tblContractors table to be able to select.)
Follow these steps:
1.) Create a new query and add tblInvoices and tblContractors to the query
grid.
2.) Add all of the fields in tblInvoices to the grid.
3.) Next, add all of the fields from tblContractors, except ContractorID.
This field must come from the table on the "many" side of the relationship,
not the "one" side. (This is important for the autolookup query to work
correctly.)
4.) Sort by any field you'd like. If you sort by
tblContractors.ContractorName, then the contractors will be sorted
alphabetically.
5.) Save the query and name it qryInvoices.
6.) Create a new form. You can use the Form Wizard or you can do it
manually, but make sure that the form is bound to the qryInvoices query and
all fields in the query are placed on the form.
7.) Open your new form in design view and change the text box for
ContractorID into a combo box. To do this, select the ContractorID text box
and right click for the pop-up menu and select "Change to" then select
"Combo Box."
8.) Open the Properties dialog window for this new combo box. On the
"Format" tab, change the "Column Count" Property from 1 to 2 and change the
"Column Widths" to 0";1"
9.) On the "Data" tab, change the "Row Source" Property to tblContractors
and make sure that the "Auto Expand" Property is set to Yes.
10.) Save the new form.
To show the autolookup query in action, open the new form in "Form View."
Start typing the first few characters of the contractor name in the
"ContractorID" combo box. As soon as you see the name of the correct
contractor displayed, press the <TAB> key. (Don't press the <ENTER> key, as
this will save the current record and take you to the next record.) The
rest of the information for the contractor has been filled in automagixly
for the other text boxes in the form, and you can fill in the invoice
information yourself.
HTH,
Gunny
Coming soon:
For your Microsoft Access, database development and maintenance needs, see:
http://www.softomagixly.com