Updating a recordset

[Allie]

When a record is added to a datasheet(subform) if a
particular criteria is met, 5 fields are added to a
different table (tableB). I am using the recordset Addnew
method to add these fields to the new table.

If any of these records are later changed, I would like to
make the changes in TableB. I am using the recordset
Edit method.

Problem - instead of editing the existing record, a second
record is added to TableB. I have use the edit method
with the seek and find methods and can not get the desired
result.

Please help

Thanks

 

[Tony C]

Hello Allie

Before anyone can help you with this, a few questions need
to be answered: -

Are you using DAO or ADO, or another method for data
manipulation?
What does your existing code look like?
What verion of Access, and Service Pack, are you using?

Tony C.

 

[Allie]

I am using Access 97, SR2

Code used

Dim Db as dao.database
dim rs as dao.recordset

set db = currentdb
set rs = db.openrecordset("tableB", dbOpenDynaset)

with rs
If me.transCode = "IS1" then
..edit
..fields("BnchId") = me.ID (ID is the PK of the TableA,
BnchID is TableA ID in TableB)
..fields("stateID") = me.state
..fields("Premium") = me.premium
..fields("UserID") = me.currentuser()
..fields("issued") = 1
..update
..close
End if
End with

set rs = nothing
set db = nothing