problem with recordset find

[[email protected]]

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

[NickHK]

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

<[email protected]> wrote in message
news:[email protected]...
> 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
>