problem with recordset find
 

Hi,

I am writing a VBA function that attempts to add a record to an access
database through excel. it takes a certain range and attempts to append
these values to a table - if there is a record with the same primary
key, i wanted to delete the old entry and add the new one (or just call
update). Anyway, when i wrote the code to find the entry with a given
primary key, i get the error "the changes you requested were not
successful because they would create duplicate values in the index,
primary key, or relationship." I'm confused because this happens right
after the function call to recordset.find, which shouldn't attempt to
add anything to the table. can someone help? here's the code:


Sub AddRec(fieldnames As Variant, entries As Variant)
If (IsRecordSetOpen) Then
On Error GoTo ErrorSearch
Call rs.AddNew(fieldnames, entries)
Exit Sub
End If
On Error GoTo 0
ErrorSearch:
ClearOldRecord fieldnames, entries
Exit Sub
End Sub
Sub ClearOldRecord(fld As Variant, ent As Variant)
rs.Find fld(0) & " = " & ent(0) 'where i get the error
rs.Delete... 'the sub breaks in error before i can get to this
line

End Sub


thanks,
sudar

problem with recordset find
 

Sudar,
The ADODB.Connection has an errors collection. As you are effectively in an
error trap, (caused by the .AddNew failing), you may be seeing
<ADODBConnection.Errors(1) that caused the original error, but because you
have not cleared the errors collection (or Excel's error for that matter),
it is masking any later errors.

NickHK

wrote in message
ups.com...
Hi,

I am writing a VBA function that attempts to add a record to an access
database through excel. it takes a certain range and attempts to append
these values to a table - if there is a record with the same primary
key, i wanted to delete the old entry and add the new one (or just call
update). Anyway, when i wrote the code to find the entry with a given
primary key, i get the error "the changes you requested were not
successful because they would create duplicate values in the index,
primary key, or relationship." I'm confused because this happens right
after the function call to recordset.find, which shouldn't attempt to
add anything to the table. can someone help? here's the code:


Sub AddRec(fieldnames As Variant, entries As Variant)
If (IsRecordSetOpen) Then
On Error GoTo ErrorSearch
Call rs.AddNew(fieldnames, entries)
Exit Sub
End If
On Error GoTo 0
ErrorSearch:
ClearOldRecord fieldnames, entries
Exit Sub
End Sub
Sub ClearOldRecord(fld As Variant, ent As Variant)
rs.Find fld(0) & " = " & ent(0) 'where i get the error
rs.Delete... 'the sub breaks in error before i can get to this
line

End Sub


thanks,
sudar