delete duplicate records from query vba

1
votes

I have built a query for duplicate records. I need to run some VBA code to check if the record is already in the table (query). If so, I need to delete the last record.

My form consists of a button with a value so that when you click the button, the data is insert into table

Dim qry As QueryDef
Set qry = CurrentDb.QueryDefs("duplicate records")
'which method do i use to see if the query got duplicate record'

With rstCategories
.MoveLast 0
End With
With rstCategories
rstCategories.Delete
End With

MsgBox "The problem already reported before!"
2
ms-accessvba

2 Answers

1
votes

What I would do is run a quick query on your table:

Dim db as Database
Dim rec as Recordset

Set db = CurrentDB
Set rec = db.OpenRecordset ("SELECT * FROM MyTable WHERE MyValue = '" & Me.MyValue & "'")

If rec.EOF = true then
  'No match found, so the value isn't in the table.  Add it.
Else
  'Match found.  This value is already in the table.
  MsgBox "This value is already in the table"
End If
1
votes

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

this code delete the entire row