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

I am confused. You have this code in the form's BeforeUpdate event. That would indicate that the form is bound. However, you have written code to update the record yourself.

Which is it? If the form is bound (has a query or table in the RecordSource property), the code should be removed except for the question:
If MsgBox("Are you sure you want to make these changes to this firm?", vbYesNo, "Change Firm Details") = vbNo Then
Me.Undo
Cancel = True
End If

If the form is not bound, the form's BeforeUpdate event will never fire, you'll need to add your own save button to the form and put your update code in the click event of your save button.