Results 1 to 5 of 5
  1. #1
    Accessfan is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2016
    Posts
    5

    Committrans and rooback feature with an Autonumber as PK in a main table and in other table


    I have a main table for ServiceOrder using autonumber to generate a unique identifier every time a new records is inserted it is the PK, how can I insert this value in the Mian table and in other table that i have a 1 o many relation the second table i store the details(parts) of the Main table at the same time. I need to have value in both table every time a new record is inserted in the main table, I have try to use commit trans and roll back feature, but i do not know how to do that , if the PK unique identifier in the main table were not autonumber it would be easy. the other idea is to create a table only to generate autonumber value and then insert that value in the main table and the dependency . Any idea? i appreciate a lot you help. Thanks

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I don't understand.

    If you are using bound forms to enter your data you can use a form/subform arrangement
    If you are using unbound forms to enter your data you can use list boxes to select a record to update or, when adding a new record force the 'main' record to save first then you could associate all 'sub' records with the main record through your button clicks. Or are you saying you have a form that is a mix of data from two tables? if that's the case why would you do that? Typically in a one to many relationship you don't mix data entry fields on the same form.

  3. #3
    Accessfan is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2016
    Posts
    5
    Sorry Rpeare for my confused post, I am using unbound forms, what i need is when a new records is inserted in the main table in the other table sabes a set of rows lest say 5 or 12 with the Autonumber value (PK) from the Main table, this is a situation when i have to use commit trans to ensure consistency in both tables. in other system i had the option to first save a record in the main table and then when is needed creat table in the details (depenency) table , but in this case, if i were using a non-autonumber field as PK it would be easy i just use execute with two sql script with insert one per table and using comminttrans and rollback and that would works perfect but niot in the case I presnt. what i want to avoid is this what happend if during the save process a new record is inserted in the main and there is a problem with the PC such as got frozen and never the new ID (PK) form the Main table was saved in the detail table that would be a inconsistency. any idea thanks!

  4. #4
    NoellaG's Avatar
    NoellaG is offline VIP
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    I don't see why you couldn't use the logic of the old application in your Access application: first save the main record to the database and after that start creating child records when needed.

  5. #5
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I agree, if it's always the same records you want to add to the subtable then saving the main record could force records into the subtable.

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

Similar Threads

  1. Replies: 1
    Last Post: 11-04-2015, 07:25 AM
  2. Replies: 14
    Last Post: 12-17-2013, 02:09 PM
  3. Replies: 3
    Last Post: 03-06-2013, 04:53 PM
  4. Replies: 8
    Last Post: 03-22-2012, 08:48 AM
  5. Replies: 2
    Last Post: 08-01-2011, 11:35 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