Results 1 to 6 of 6
  1. #1
    TomMcCray is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2021
    Posts
    2

    Lookup field value in table from a query

    I'm sure my answer is here somewhere in the forum, but I'll be darned if I can find it with various keyword searches.



    I have an MS Access 2013 database I have inherited that has significant existing reports, tables, forms and queries.


    As a result, I'm reluctant to make significant changes since it seems things are working, but I need to add some values in a table.


    In the table "tblX" I want one field ("fldX") to obtain its value from an existing query ("qryY") based on a field common to both ("fldY").


    I have created a Lookup in fldX, and I can get the value using a combobox ONE time, but if the value in qryX changes, the value in the tblX fldX lookup does not update.


    So, basically I want fldX to reflect the value in fldY of qryY, and to be "hot-linked" so if the value in qryY changes, the value in tblX fldX updates automatically.


    Surely possible within a lookup query tblX, but I'm missing something...


    Thanks in advance!

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    using lookups in tables is a bad idea - use a form.

  3. #3
    TomMcCray is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2021
    Posts
    2
    Quote Originally Posted by Ajax View Post
    using lookups in tables is a bad idea - use a form.
    Yes, yes, I know. I have seen the "warnings", even though it is a canned function ("Lookup Wizard") in MSACCESS.

    Creating a form (and hooking to a table) just to do what a simple "VLOOKUP" does natively in Excel seems a bit ridiculous.

    Surely there is a simple solution.

  4. #4
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Ajax,
    How dare you, an access guru and veteran of this forum, draw from your years of experience to give this person good advice and "warnings" when they clearly already know better... How dare you?!

  5. #5
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by TomMcCray View Post
    I have created a Lookup in fldX, and I can get the value using a combobox ONE time, but if the value in qryX changes, the value in the tblX fldX lookup does not update.
    That problem is well known and why the "WARNINGS".



    Quote Originally Posted by TomMcCray View Post
    ..... even though it is a canned function ("Lookup Wizard") in MSACCESS.
    Yep, everything MS does woks perfectly! Well, except for Windows (all of the "Service packs" and weekly "Updates")



    Quote Originally Posted by TomMcCray View Post
    Creating a form (and hooking to a table) just to do what a simple "VLOOKUP" does natively in Excel seems a bit ridiculous
    Except Access and Excel are two completely different object models.......



    Quote Originally Posted by TomMcCray View Post
    Surely there is a simple solution.
    Well, yes. DON'T use table level look up fields.

  6. #6
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    If the value can be looked up the simply make a form based on a query to present/display the data in the way that you want.

    Fundamentally, you are confusing a table that stores data, as a place to work with the data, which it is not and never should be.
    Use a form with a combo as the simple equivalent of a VLookup.

    I have just removed about 30 "lookup-fields" from a clients system that were causing performance issues, difficulty with modifying forms and generally being a PITA!
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

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

Similar Threads

  1. Replies: 9
    Last Post: 10-10-2019, 05:13 PM
  2. Replies: 5
    Last Post: 01-23-2015, 03:38 PM
  3. Replies: 3
    Last Post: 05-31-2014, 11:37 AM
  4. Replies: 3
    Last Post: 03-07-2013, 02:29 PM
  5. Query to lookup relative field from table selection
    By shabbaranks in forum Queries
    Replies: 5
    Last Post: 12-20-2011, 11:12 AM

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