Update/Don't update Recordset problem.

[square]

Hi,

I need your assistance.

I have built a form to use to edit one data field in my tables.

If the data has been changed, before moving to the next record or closing the form, the user gets a Yes/No message box asking them to confirm whether they like to make the change.

If Yes is chosen, I'd like the change to be saved to the table. If No, the changes should be disregarded.

I've tried the following code but it doesn't work quite as expected.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim myForm As Form, myResponse As Integer
Dim dbsFinancial As Database
Dim myTable As Recordset, myControl As TextBox
Dim myFirm As String, myOldValue As String

Set dbsFinancial = OpenDatabase("C:\myDatabase")

Set myForm = Forms!FrmMemberEdit
Set myTable = dbsFinancial.OpenRecordset("TblMbrs", dbOpenDynaset)
Set myControl = Me.Controls("MbrName")

myFirm = myForm!MbrName
myOldValue = myControl.OldValue

If myFirm <> myOldValue Then

myResponse = MsgBox("Are you sure you want to make these changes to this firm?", vbYesNo, "Change Firm Details")

End If

If myResponse = vbYes Then
EditName myTable, myFirm
With myTable
.Edit
MsgBox "Current Value is " & myFirm & vbLf & "the Previous value was " & myOldValue & "."
!MbrName = myFirm
.Bookmark = .LastModified

End With
Else
.CancelUpdate
End If

End Sub

Sub EditName(rstTemp As Recordset, strMember As String)

With rstTemp
.Edit
!MbrName = strMember
.Update
.Bookmark = .LastModified
End With

End Sub​

Any help is welcome.

Square