delete duplicate records from query vba

Question

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!"

Johnny Bones · Accepted Answer · 2015-09-04 17:03:04Z

1

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

answered Sep 4, 2015 at 17:03

Johnny Bones

8,4927 gold badges61 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

eshai Over a year ago

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

Johnny Bones Over a year ago

Can you edit the OpenRecordset and add a match for date? Like, "SELECT * FROM MyTable WHERE MyValue = '" & Me.MyValue & "' AND MyDateField >= #" & DateAdd('d', -7, Now()) & "#"

eshai Over a year ago

this code will be cumbersome because i will have to declare every "text field as" and use & and & and & believe me i have this code and is have a lot of procedure to do.pls just "open query as dao" and put the "if" method to see if there's a new record in the query "then" move last delete" i know why i'm using this method

eshai · Accepted Answer · 2015-09-06 18:28:13Z

1

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

edited Sep 6, 2015 at 18:28

answered Sep 6, 2015 at 18:20

eshai

531 silver badge12 bronze badges

Collectives™ on Stack Overflow

delete duplicate records from query vba

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related