Solved

Autofill from lookup

Posted on 1998-01-05
6
597 Views
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!!!
0
Comment
Question by:fuerstm
  • 4
  • 2
6 Comments
 
LVL 1

Accepted Solution

by:
IanHinson earned 100 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.

0
 

Author Comment

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

Author Comment

by:fuerstm
ID: 1964373
I need more clarification on step 2 and 3.
This is for Ian Hinson
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 1

Expert Comment

by:IanHinson
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.

Ian.

0
 
LVL 1

Expert Comment

by:IanHinson
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?

0
 
LVL 1

Expert Comment

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

0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

707 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now