SQL INSERT INTO Appends duplicate Records

[ijo68]

I'm Using a simple SQL Statement to insert a record into a backup table.


Code:
DoCmd.RunSQL "INSERT INTO tblContactsBU SELECT * FROM tblContacts " & _
"WHERE ID=" & ID

The Problem is it inserts duplicate records into tblContactsBU... Any idea what may cause this.

Thanks

[Micron]

Not clear what you mean.
Could be that duplicate records are appended at the same time - even if the table was empty it ends up with duplicates.
Could be that the query appends records that are identical to records that are already there.
In the latter case, easiest might be to set one or more indexes (no dupes) on table field(s) and the query will fail to append those records. When that's working you could deal with any warnings you get.

[ijo68]

This is in the 'before-update' of the form properties so a copy is sent to the tblContactBU as a log of the record before changes are made. Only it sends two of the same record.

[Micron]

Then I guess we'd have to see the code for that event since the append is done via vba. However, that may not be enough as there could be other events at play. Could it be that the code stores an 'original' record plus one that is thought to be an edited record but it turns out it's not? Best approach would be for you to put a break point at the beginning of the event and step through it, looking for the cause for the 2 appends and see what you discover.

As mentioned, you can prevent table dupes by setting indexes on one or more fields.

[ijo68]

the only problem with the index is that the record may be edited more than once over time.. I'll put a break as mentioned..Thanks

[June7]

If you want to provide db for analysis, follow instructions at bottom of my post.

Do you have a date/time field in BU table to document when record was created?

[Micron]

I'm not understanding. Edits are one thing; appends are another. If indexed with no dupes allowed, it doesn't matter - you can't duplicate the indexed fields either way. If it is a composite index, you cannot duplicate the combination. The only exception for composite index would be if the index Ignore Nulls setting is Yes. In that case you could have duplicate values in the other field or combination of fields. If you don't have a composite index you can ignore that for now.

[ijo68]

Found the problem.. I used a calculated field in the query to grab the name of the user from the login form , the textbox on the form with this field as it control source had a different name..(txtchgUser)

Thanks for the help

[Micron]

Glad to see that you have a solution.