title: Insert query to insert multiple rows always ends up inserting one row less in the table
body: <p>I have a continuous subform that has a checkbox (IsTag) and a Contact name. A button that inserts records into the table. The button executes the following query.
  This is my insert query in for a form:</p>
  
  <pre><code>CurrentDb.Execute ("INSERT INTO [dbo_PrimaryContacts/InternalContacts_IntersectionTable] SELECT '" &amp; progID &amp; "' As Program_ID ,tmpInternalContacts_ID As InternalContacts_ID FROM dbo_tmpInternalContacts WHERE IsTag = True;"), dbFailOnError
  </code></pre>
  
  <p>The table [dbo_PrimaryContacts/InternalContacts_IntersectionTable] is a linked table.</p>
  
  <p>The query takes the the Program Id (progID) from the main form.
  My problem is: Suppose I select 5 checkboxes and click the button, the insert query only inserts 4 rows. I tried to run the query in sql server and it runs perfectly fine, but when it executes as part of the vba code it inserts one row less.</p>
  
  <p>I have tried adding a new Identity field and using dbSeeChanges, but it did not work.</p>
  
  <p>Can anyone help me figure out this problem.</p>
  
  <p>Thank You.</p>
  
  <p>This is my vba code:</p>
  
  <pre><code>Private Sub Command15_Click()
  
  
  Dim strSel As String
  Dim dbs As DAO.Database
  Dim rs As DAO.Recordset
  Dim progID As String
  progID = Forms![F_PROGRAM MAIN].Program_ID.Value
  Debug.Print progID
  Set dbs = CurrentDb
  strSel = "Select Program_ID from [dbo_PrimaryContacts/InternalContacts_IntersectionTable];"
  Set rs = dbs.OpenRecordset(strSel)
  
  rs.FindFirst "Program_ID = '" &amp; progID &amp; "'"
  If rs.NoMatch Then
  DoEvents
    CurrentDb.Execute ("INSERT INTO [dbo_PrimaryContacts/InternalContacts_IntersectionTable] SELECT '" &amp; progID &amp; "' As Program_ID ,tmpInternalContacts_ID As InternalContacts_ID FROM dbo_tmpInternalContacts WHERE IsTag = True;"), dbFailOnError
  DoEvents
    MsgBox "Tags have been saved successfully!", vbInformation, "MyApp"
    GoTo Cleanup
  Else
     DoEvents
      CurrentDb.Execute ("DELETE FROM [dbo_PrimaryContacts/InternalContacts_IntersectionTable] WHERE Program_ID = '" &amp; progID &amp; "';"), dbFailOnError
      CurrentDb.Execute ("INSERT INTO [dbo_PrimaryContacts/InternalContacts_IntersectionTable] SELECT '" &amp; progID &amp; "' As Program_ID ,tmpInternalContacts_ID As InternalContacts_ID FROM dbo_tmpInternalContacts WHERE IsTag = True;"), dbFailOnError
     DoEvents
      MsgBox "Tags have been saved successfully!", vbInformation, "MyApp"
  
   End If
  Me.Parent.Refresh
  Cleanup:
  rs.Close
  Set rs = Nothing
  Set dbs = Nothing
  Dim result As String
  Set rs = CurrentDb.OpenRecordset("Select [FullName] from dbo_tmpInternalContacts where IsTag = true;")
  While rs.EOF = False
           result = result &amp; ", " &amp; rs.Fields("[FullName]")
      rs.MoveNext
  Wend
   If Left(result, 2) = ", " Then result = Right(result, Len(result) - 2)
  Me.Parent!txtPrimaryContact = result
  
  Command15_Click_Exit:
  Exit Sub
  
  Command15_Click_Err:
  MsgBox Error$
  Resume Command15_Click_Exit
  
  End Sub
  </code></pre>