Results 1 to 3 of 3
  1. #1
    haywam59 is offline Novice
    Windows 8 Access 2013
    Join Date
    Jan 2016
    Posts
    5

    Lookup table values

    Hi


    I have a database with a number of tables set-up with primary keys etc. Is it possible when entering a value into one table to get it to also automatically add/show some of the data from the other tables without having to use a query?

    Ie I have table one that shows employees. In another table I have tasks. I would use the primary key of the employee in tasks but is it possible to get it to show the primary key and the name or does that just defeat the point of normalisation?

  2. #2
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Are you talking about doing this on a form to enter tasks?

    If so, it's easily done with a line or two of VBA. Use the After Update event of the employee number (the PK - doesn't have to be a number) to retrieve and display the Employee name into an unbound text control, using a DLookup:

    me![EmployeeName] = DLookup("Employee_Name","Employee","Employee_ID = " & me![EmployeeID])
    Assumes Employee_ID is the PK in the Employee table, and is numeric.

    Change the names in italics to the actual names of your tables, fields and form controls.

  3. #3
    NTC is offline VIP
    Windows 7 64bit Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    a lookup can be implemented at the table level - as the field property (field type: LookUp) and the wizard will walk you thru the set up - OR - it can be set up at the form level by changing the text box to a combobox (but you don't get a wizard in this case)

    in either case; typically the key value is the first, most left, field which is 'bound' (meaning the actual entry) but it is the 2nd field which is viewed. This is all somewhat automated to speed development but at first is confusing. One can add all the other fields of the looked up record - they will display when triggering the look up field which can be very helpful in differentiating which record to select - but they do not display once the selection is made (only the 2nd field is displaying). So in order to get them to display (which is often needed) you can call them from that combobox box because even though you don't see them the data is already there.

    Drag new unbound text boxes onto your form - and then enter their control source to be i.e. =me.comboboxName.column(2) use that exact syntax and vary the number. from left to right the numbering of the columns is 0, 1, 2, etc so the first unseen field will always be 2

    You can further refine & manipulate the actual query of the combobox as well as the width, visibility of the combobox fields in the combobox property ....

    hope this helps

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 04-25-2015, 10:29 PM
  2. How to use lookup table values in a form as a Label
    By MP BILL in forum Database Design
    Replies: 12
    Last Post: 08-09-2012, 06:13 PM
  3. Replies: 5
    Last Post: 06-14-2012, 08:30 AM
  4. Replies: 5
    Last Post: 12-21-2011, 07:16 PM
  5. table lookup / null field values, help!
    By benjammin in forum Forms
    Replies: 3
    Last Post: 07-27-2011, 01:56 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums