delete duplicate records

[eshai]

hi:
i build a query for duplicate records
onbeforeupdate form in need to run a vba code
if the record is already in the table. then delete the last record

Dim qry As QueryDef
Set qry = CurrentDb.QueryDefs("duplicate records")
if (i'm stuck) >0 then (again stuck) move last delete
MsgBox "The problem already reported before!"

 

[JHB]

What is duplicate, a single record or ...?
You can setup your tables so duplicates isn't allowed.
Use a recordset to find one or more records with duplicates.
Or DCount to find a single record with a duplicate.
Here is a link how to use DCount.
http://www.techonthenet.com/access/functions/domain/dcount.php

 

[AccessBlaster]

http://www.allenbrowne.com/subquery-01.html

This example uses a subquery to de-duplicate a table. "Duplicate" is defined as records that have the same values in Surname and FirstName. We keep the one that has the lowest primary key value (field ID.)

DELETE FROM Table1
WHERE ID <> (SELECT Min(ID) AS MinOfID FROM Table1 AS Dupe 
WHERE (Dupe.Surname = Table1.Surname) 
AND (Dupe.FirstName = Table1.FirstName));

 

[eshai]

What is duplicate, a single record or ...?
You can setup your tables so duplicates isn't allowed.

This example uses a subquery to de-duplicate a table. "Duplicate" is defined as records that have the same values in Surname and FirstName. We keep the one that has the lowest primary key value (field ID.)

thanx for the reply. let me explain more. i used the build in duplicate records query in access 2013 and add some criteria to the the query as show only last 7 days record. now if i use table i will have to use "dcount" my query have 5 fields so i only want to do "dim" my query and use "if" ' new record as been add ' then delete it

 

[eshai]

ok i solved it.
I created a query using the find duplicates query wizard.
then i insert this code in "form close"

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = Application.CurrentDb
Set rs = db.OpenRecordset("qry_Duplicates")


Do Until rs.EOF
rs.MoveFirst
rs.delete
rs.Close
Set rs = Nothing
Set rs = db.OpenRecordset("qry_Duplicates")
Loop