Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Lookup values for display on a form

Posted on 2004-11-07
Medium Priority
Last Modified: 2012-08-14
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.

Question by:rugby148
LVL 14

Expert Comment

ID: 12521093
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.

LVL 58

Accepted Solution

harfang earned 1200 total points
ID: 12521105
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

Expert Comment

ID: 12521256

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.



Author Comment

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

LVL 58

Expert Comment

ID: 12528985
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 :)

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

564 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