Autofill from lookup

Posted on 1998-01-05
Medium Priority
Last Modified: 2012-08-13
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!!!
Question by:fuerstm
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2

Accepted Solution

IanHinson earned 200 total points
ID: 1964371
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.


Author Comment

ID: 1964372
I need more clarification on steps 2 and 3.

Author Comment

ID: 1964373
I need more clarification on step 2 and 3.
This is for Ian Hinson
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.


Expert Comment

ID: 1964374
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.



Expert Comment

ID: 1964375
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?


Expert Comment

ID: 1964376
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.


Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question