Databases
StackOverflow2013
Postsdbo
PODuplicate rows inserted in linked table when updating Form's RecordSource with VBA
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PODuplicate rows inserted in linked table when updating Form's RecordSource with VBA
    primarykey
    data
    text
    <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>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USMichaelJCox
    1. USMichaelJCox
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. PODuplicate rows inserted in linked table when updating Form's RecordSource with VBA
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PODuplicate rows inserted in linked table when updating Form's RecordSource with VBA
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PODuplicate rows inserted in linked table when updating Form's RecordSource with VBA
      1. This table or related slice is empty.
      1. VTUpMod
    1. CONew_Rec is being called more than once with the same parameters. You didn't show how it gets called, so there's no way I could tell you. You could always update the New_Rec to check for the record before inserting it. It would take longer, but end the issue.
      singulars
      1. PODuplicate rows inserted in linked table when updating Form's RecordSource with VBA
      1. USDaniel
    2. COThanks Daniel. I've included the calling code in case it might help, but wouldn't the debugger make 2 trips through New_Rec when I step through the code? It only goes through 1 time for me.
      singulars
      1. PODuplicate rows inserted in linked table when updating Form's RecordSource with VBA
      1. USMichaelJCox
    3. COIn your `ProcessOpenArgs`, the `If (rs.RecordCount = 0) Then` line could be a problem unless you first use `rs.MoveLast` - see http://msdn.microsoft.com/en-us/library/ff821452%28v=office.14%29.aspx
      singulars
      1. PODuplicate rows inserted in linked table when updating Form's RecordSource with VBA
      1. USbarrowc
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload