Update Recordset deletes records in underlying table

[lachesis]

I have a form to display the records from its underlying table, tblCategory.

In order to edit existing records from the underlying table I transfer (user clicks 'EDIT' button) the displayed record to unbound fields on the same form. In the unbound fields the user can make any changes & then clicks the 'UPDATE' button.

'UPDATE' then uses the following code to perform the update:

Dim rs as recordset
Set rs = me.recordsetclone
...
some code to validate the unbound field changes
...
' check the modified record is not a duplicate
rs.MoveFirst
rs.FindFirst "[fnCatCode] = " & Me.UnboundCatCode.Value

If rs.NoMatch Then ' no dupes
rs.Edit
rs![fnCatCode] = Me.UnboundCatCode.Value
rs![fnCatName] = Me.UnboundCatName.Value
rs.upDate
Else ' dupe found
MsgBox "This is a duplicate record." & chr(13) & _
"Please try with other data."
Me.UnboundCatCode.SetFocus
Exit Sub
End If
...

The main problem is that the record does not edit the current record. For eg.

fnCatcode fnCatName
14 Name1

Edited to:
140 Name1

In tblCategory the end result is:
14 Name1
140 Name1

Also each time I run the UPDATE procedure, it deletes the first record in the table.

Not sure what the heck is going on here??
Help!

 

[faeryfyrre]

ok,
I'm only making an educated guess here but when you do the search to see if the new value will be a duplicate you move the recordset's current record, and in your case when you launch your rs.edit on rs.nomatch, i think the recordset is sitting on a new record so you've basically done the Equivalent of rs.addnew.

You would have to reset the recordset back to the Record you actually want to edit.

IE place the following line before your rs.edit
rs.bookmark = me.bookmark



Alec Doughty
Doughty Consulting P/L

"Life's a competition. Play hard, but play fair"

 

[lachesis]

thanks. That seem to have done the trick.