title: INSERT INTO multiple tables with a single SQL statement, preserving AutoNumber
body: <p>I have two tables.</p>
  
  <p><code>Payments</code> consists of a single Autonumber field - <code>PaymentID</code>. `</p>
  
  <p><code>PaymentsMain</code> contains two fields - <code>MainID</code>, and <code>PaymentID</code>. It is joined to <code>Payments</code> on <code>PaymentsMain.PaymentID = Payments.PaymentID</code>.</p>
  
  <p>How can I insert a single value into <code>PaymentsMain.MainID</code>, and have a new corresponding record added to <code>Payments</code> with the AutoNumber calculated into <code>Payments.PaymentID</code>, and that calculated Autonumber used as the value for `PaymentsMain.PaymentID'?</p>
  
  <p>I imagine this is possible if I manually generate the <code>PaymentID</code> - some sort of running sum based on the <code>MainID</code>s added to the initial maximum value of the <code>PaymentID</code>, but I am wondering if this can be done using the Autonumbers alone.</p>
  
  <p><strong>Edit</strong></p>
  
  <p>The relationships are as follows:<img src="https://i.stack.imgur.com/FBkTO.png" alt="Relationships"></p>
  
  <p><strong>Edit</strong></p>
  
  <p>If it is possible to specify that Access should insert the <code>DEFAULT</code> value for the <code>PaymentID</code>, that would also work.</p>