Lookup values between tables RRS feed

  • Question

  • I'm trying to set up a form that automatically enters a name, gender and year based on the value entered in the unique ID field of the table.  I have a table containing the register of unique numbers, names, genders and years, and I want all four fields to automatically appear in another table (the daily log) by entering only the unique number.  I've tried using lookup tables, calculated fields and relationships but have not managed to crack this one.  I feel as though it should be possible?

    I am using Access 2013.

    Monday, January 30, 2017 1:55 PM

All replies

  • What do you mean you want those fields to "appear" in another table? What is the daily log logging? When and why is it "logging".

    If you want to insert the data from the first to the second, you can put the Id in a variable and use DoCmd.RunSQL "INSERT INTO Table2 (Id, Names, Genders, Years) SELECT Id, Names, Genders, Years FROM Table1 WHERE Id = " & IdVariable

    Or, I think you may just be trying to do with a Table what a Query is responsible for to doing. Why not have a Record_Created date field and a query that can display the records from the original table based on the Record_Created field?
    Monday, January 30, 2017 2:34 PM
  • You shouldn't be duplicating that data. Your daily log should only need the ID. Create a form with your fields in the first table. Add a subform for your daily log table and set the master and child links to the ID field.

    Anytime you want to view the log you use a query that contains the two tables joined on the ID.

    Bill Mosca

    Monday, January 30, 2017 4:01 PM