title: How to get the AutoNumber generated by an SQL insert
body: <p>I am trying to write a function to check the minimum value in an inventory table then generate and send a purchase request if the value is below the minimum level. </p>
  
  <p>If the conditions are met an append sql statement like the one below is run.</p>
  
  <pre><code>DoCmd.RunSQL "INSERT INTO [table1]('field 1', 'field 2')" &amp; _
               "VALUES ('value 1', 'value 2')"
  </code></pre>
  
  <p>The linked tables I am appending to use an auto number field as primary key and I need to reference its value to link the various items being requested. I do not know how to store the auto number that is being generated for the new record. I feel like I am missing something really simple, but none of my searches have brought anything up.</p>
  
  <p>I would like to be able to write a second statement like the one below with the auto number value included.</p>
  
  <pre><code>DoCmd.RunSQL "INSERT INTO [table2]( 'ID' , 'field 3')" &amp; _
               "VALUES (" &amp; TempVars!autonumber &amp; ", 'value 3')"
  </code></pre>