fuerstm
asked on
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!!!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I need more clarification on step 2 and 3.
This is for Ian Hinson
This is for Ian Hinson
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.
Ian.
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.
Ian.
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?
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?
POPULATING A CHILD RECORD:
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.
Ian.
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.
Ian.
ASKER