title: MSAccess VBA AfterUpdate SQL code is running for every record instead of the current record
body: <p>I have an access database that I will be using to track orders and to track inventory levels.  When I attempt to add the parts on my order form (<code>sbfrmOrderDetails</code>) to my inventory table (<code>tblInventory</code>) my VBA code does not execute as planned.</p>
  
  <p>Please note that I have stripped down the code and the tables to just the relevant information/values.  The code posted below does work, just not as intended. I explain in much more detail below.</p>
  
  <hr>
  
  <h2>Form Structure</h2>
  
  <p>I created my Order form (<strong>frmOrder</strong>) as a Single Form.  This form is referred to in my later code to determine the order number using the <strong>txtOrderID</strong> control.  When I link my subform, the linked master field is <strong>OrderID</strong>.</p>
  
  <p>Within this form is my Order Details subform (<strong>sbfrmOrderDetails</strong>) as a continuous form. Every control is bound, and it is linked to the parent form.  The linked child field is <strong>OrderID</strong>.</p>
  
  <p>Photo 1: This photo may better illustrate my form:</p>
  
  <p><img src="https://i.stack.imgur.com/XZWux.jpg" alt="Photo 1: This photo may better illustrate my form."></p>
  
  <hr>
  
  <h2>Table Structure</h2>
  
  <p>The relevant tables I have are structured like so:</p>
  
  <p>TableName: tblOrders<br>
  TableColumns: OrderID</p>
  
  <p>TableName: tblOrderDetails<br>
  TableColumns: ID|InvID|Qty|OrderID|DeliveryStatus</p>
  
  <p>TableName: tblInventory<br>
  TableColumns: ID|InvID|Qty|OrderID</p>
  
  <hr>
  
  <h2>Intended Action</h2>
  
  <p>The action I am trying to take occurs in the subform and is supposed to be isolated to the current record.  When the user changes the ComboBox (<strong>Combo1</strong> bound control to tblOrderDetails.DeliveryStatus), my VBA code will execute an 'INSERT INTO' SQL string that adds the InvID and the Qty from the current record into the inventory table (<strong>tblInventory</strong>).</p>
  
  <p>VBA Code for Combo1 AfterUpdate Event (On sbfrmOrderDetails)</p>
  
  <pre class="lang-vb prettyprint-override"><code>Private Sub Combo1_AfterUpdate()
  Dim db As DAO.Database
  Dim strSQL As String
  Set db = CurrentDb
  
  If Me.Combo1.Value = "Delivered" Then
     strSQL = "INSERT INTO [tblInventory] ([InvID],[Qty])" _ 
        &amp; "SELECT " &amp; Forms![frmOrder].Form![sbfrmOrderDetails].Form![txtInvID] &amp; " AS InvID," &amp; Forms![frmOrder].Form![sbfrmOrderDetails].Form![txtQty] &amp; " AS Qty " _ 
         &amp; "FROM tblOrderDetails WHERE ((tblOrderDetails.OrderID)=(" &amp; Forms![frmOrder]![txtOrderID] &amp; "));" 
  
     Debug.Print strSQL
     db.Execute strSQL, dbFailOnError
  Else
     'Other event
  End If
  End Sub
  </code></pre>
  
  <hr>
  
  <h2>Intended Results</h2>
  
  <p>When Combo1 (bound control) is changed from null to “Delivered” on record ID #11 only, it is supposed to add a single new record.</p>
  
  <p>Photo 2: Intended Results:</p>
  
  <p><img src="https://i.stack.imgur.com/F2NGT.jpg" alt="Photo 2: Intended Results"></p>
  
  <hr>
  
  <h2>Actual Results</h2>
  
  <p>When Combo1 (bound control) is changed from null to “Delivered” on record ID #11 only, it is adding a new record for every record populated in the subform. </p>
  
  <p>Please refer to Photo 2 above to compare the Intended Results to the Actual Results.</p>
  
  <p>You can see that the quantity from records 12 and 13 are transferred over under the InvID from record 11. </p>
  
  <p>Please refer to Photo 1 to view the sample data and also to Photo 2 above to see the Actual Result of the code.</p>
  
  <hr>
  
  <p>I suspect that since this is a continuous form that has Parent/Child linking, the form is running the VBA code once for every record (instead of one time for the current record).</p>
  
  <p>Can I alter my VBA code to only run this code once on the current record as is intended?  I am hoping this is the best approach to complete this task.</p>