ADO Recordset Addnew on Duplicate record

[mikemcginty]

I am using 'On Error Resume Next' to not add duplicate records into my database where the next statement is to return to the top of the recordset.
eg

.addnew
rs.columns("XYZ&quot.value = duplicatedata
.update

.movefirst
Loop

where XYZ is a primary key

The error occurs ok on the .update however it also occurs on the .movefirst so that the recordset never moves to the start record.

How do I handle the error so that the duplicate is not added but I can also shift off the duplicate record? I have adOpenKeyset or adOpenDynamic and adLockPessimistic

Mike

When you call out for help in the darkness, and you hear a voice in return, you're probably just talking to yourself again!

 

[ALSayegh]

I believe you have to way to solve your problem
1- in your data base you can make same rolls to restrict duplicate data either on One Fields or More
then in your program you handle it with “error handler “. How … when you try to add a duplicate
data, data base well send an error to your VB.
2- You can Check in Your Database before you add your record.

I prefer option 1 it faster and more efficient.

 

[SemperFiDownUnda]

Refresh your query ... I think it has to do with the fact that you are using a Keyset recordset.

As a side not I don't use .Addnew with recordsets. I much prefer sending up a "INSERT INTO ..." statement to the server.

 

[CCLINT]

1. Use in line error handling, where, after the update statement, you check for certain error numbers. If the error number is a certain value, then take action on it.
In your case you would use the rs.CancelUpdate
Or
2. Do not use 'On Error Resume Next' but use a qualified Error handler.
In the error handler, check the error number.
Use rs.CancelUpdate and 'Resume Next' [/b][/i][/u]*******************************************************[sub]
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!

 

[mikemcginty]

Thanks for your helpfull responses

CCLINT I believe rs.CancelUpdate is what I was looking for

Mike When you call out for help in the darkness, and you hear a voice in return, you're probably just talking to yourself again!