Autolook up

N

Neil Greenough

In a form / table, I have a field which is a lookup field searching for
people's Employee number. This is in the header of my form.

Now, in the detail part of my form, I have two fields - surname and first
name. These are located in a table along with the corresponding employee
number.

What I would like is for when I select an employee number from the search
field at the top of my form, the surname and first name automatically fill
in with the corresponding information.

Any ideas how I can do this?
 
D

DevalilaJohn

You have several options depending on how your system is set up.

If you are doing the look up for employee number from a dropdown which is
query based, simply include the name fields in the query and apply them when
the employee ID is selected.

If you are going directly against a table that has id and name, positioning
to the record will fill in the fields automatically.

If the id and name fields are in different tables, try setting up a
relationship between the tables and then adding a subform that will populate
automatically.

There are a bunch of other things you can do, but hopefully the above will
cover your needs.
 
J

John Vinson

In a form / table, I have a field which is a lookup field searching for
people's Employee number. This is in the header of my form.

Now, in the detail part of my form, I have two fields - surname and first
name. These are located in a table along with the corresponding employee
number.

What I would like is for when I select an employee number from the search
field at the top of my form, the surname and first name automatically fill
in with the corresponding information.

Any ideas how I can do this?
Click to expand...

Several suggestions here:

- If this is defined as a Lookup Field in your *table*, read the
critique of this (mis)feature at
http://www.mvps.org/access/lookupfields.htm. Most developers shun the
Lookup Field type, while gladly using Combo Boxes ("lookups") on
forms.

- If you are trying to copy the first and last names from the employee
table into some other table (the table upon which this form is based),
DON'T. Storing the employee's name redundantly is neither necessary
nor desirable - it wastes space, and risks data corruption; if an
employee's name changes, you now have two (or more) places to track
down and change the name. Store the name ONCE in the employee table,
and use queries or the suggestion below to display it.

- If you just want to *see* the first name and surname on your form
(which I would recommend), include them in the Query you are using as
the Row Source for the combo box on the form. You can set the combo's
ColumnWidths property to have these fields of zero width. Then on your
form you can put

=cboEmployeeID.Column(n)

in the Control Source of a textbox to pull the appropriate *zero
based* column from the combo - that is, if FirstName is in the third
field in the query, use Column(2).

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Top