Tracking Data Changes on Form

[gazelle04]

I'm trying to track data changes on my database. On form I create a procedure on Before Update event to track data changes, here it is: (It also puts the username who changes the record.

Dim ctl As Control
For Each ctl In Controls

If TypeOf ctl Is TextBox Then
If ctl.Value <> ctl.OldValue Then

Dim ans As Integer
ans = MsgBox("A record has changed." & vbcrkf + vbCrLf & _
"Do you want to save the record.", vbYesNo)
If ans = vbYes Then
Me.txtUpdatedBy.Value = fOSUserName
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Else
Cancel = True
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
End If

End If

Next ctl



When I move to another record this procedure runs, It's ok if I say No to the prompt, but when I say Yes, it gives me an error that says I cannot save the data.

Just for a test I place a Save button on the form. When I changed something on the record and clicked that Save button, it gives me an error.

What's wrong with my codes?

[PEB]

Hi,

In Before Update event you don't need to save the record...

The form is automaticlly in process to do it if before update is run...

So to Cancel a Save you need to type

Cancel=true

and the changes are rejected...

So you don't need the longs

docmd.....

Have a nice day...

[gazelle04]

In the message prompt when I clicked "No" I already have that Cancel = True.

But when I clicked "Yes" that's where the error is. I included the DoCmd.Save method there because I needed to have something to prove that I already saved the record and If I didn't included the DoCmd.Save it will prompt me again if I move to another record.

I needed to show to the user the option to save or not whenever there are changes in the record.

[PEB]

About your code...

If multi controls are changed in your record, so this message will appear mult times...

It's better to use a variable like flag

Dim my_flag as boolean

my_flag=false

cycle controls for changes

If change of control
my_flag=true

end if

end cycle

if my_flag=true then
my_result= inputbox...
if my_result=false then cancel=true
end if

Try structure like this without any docmd...

And tell us what happens!

Have a nice day !

:)

[gazelle04]

The error number is 2115. It says that the BeforeUpdate or Validation rule prevents the database from saving the record. I'm not using any validation rules.

When I move to another record or if a form has lost its focus there should be a way to catch the record changes and there should be an option to cancel or save. So far I have catched the option to Cancel.