none
Is it possible to keep two tables id fields in sync? RRS feed

  • Question

  • I would like two tables to have two fields kept in sync. The tables are joined on these fields, and if a new record is made in one table, I need the id to be matched in the other table so that a sub form will always contain fields when viewed on the main form.

    There is a join on the two tables.

    Thanks


    Nath

    Monday, March 6, 2017 7:34 PM

Answers

  • Yes,

    I want to automatically create a matching record in the subform as soon as a new record is added to the main form.

    I'm having trouble passing the parent form id to a function to add the subform's table record.


    Nath

    Nath - Maybe you don't understand how a subform works. As long as you link the master and child on the proper field Access will automatically put the parent's ID in the subform's table for you. You don't even have to put that field on the subform. Just make sure if you are using a query as the record source that you include it in said query.

    The sub form will get that ID as soon as you start adding a record in it.


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    • Marked as answer by NaPazz Monday, March 6, 2017 9:11 PM
    Monday, March 6, 2017 8:53 PM

All replies

  • Hi Nath,

    If you place a subform on the main form and link it using the ID field, then the ID should automatically get entered when you add a new record in the subform. Otherwise, nothing will show because the other table is still empty or does not have a matching record for the one displayed on the main form.

    Or, are you saying you want to automatically create a matching record in the subform as soon as a new record is added to the main form?

    Monday, March 6, 2017 8:28 PM
  • Yes,

    I want to automatically create a matching record in the subform as soon as a new record is added to the main form.

    I'm having trouble passing the parent form id to a function to add the subform's table record.


    Nath

    Monday, March 6, 2017 8:39 PM
  • Are you doing it in the main Form's AfterUpdate event? Also, what data are you creating in the subform? If just the ID, then it should not be necessary because only having an ID in the subform is pretty much an "empty" subform too because you really don't need to show the ID on both forms.

    Just my 2 cents...

    Monday, March 6, 2017 8:48 PM
  • Yes,

    I want to automatically create a matching record in the subform as soon as a new record is added to the main form.

    I'm having trouble passing the parent form id to a function to add the subform's table record.


    Nath

    Nath - Maybe you don't understand how a subform works. As long as you link the master and child on the proper field Access will automatically put the parent's ID in the subform's table for you. You don't even have to put that field on the subform. Just make sure if you are using a query as the record source that you include it in said query.

    The sub form will get that ID as soon as you start adding a record in it.


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    • Marked as answer by NaPazz Monday, March 6, 2017 9:11 PM
    Monday, March 6, 2017 8:53 PM
  • Thanks Bill.

    It did work automatically, and then stopped.

    After your suggestion, I noticed Allow Additions had been mistakenly set to No.

    Setting to Yes, has resolved the problem.

    Thanks.


    Nath

    Monday, March 6, 2017 9:13 PM
  • Hi Nath,

    Congratulations! Glad to hear you got it sorted out. Good luck with your project.

    Monday, March 6, 2017 9:18 PM
  • Ha! Now you know that you have to check all the little details. Those are the ones that will drive you crazy.

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Monday, March 6, 2017 9:47 PM