How can I set one field to return data into another field?

[Terri]

OK here goes, hope this is clear

I am wanting to set up a database where info is selected in one field and
then multiple fields are populated. EG entering username from drop down list
automatically fills in Name, Dept and Ext so save looking all those up each
time.

In excel I would use the lookup function and put formulae in each cell.
Is this possible in access? Any help greatly appreciated.

 

[KARL DEWEY]

No need to populate all those fields in a second table. Just enter EmployeeID
using a combo sourced from Employee table and join the Employee table in your
query that feeds the form.

 

[John W. Vinson]

OK here goes, hope this is clear

I am wanting to set up a database where info is selected in one field and
then multiple fields are populated. EG entering username from drop down list
automatically fills in Name, Dept and Ext so save looking all those up each
time.

In excel I would use the lookup function and put formulae in each cell.
Is this possible in access? Any help greatly appreciated.

The name, dept and ext fields should simply NOT EXIST in your second table.

Relational databases use the "Grandmother's Pantry Principle": "A place - ONE
place! - for everything, everything in its place". These fields should exist
only in the Personnel table (btw I'd use FirstName and LastName rather than
just Name); you can use Queries to link other tables to the personnel table by
EG, and you can use additional textboxes on a Form referencing a combo box.
For instance, if the Dept is column 3 in a multicolumn combo box (even if some
of the columns aren't visible) you can put a textbox on the Form with a
control source

=comboboxname.Column(2)

The column property is zero based so this will show the third column.

If you're working in Tables with Lookup fields... well, don't:

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

 

[Terri]

Thanks for your replies, I have followed what you have both said for me to do
and have the form now working BUT the data is duplicated on each record ie we
change record 3 and records 1 and 2 change to match record 3, have I missed
something on one of the settings. Thanks again for your help.

 

[KARL DEWEY]

It appears you did not bind the combo to a field.
Row Source is where the combo pull data from and Control Source is where it
is bound to store the data.
Bound Column is the field of Row Source that you store.

 

[Terri]

Thanks very much, that has now solved the problem for me - I was missing
changing the bound column selection. Thanks again for the replies.