By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,930 Members | 1,269 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,930 IT Pros & Developers. It's quick & easy.

Afterupdate Error

P: 24
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_AfterUpdate()
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.
Jul 19 '07 #1
Share this Question
Share on Google+
7 Replies


Expert 100+
P: 634
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_AfterUpdate()
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
Jul 19 '07 #2

P: 24
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.
Jul 19 '07 #3

Expert 100+
P: 634
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
Jul 19 '07 #4

P: 24
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.
Jul 19 '07 #5

Expert 100+
P: 634
i just used the object oriented programming and made myself a macro. Thanks.
object oriented programming ?????

In a macro ??


MTB
Jul 19 '07 #6

P: 24
oops. i meant i just made a macro got too carried away with the big words...
Jul 19 '07 #7

damonreid
Expert 100+
P: 114
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.

Expand|Select|Wrap|Line Numbers
  1.                 Dim MyRST As Recordset
  2.                 Set MyRST = CurrentDb.OpenRecordset("Changes", dbOpenDynaset)
  3.                 With MyRST
  4.                     .AddNew
  5.                     ![Date Changed] = Date()
  6.                     ![Change By] = CurrentUser
  7.                     .Update
  8.                 End With
  9.                 MyRST.Close
And you can input any other fields you want as well.
Jul 19 '07 #8

Post your reply

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