locked
Look Up Field Values form a Separate Database Based on a Field added (Similar to VLookup) RRS feed

  • Question

  • Hi All, I have two Databases, One that contains a Stock Table, and another that contains my Orders.

    In the Orders Database I want to create a form to add orders, on This form when you are adding stock to be ordered is it possible that when you add the Barcode into a Field, it then looks up the barcode the the STOCK database and then copys the rest of the field details accross, EG, Description Price, Size etc. And then if the item isnt found have a separate form to add new stock.

    It would be similar to a VLOOKUP in Excel.

    Thanks

    Tom

    PS: I am using Access 2010


    • Edited by t0m46 Thursday, February 9, 2012 3:28 PM Wrong Version of Access
    Thursday, February 9, 2012 3:26 PM

Answers

  • I know of no way to link when one side is empty.   You cannot join on a null or zero lenght string.

    • Marked as answer by Bruce Song Thursday, February 23, 2012 10:00 AM
    Friday, February 10, 2012 5:08 PM

All replies

  • Why not just link the tables you need from the Stock database into your Orders Database and then just use a set of queries.  You can use a regular append query, etc. once you do that.

    Bob Larson, Access MVP 2008-2010, 2011

    Thursday, February 9, 2012 5:05 PM
  • Hi Thanks for your reply, Would this work in real time? EG, When you are adding a new Row to the table, when you enter the first field which is the barcode the other fields will be automaticaly populated?

    Thanks

    Friday, February 10, 2012 11:40 AM
  • There is no need to store the information other than the barcode.   Just use the linked information in your queries/forms/reports when need to display.  Otherwise it is a waste of disk space and if something changes in the Stock table then the duplicated data will be out of date.
    Friday, February 10, 2012 3:52 PM
  • Hi Thanks, The Main Reason I want to do this is because sometimes, until we recieve the order we dont know what the Barcode is, which is used to link the stock table to the orders and sometimes it will be blank so it wont be able to link, Is there a way to link two fields across, So if there is no barcode link on a ID which is an autonumber?

    Thanks

    Friday, February 10, 2012 4:15 PM
  • I know of no way to link when one side is empty.   You cannot join on a null or zero lenght string.

    • Marked as answer by Bruce Song Thursday, February 23, 2012 10:00 AM
    Friday, February 10, 2012 5:08 PM