I am having a problem using the FindNext in Access VBA. I have two tables, table A and table B, where table A was created using a query to find duplicate records in Table B. I want to delete one of the duplicate records from Table A.

Here's my code:
Dim rsin As DAO.Recordset
Dim rsin2 As DAO.Recordset
Set rsin = db.OpenRecordset("ProToolDupes", dbOpenDynaset)
Set rsin2 = db.OpenRecordset("ProToolDataSorted", dbOpenDynaset)


rsin.MoveFirst
If rsin.Fields![Year] = "2006" Then
    rsin2.FindNext ("[project number] =" & rsin.Fields![project number])
     rsin2.Delete
End If

I am getting a 'data type mismatch' error.

If I set up the findnext statement as:

rsin2.findnext ("[project number]='01400113'")

it works perfectly (for that one record of course). What am I doing wrong with the statement syntax using a variable to set up the record key?

Any assistance will be greatly appreciated.

Try this...

rsin2.FindNext ("[project number] ='" & rsin.Fields![project number]) & "'"

Mike

Mike
EchoVue.com

You are wonderful!!! Thanks so much!

No Worries!! Welcome to the forum:)

Mike
EchoVue.com

Hi, sorry I am late.

An easier way to do this whole process of removing duplicate records is to create a Make Table query on the dataset with the duplicates, and then select the query properties, and set the Unique Value property to Yes. Then run the query.

This removes the dupes and gives you a back up of the data at the same time.

Then either rename the dataset (table) with the dupes, and rename your new table to the old table name, or delete all the data in the old table and transfer in the new data (may have PK issues, but that is a different problem.)


HTH

mmcdonal