title: Duplicate rows inserted in linked table when updating Form's RecordSource with VBA
body: <p>I am doing some maintenance work on a linked-table application in Microsoft Access 2010 and experiencing this little gem of a problem.  The application is linked to a SQL Server 2008 database.  The application has a main form that allows a user to choose a combination of park code and resource and pops up an edit form for the details of that particular combination. If that combo doesn't exist in the database, the application inserts a new record in, but the issue is that 2 records get inserted.</p>
  
  <p>Here's the seat of the problem code, it gets called when I need to insert a new record in a details popup form:</p>
  
  <pre><code>Private Sub New_Rec(unit_code As String, resource As String, sql As String)
  
     DoCmd.RunSQL ("INSERT INTO PARK_RESOURCES (unit_code, resource, sensitivity) VALUES " _
      &amp; "('" &amp; unit_code &amp; "','" &amp; resource &amp; "','public')")
  
     'Force an explicit save
     'http://www.pcreview.co.uk/forums/update-cancelupdate-without-addnew-edit-t1150554.html
     If Me.Dirty Then
        Me.Dirty = False
     End If
  
     Me.RecordSource = sql
  End Sub
  </code></pre>
  
  <p>Creating a "new" record results in 2 records getting inserted into the Recordset.  It doesn't seem to matter if I move the explicit save code before or after setting the RecordSource.  In either order (and stopping after either) produces 2 new records inserted in the database (verified by querying in SSMS).  </p>
  
  <p>When I set the RecordSource property and step through the code, the event chain looks like: Me.RecordSource = sql --> Form_BeforeUpdate() --> Form_AfterUpdate() --> Form_After_Insert() --> Form_Current().  The duplicate is not present at the close of BeforeUpdate, but by the time I get to AfterUpdate, the duplicate has already been inserted.  What happens between BeforeUpdate and AfterUpdate that causes this to happen?  </p>
  
  <p>According to <a href="http://msdn.microsoft.com/en-us/library/office/ff835397%28v=office.14%29.aspx" rel="nofollow">MSDN, the order is</a>: BeforeInsert → BeforeUpdate → AfterUpdate → AfterInsert.  They also state that setting the value of a control through Visual Basic doesn't trigger these events.  But when I update the RecordSource in code, the last 3 events certainly fire; BeforeInsert is the only one that a step-through doesn't stop on.</p>
  
  <p>As per Daniel Cook's request, here is the calling code. </p>
  
  <pre><code>Private Sub Form_Load()
     On Error GoTo Err_Form_Load
  
     Me.KeyPreview = True
     If Not IsNull(Me.OpenArgs) Then
        ProcessOpenArgs (Me.OpenArgs)
        Me.lblHeader.Caption = Me.unit_code &amp; ": Resource - " &amp; Me.resource
     Else
        Me.lblHeader.Caption = "Information Needs"
     End If
     ... (error trapping)
  End Sub
  </code></pre>
  
  <p>And the ProcessOpenArgs sub (OpenArgs get set as "park;resource"):</p>
  
  <pre><code>Private Sub ProcessOpenArgs(open_args As String)
     On Error GoTo Err_ProcessOpenArgs
  
     Dim Args() As String
     Args = Split(open_args, ";")
  
     Me.unit_code = Args(0)
     Me.resource = Args(1)
  
     'Check to see if there are records in the database for current unit/resource combo'
     Dim rs As DAO.Recordset
     Dim sql As String
     sql = "SELECT * FROM PARK_RESOURCES " &amp; _
      "WHERE resource='" &amp; Me.resource &amp; "' AND unit_code='" &amp; Me.unit_code &amp; "'"
     Set rs = CurrentDb.OpenRecordset(sql, dbOpenDynaset, dbSeeChanges)
  
     'if there aren''t, create a new record'
     If (rs.RecordCount = 0) Then
        New_Rec Me.unit_code, Me.resource, sql
     Else 'go to the current listing'
        Me.RecordSource = sql
     End If
  
  Exit_ProcessOpenArgs:
     Exit Sub
  Err_ProcessOpenArgs:
     MsgBox Err.Number &amp; Err.description
     Resume Exit_ProcessOpenArgs
  End Sub
  </code></pre>
  
  <p>I will continue to comb through the event documentation and as a last resort I may go totally nuts and just stick every possible event in my VBA code and step through them, but does anyone know what could be happening to cause the duplicates?</p>