|
"Auto-complete" A Form, With or Without Code
By The Gunny
Select an item in a combo box and the rest of the form will automatically fill in the rest of the information for the record.
Tired of typing entries into every record when you know that the data is already saved in the database for another record? Set up the tables, queries and forms the right way, and data entry can be as quick and as simple as a few selections in combo boxes. We answered a question a while back that used a typical scenario for data entry, and we described the easiest ways to accomplish quick data entries. We've reproduced it here for you.
Question: Is there a way to code for auto-complete on a form? I have a table with a list of names, and I want to be able to have the name suggested as the first letter(s) are typed into a text box, then enter the name after hitting the return key. Ultimately, I want to display the values of a single row from this table along with other field values related to this row in separate text boxes.
For example: My table has a list of contractors, a vendor number for each contractor, and a phone number for each contractor. My form has separate text boxes for each of these values: contractor, vendor #, and phone #. As I enter the first letter in the contractor text box, I want it to suggest or auto-complete the contractor name and display the corresponding values (vendor # and phone #) in the other text boxes. The results also need to remain unique with each record.
Answer: There are several ways to do this, depending upon whether you want to use code or not. However, you may want to use a combo box that has been pre-filled with items for the user to select, instead of using a text box. This way, you may avoid having many different spellings for the same entries (near duplicates) in your tables.
For an example that uses code, open the Northwind sample database that comes with Access. Check out the "Orders" form, which allows the user to select a customer in the combo box and automatically fill in another part of the form and the subform with the selected customer's data.
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 we 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 inputting 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, 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 which has the same relationship and circumstances we'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:
- Create a new query and add tblInvoices and tblContractors to the query grid.
- Add all of the fields in tblInvoices to the grid.
- 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.)
- Sort by any field you'd like. If you sort by tblContractors.ContractorName, then the contractors will be sorted alphabetically.
- Save the query and name it qryInvoices.
- 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.
- 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."
- 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"
- On the "Data" tab, change the "Row Source" Property to tblContractors and make sure that the "Auto Expand" Property is set to Yes.
- 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 -- if you have the most common setting for <ENTER> key behavior.) 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.
You can read about your favorite expert contributors.
Copyright © 2004 - 2006 Q-Built Solutions. All rights reserved.
 Top
Visitors since 4 Dec. '05: 
|
