Autofill of data

K

KLR

I am filling in data in a Table (can also use a Form).

The data (Name) in Field 1 is selected from a drop down list. I then
want to populate Fields 2, 3 and 4 with Phone, Email and Postcode which
are attached to Name in a separate table.

How do I do this?
 
R

Rick B

You don't. That would be redundant to save the data in two tables. If the
phone number changes, do you really want to update two tables?

The proper way to handle this is to store all that personal data in a
separate table as you have done. Then, you need to have a common field
between the two tables (such as name) that links the two tables. Once the
link is created, you can simply include both tables in your reports,
queries, and forms and you will be able to pull fields from each table.

A few comments, I would add a unique field such as "vendor number" or
"employee number" or "customer number". I would not use "name" as a key
field. Secondly, I would not store the entire name in one field. I would
have a FirstName field, a MiddleInitial field, a LastName field, a Title
field, etc. If you don't how will you format the name for various
applications? What if you want to put a person's full name at the top of a
letter, but Only put "Mr. Jones" as your salutation? What if you want to
create a report and sort it by last name?

For more help, you can look at just about any of the templates that ship
with Access. They almost all have "people" tables and related "item"
tables.

Hope that helps.
 
W

Wayne Morgan

First, don't! Use a form, don't enter data directly into the table. The
table is just there to store the data and shouldn't be used directly for
routine operations.

Next, you shouldn't store all of the information you mention in the table
for the form. Instead, you may display the information from the table with
the name, phone number, etc., but that should be display only. What should
be stored is the unique record ID (primary key) for that record in the
person information table. This will be the "foreign key" in the table that
the form is based on. You would link these two table together on that common
field so that you can get the associated information when you need it.

To display all of this on the form, you will need 5 columns in the combo
box.

1) RecordID Field
2) Name Field
3) Phone Field
4) Postal Code Field
5) Email Field

The RecordID field should be the Bound Column. The first visible column will
be what is displayed in the combo box after you make your selection, but the
other columns can be displayed in the combo box's drop down list if you
wish. Set the Column Widths property as desired to hide the columns you
don't want displayed by setting the width of that column to zero. Change the
order of the columns by changing the order they're listed (left to right) in
the query used as the Row Source for the combo box. To display the other
columns on the form (since only the first visible one will display in the
combo), place 3 more textboxes on the form and set their Control Sources to
point to the other columns. This will make them "calculated textboxes", so
they won't be editable.

Example Control Source:
=cboMyCombo.Column(2)

The column index number is zero based, so 0 is the first column, 1 is the
second, 2 is the third, and 3 is the fourth. However, the Bound Column
property is one based, so 1 is the first column in that property.

Please see the information from this link, especially item number 2:
http://www.mvps.org/access/tencommandments.htm
 
Top