Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

597 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