Lookup values for display on a form

Need a little help.  This should be pretty simple.

I have a form that has a combo box "name" derived from table "people" and stores the primary key "personID"

I want to display below the name combo box the corresponding address, phone, city, state and zip fields from the table people.  I do not need to store the corresponding data in any additional location.

I assume i want to do something in the afterupdate of the frm field "name"
I suspect it could set values on the form to equal the appropriate values from the other table.

Please help.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

you don't need any code for this, actually: simply create a subform with the info in it (address, phone, city etc.), then put the subform below the combo box and make sure the link fields are personID.

Several ways, of which the AfterUpdate event is not the best...

1) In the main form, edit the recordsource to create a "left join" to the table "people" based on "personID" (add table "person" to the query, create the link by dragging "personID" from one table to the other. Then right-click the line, choose options and select "display all records from <your main table>".

They you simply add the fields "address", "phone", etc to your form, probably with .Locked:Yes, .Enabled:No, transparent, etc.

This seems the best way.

2) In the combo where you select the "personID", let's call it "cboName", add all the information you want to see outside of the combo, but leave the number of columns set to 2.

In several other textboxes, use "= cboName.Column(3)", changing the number until you get the hidden column from the combo to display what you want.

3) Create a tiny subform displaying the personal info needed. Set the subforms LinkChildField to "personID", and LinkMasterField to "cboName". Of course, you can make the info on the subform read-only.

AfterUpdate has these problems:
* needs VBA or macro
* runs *as part of the event*, preventing you form doing anything else... The solutions above are managed by Access and will not lock the display at all...

Good Luck

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

For example your combobox RowSource:
 Person: [adressTable]![Adress]&" "&[...]![Fone]&" "&[...]![City]&" "&[...]![state]&" "&[...]![zip]

And also, in making this query for your combobox in criteria you can yous loaded form's value.


rugby148Author Commented:
I forgot to include one important detail.  The main form is an unbound form.  Is a subform a possibe?

Not a problem at all. The LinkMasterField accepts fileds and labels as source (not expressions, but these can be placed in invisible text controls).

The query solution is not possible in this case, obviously :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.