updating a recordset - repost

[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
Adnew 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.

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

Thanks

 

[Nikos Yannacopoulos]

Allie,

Reading through your code, it looks like you are not adding a record, you
are indeed editing one; the catch is you are always editing the first record
in the recordset, not the targeted one, as there is no action in your code
to get to that record.

I have modified your code to open as a recordset a query on the table
instead of the whole table, filtering on ID, so as to make sure the changes
are made on the right record(s). Actually, if there are more records in
tableB with the same BnchID, the changes will be made to all records. If
there is only ever going to be one record, you can remove the lines with the
'* comment at the end (though leaving them in won't actually change anything
in the execution). Here's the modified code:

Dim Db As DAO.Database
Dim rs As DAO.Recordset
Dim rsSQL As String

If Me.transCode = "IS1" Then

rsSQL = "SELECT * FROM tableB WHERE BnchID = " & Me.ID

Set Db = CurrentDb
Set rs = Db.OpenRecordset(rsSQL, dbOpenDynaset)
rs.MoveFirst
Do Until rs.EOF '*
With rs
.Edit
.Fields("stateID") = Me.State
.Fields("Premium") = Me.premium
.Fields("UserID") = Me.CurrentUser()
.Fields("issued") = 1
.Update
End With
rs.MoveNext '*
Loop '*
rs.Close

End If

Set rs = Nothing
Set Db = Nothing

Note: I have assumed that BnchID is numeric. If it is text, then the
recordset SQL expression needs to be changed to:

rsSQL = "SELECT * FROM tableB WHERE BnchID = '" & Me.ID & "'"

HTH,
Nikos