469,942 Members | 2,516 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,942 developers. It's quick & easy.

Tracking Data Changes on Form

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
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?
Sep 11 '06 #1
4 1959
1,418 Expert 1GB

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


and the changes are rejected...

So you don't need the longs


Have a nice day...
Sep 11 '06 #2
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.
Sep 11 '06 #3
1,418 Expert 1GB
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


cycle controls for changes

If change of control

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 !

Sep 11 '06 #4
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.
Sep 11 '06 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Alex.Wisnoski | last post: by
4 posts views Thread by Glenn Owens | last post: by
1 post views Thread by fred tate via .NET 247 | last post: by
6 posts views Thread by Frank Esser | last post: by
7 posts views Thread by Mike McGavin | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.