Auto-fill data in Table

[Nora]

I have a table consist of clients detail.
Another table (main; data-entry) store all products, client, cost details &
etc.,.
I would like to get the experts' advise on how could I create a column
(field) of AUTO-FILL data of my clients' names when I just need to key-in the
"Client ID" (number) on the that same column.
OR another way where I could just select the "Client ID" from a column
(currently exist) by itself and the next column automatically display the
client's name.

 

[Duane Hookom]

In properly normalized tables, you would only store the clients' names in one
place. You can always display the client names by joining to the table
containing the name.

 

[Ken Sheridan]

In your main table, which for this example I'll call MainTable, you'd have a
Client ID column as a 'foreign key', and no columns for the client's names as
Duane has said. For data entry in a form bound to this table (never enter
data directly in a table's datasheet, always via a form) you'd normally use a
combo box for the Client ID set up like this:

ControlSource: Client ID

RowSource: SELECT [Client ID], [FirstName] & " " & [LastName] FROM
[Clients] 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.

Where the table containing the client details, with one row per client, is
called Clients and includes columns Client ID (the primary key), FirstName,
LastName etc.

The user then simply selects a client from the alphabetically ordered list,
and their full name shows in the control, but its underlying value, and
therefore the value in the table, is the hidden Client ID.

If you really want to enter the Client ID rather than select by name, set
the combo box up as above but change its RowSource to:

SELECT [Client ID], [FirstName] & " " & [LastName] FROM [Clients] ORDER BY
[Client ID];

and change the ColumnWidths property to: 8cm;0cm. This will now show the
numbers, not the names. Then add a text box with a ControlSource property of:

=[Client ID].Column(1)

The Column property is zero based, so Column(1) is the second column, the
client's name, which will show in the unbound text box when you select or
type in a Client ID into the combo box.

Another approach would be to have a clients form, based on the Clients
table, and within it a subform, based on the MainTable, linking the subform
to the parent form on Client ID. As you navigate to each client in the main
form the subform will show only those rows for that client, and you can add
new ones in the subform. The subform need not include a control bound to the
Client ID column as the correct value is automatically inserted into the
underlying field via the linking mechanism.

BTW, whatever you do, don't be tempted to use the 'Lookup Wizard' data
type selection for the Client ID column in the main table in table design
view. For reasons why see:


http://www.mvps.org/access/lookupfields.htm


Ken Sheridan
Stafford, England