Afterupdate Error

[vkong85]

Currently I am running a database in order to keep track of projects. One of the fields in the project tracking is a bit of code which tells me (if someone changes the records) who changed it and when. I am currently running some code to do this but i keep getting an error

my code:

Private Sub Form_AfterUpdat e()
Me.LastModified = DateValue(Now() )
Me.By = MODBY
Me.Refresh
End Sub

me.by is where the name should be. MODBY is a variable dimensioned globally in a module. The value for MODBY is assigned in another form. code for MODBY:

Public MODBY As String **this is declared in the module**

Private Sub form_current() **Mind you this is in a different form**
MODBY = TxtUser.Value
end sub

Im getting two problems. First it tells me that MODBY has a "zero-string" value. But I have checked the value of MODBY before loading the project entry form with a msgbox and it tells me the value that i should be getting.

My second problem occurs when the afterupdate code is run. It reads as the following:

"Run time error '2115':
The macro or function set to the Beforeupdate or Validation Rule property for this field is preventing Microsoft Office Access from saving data in the field.

I am lost. I've tried for the last 3 hours yesterday and still can't firgure it out. Thanks.

[MikeTheBike]

HI

Currently I am running a database in order to keep track of projects. One of the fields in the project tracking is a bit of code which tells me (if someone changes the records) who changed it and when. I am currently running some code to do this but i keep getting an error

my code:

Private Sub Form_AfterUpdat e()
Me.LastModified = DateValue(Now() )
Me.By = MODBY
Me.Refresh
End Sub

me.by is where the name should be. MODBY is a variable dimensioned globally in a module. The value for MODBY is assigned in another form. code for MODBY:

Public MODBY As String **this is declared in the module**

Private Sub form_current() **Mind you this is in a different form**
MODBY = TxtUser.Value
end sub

Im getting two problems. First it tells me that MODBY has a "zero-string" value. But I have checked the value of MODBY before loading the project entry form with a msgbox and it tells me the value that i should be getting.

My second problem occurs when the afterupdate code is run. It reads as the following:

"Run time error '2115':
The macro or function set to the Beforeupdate or Validation Rule property for this field is preventing Microsoft Office Access from saving data in the field.

I am lost. I've tried for the last 3 hours yesterday and still can't firgure it out. Thanks.

I believe this would work better (ie actaully would work) in the BeforeUpdate event !

If you think about it long enough you will realise that your code would lead to an endless loop if it was allowed to run !

MTB

[vkong85]

Its running now but nothing seems to be changing. The values are staying the same. After I changed it to a beforeupdate the msgbox would no longer pop up as if it wasn't being read. What I'm trying to do is read the username after a record has been changed thats why i chose to use the afterupdate.

[MikeTheBike]

HI

Its running now but nothing seems to be changing. The values are staying the same. After I changed it to a beforeupdate the msgbox would no longer pop up as if it wasn't being read. What I'm trying to do is read the username after a record has been changed thats why i chose to use the afterupdate.

I am unsure what message box is not poping up, do you mean the error message?

I assume MODBY is declared only ONCE in the DB and in a CODE module ??
If it is declared in the form module where it is set (MODBY = TxtUser) then it is only available in that form and any global declared variable will be null. Also I think you only need to set it on the OnLoad event of the startup for because the user cannot change while the DB is open (or can it?).

MTB

[vkong85]

i found the solution. It was before update but instead of VBA code i just used the object oriented programming and made myself a macro. Thanks.

[MikeTheBike]

i just used the object oriented programming and made myself a macro. Thanks.

object oriented programming ?????

In a macro ??


MTB

[vkong85]

oops. i meant i just made a macro got too carried away with the big words...

[damonreid]

Why not make a new table called "Changes" for example.

On the afterupdate event simply input a new line into changes. That way you can see all the updates to the form and not just who did the last one.

[code=vb] Dim MyRST As Recordset
Set MyRST = CurrentDb.OpenR ecordset("Chang es", dbOpenDynaset)
With MyRST
.AddNew
![Date Changed] = Date()
![Change By] = CurrentUser
.Update
End With
MyRST.Close[/code]

And you can input any other fields you want as well.