Autofill from lookup

I have a problem tracking application in Access 97 that the  employee id field is going to a lookup table and is filling in that field.  However I do have other corresponding  fields in both tables like name ext dept etc that I would also like to be autofilled from that look up table after you've selected the correct employee id.   How can I do this.  I've done alot of database work in Paradox and you just say fill all corresponding fields but it's not that easy in Access.            Please Help!!!
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Autofill is a feature of MS Access. To make it work:

1) Define the One-to-many relationship between the tables using the Relationships window.
2) Include both tables in the underlying query of the form, dragging onto the query grid the fields needed from either table.
3) Use the joining field from the *many* side of the relationship on the form, which should be included in the step above.

The way autofill works is that as soon as the employee_id value is entered, the related values will be looked up and filled in on the form.

Naturally employee_id is not 'typed-in', but would be selected from a combo-box which:
1) displays the employee name.
2) has the employee_id as the bound column, and
3) the combo-box itself has the ControlSource (i.e. is bound to) the employee_id field from the Many side in the underlying query.

Construction of a suitable combo-box is easiest done using the wizard, which automatically includes the 'id' field and makes it the bound column.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
fuerstmAuthor Commented:
I need more clarification on steps 2 and 3.
fuerstmAuthor Commented:
I need more clarification on step 2 and 3.
This is for Ian Hinson
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Firstly, the proper name of this facility in MS Access is 'AutoLookup' - I incorrectly referred to it as Autofill before.

Assume that:
- we're working with 2 tables called "Employees" and "Sales" (read "Sales" as whatever employee-related table you are using in your case.)
- [Employee_ID] is the Primary Key of Employees, and is an autonumber field
- "Sales" has a *long integer* field called [Employee_ID] for each sales record.

I would like to go over Step 1, to re-check that part
The Relationships window is opened by selecting 'Relationships' from the 'Tools' menu.
This should not be confused with top portion of the Query grid, which can define relationships only for the purpose/duration of the query.
In the Relationships window you:
1) Add the Employees and Sales tables to the window using the 'Show Table' button or by selecting 'Show Table..' from the 'Relationships' menu.
2) You click on the Employee_ID field in the "Employees" table, and *drag* it across to the corresponding field in the "Sales" table.
3) The Relationships dialog appears with "Employees" heading the left column and "Sales' heading right column.
4) Click on the checkbox labelled 'Enforce Referential Integrity' to enforce the One-to-Many relationship.

Step 2
The form you will be using will be based on a query that includes fields from both tables. This step contructs that query.
0) Close the Relationships window if you haven't already.
1) Click on the 'Queries' tab of the Database window.
2) Click on the 'New' button.
3) Select Design View from the listbox presented when creating the query.
4) Using the Show Table dialog, add (only) the Employees and Sales tables to the Query Grid, then close the Show Table dialog.
CHECKPOINT: Does a relationship line *automatically* appear between the Employees table and Sales table in which a figure 'one' appears near the Employees tables and the 'infinity' symbol appears at the Sales table end of the join-line?
If NOT then Step 1 has not been done properly, and you can't continue until it is fixed.
5) Drag the fields needed from the Sales table in the top portion of the Query Grid down onto the grid in the lower portion of the Query Grid.
5*) Ensure you include the [Employee_id] field from the Sales table because this field is on the Many side of the relationship and must be included in the query output fields.
6) Drag fields from the Employees table down onto the grid e.g. [Dept] etc.
6*) Do not drag the Employees.Employee_ID field onto the grid because it is not needed and may cause ambiguity with the Sales.Employee_ID field.
7) When all the fields that you want to display on your form have been added to the query, close the query and save it.

I have assumed the query developed in Step 2 is saved as "Employee_Sales"

Step 3
The final stage is to create a form which uses the value entered into the Sales.Employee_ID field on the form to fill in the [Dept],[Ext] or whatever employee-related information from the Employees table was added to the query in 6) above.
1) Click on the 'Forms' tab of the Database window.
2) Click on the 'New' button
3) When the New Form dialog appears:
(a) Select "Design View" in the listbox (should already be selected by default)
(b) Select "Employee_Sales" in the combo box at the bottom of the dialog, then click OK.
4) Make the Field List visible by either clicking on the Field List button or selecting 'Field List' from the 'View' menu.
5) In a similar manner, make the Toolbox visible.
6) Drag fields from the Field List and drop them on the form. This creates text boxes by default.
7) Before adding the Employee_ID field to the form ensure that the Toolbox has the Combo Box and the Wizard buttons down.
7a) Drag the Employee_ID field from the Field List to the form.
8) A Combo Box Wizard should startup.  If not, select Undo Create from the Edit menu and recheck 7) and do 8) again.
The options select in the Wizard are:
- 'I want the combo box to look up the values in a table or query'. Next..
- Click on the Employees table. Next..
- In the values you want included in the combo box select [Name]. Do *not* include Employee_ID as this is done automatically by the wizard. Next..
NOTE: Joining FirstName and LastName fields into one field is not dealt with by the wizard, and must be fixed up later if required.  This is beyond the scope of the issue in your particular question.
- Adjust the width of the column by double-clicking the right edge of the column heading. Next..
- Store the value in field Employee_ID should already appear.  Next..
- Enter 'Employee Name' as a label then click Finish.

To test the form, you should find that in Form View the Employee Name selected in the combo box automatically fills in fields which where drawn from the Employees table side of the underlying query.


The possibility occurred to me that you might not be using normalised table structures.

That is, what if you never intended to have a foreign-key called Employee_ID in the 'Sales' table, but instead wanted to *copy* the information from the Employees table into "mirror" fields such as say 'Emp_Name', 'Emp_Dept'.

You never actually specified what the two tables were and what fields they contained, so I made the assumption that the related table was joined by a foreign key to the primary table (which, as you may be aware, is the way that databases are more 'properly' designed).

Is this 'copying fields' idea closer to what you were originally asking for?

Say you want to populate a record so that it contains the same value that was looked-up from the master record on the 'one' side of the join.

For example, the "Sales" table might have a field called [EmployeeName] which gets its value from [Name] field the "Employee" table for the given [Employee_ID]

First, the query called "Employee_Sales" described earlier should be checked that it includes the field to be populated. e.g. [EmployeeName] from the "Sales" table shown in the query.

Then on the Form properties, build the following Before Update event:

Private Sub Form_BeforeUpdate(Cancel As Integer)
  Me![EmployeeName] = Me![Name]
End Sub

This will 'plug in' the value looked up in the master record into the child record.
There is no need to actually include any text box (hidden or not) called [EmployeeName] on the form.

Hope this helps.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.