Lookup values for display on a form

Posted on 2004-11-07
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

    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

    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
    LVL 1

    Expert Comment


    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

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

    LVL 58

    Expert Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
    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…

    779 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

    9 Experts available now in Live!

    Get 1:1 Help Now