Creating Lookup Fields with Microsoft Access 2003

[tlang]

A lookup field allows a data entry user to select from a drop-down list to
ensure accurate data entry. Is there a way to create a lookup field that
triggers the completion of all other fields in a form when a data entry user
makes a selection in that lookup field?

 

[Douglas J. Steele]

Do yourself a HUGE favour, and forget that lookup fields are a possibility.
(See http://www.mvps.org/access/lookupfields.htm at "The Access Web" for
just some of the reasons why.)

Instead, use a combo box in a form, and put code in the combo box's
AfterUpdate event to handle your requirements.

 

[tlang]

Great. So I created the ComboBox with the detail information I want to be
automatically filled in in the fields following the field that houses the
comb box. But I don't know what code to use in the AfterUpdate field in the
combo box properties. What code is necessary to instruct access to fill in
fields subsequent to a combo box with the information contained in the combo
box drop down list?

 

[ruralguy via AccessMonster.com]

If your form is based on a query that joins the proper tables on their key
fields then all you need to fill in the rest of the form is binding the
ComboBox to the ForeignKey field. That way the fields are not duplicated
anywhere.

Great. So I created the ComboBox with the detail information I want to be
automatically filled in in the fields following the field that houses the
comb box. But I don't know what code to use in the AfterUpdate field in the
combo box properties. What code is necessary to instruct access to fill in
fields subsequent to a combo box with the information contained in the combo
box drop down list?

Do yourself a HUGE favour, and forget that lookup fields are a possibility.
(See http://www.mvps.org/access/lookupfields.htm at "The Access Web" for

[quoted text clipped - 8 lines]

 

[tlang]

My form is an Auto Form generated by the creation of a table that contains
employee data, including office location indicator, and address information,
along with employee-specifc data. When a user enters the office laocation
indicator, I want the combobox function to fill in the office address and
phone number information automatically. Right now, the combo box dropdown
list contains all that information (it pulls it form the office details
table), but I want it not to just display the office details information- I
want that information to be placed in the fields subsequnet to the office
location indicator field--the office addrss, city, state, zip, and phone #
fileds, to be exact.

If your form is based on a query that joins the proper tables on their key
fields then all you need to fill in the rest of the form is binding the
ComboBox to the ForeignKey field. That way the fields are not duplicated
anywhere.

Great. So I created the ComboBox with the detail information I want to be
automatically filled in in the fields following the field that houses the
comb box. But I don't know what code to use in the AfterUpdate field in the
combo box properties. What code is necessary to instruct access to fill in
fields subsequent to a combo box with the information contained in the combo
box drop down list?

Do yourself a HUGE favour, and forget that lookup fields are a possibility.
(See http://www.mvps.org/access/lookupfields.htm at "The Access Web" for

[quoted text clipped - 8 lines]

user
makes a selection in that lookup field?

 

[ruralguy via AccessMonster.com]

As I said, if your form were based on a query that joined the Employee table
and the Office table on the OfficeLocationIndicator field you could then
display all of the fields from both tables as if they were in one table.
Completing the ForeignKey (OfficeLocationIndicator) field would automatically
populate all of the displayed fields from the Office table on your form.

My form is an Auto Form generated by the creation of a table that contains
employee data, including office location indicator, and address information,
along with employee-specifc data. When a user enters the office laocation
indicator, I want the combobox function to fill in the office address and
phone number information automatically. Right now, the combo box dropdown
list contains all that information (it pulls it form the office details
table), but I want it not to just display the office details information- I
want that information to be placed in the fields subsequnet to the office
location indicator field--the office addrss, city, state, zip, and phone #
fileds, to be exact.

If your form is based on a query that joins the proper tables on their key
fields then all you need to fill in the rest of the form is binding the

[quoted text clipped - 13 lines]

 

[ruralguy via AccessMonster.com]

I did not suggest you use the AutoForm, whatever that is. Simply set the
RecordSource of previous form to your new query and then go through the
controls and set their ControlSource to the correct field. You are going to
want to use a left join and test if it is updateable in the query Datasheet
view.

I joined the employee details and office detals tables at the
OfficeLocationID field and ran a query, then generated an autoform as you
suggested. However, I am unable to enter data in any but the office details
fields; the others do not allow for entry of any data, and filling in the
OfficeLocatorID field does not automaticall fill in the rest of ht eOffice
Details fields in the form.

As I said, if your form were based on a query that joined the Employee table
and the Office table on the OfficeLocationIndicator field you could then

[quoted text clipped - 18 lines]