INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Record date that a record is modified.

Record date that a record is modified.

(OP)
I have Access 2016.

I would like to record the date a record is modified. The code should replace the former date if the record is subsequently modified.

This is what I have tried... which does not work. ModDate is the name of the field that the modification date should be recorded.


CODE

Private Sub Form_Dirty(Cancel As Integer)
    Me!ModDate.Value = Date
End Sub 

Thank you.

rccline

RE: Record date that a record is modified.

Try the Form's Beforeupdate event instead.

RE: Record date that a record is modified.

(OP)
I tried AfterUpdate and under Debug, I was not able to compile the code.
And my input form was frozen.

CODE

Private Sub Form_AfterUpdate()
       Me!ModDate.Value = Date
End Sub 

Then, I tried this...

CODE

Private Sub Form_AfterUpdate()
    UPDATE tblInstruments SET tblInstruments.ModDate = Date();
End Sub 
.. I get "Syntax Error."

RE: Record date that a record is modified.

(OP)
Oh.. I think... I need to tell it to RunSQL, but I still get "Expect end of statement"

CODE

Private Sub Form_AfterUpdate()
    Docmd.RunSQL UPDATE tblInstruments SET tblInstruments.ModDate = Date();
End Sub 

RE: Record date that a record is modified.

CODE --> vba

Docmd.RunSQL "UPDATE tblInstruments SET tblInstruments.ModDate =" &  NOW()  & ";" 

RE: Record date that a record is modified.

(OP)
Thank you Pwise:

This code compiled, but when I moved of the record I modified, I got a runtime error 3075
"syntax error (missing operator) in query expression '2/9/2017 7:20:23 AM'."

RE: Record date that a record is modified.

Need to add date delimiters "#"

CODE -->

Public Sub insert()
  Dim strSql As String
  strSql = "update table1 set moddate = #" & Now & "#"
  CurrentDb.Execute strSql
End Sub 

However, just so you know that code is worhtless. It will update every record to the current date and time. You need to add a where statement like

strSql = "update table1 set moddate = #" & Now & "# where somePK = " & me.ID

RE: Record date that a record is modified.

(OP)
YEAH!! That worked... How wonderful...

THANK YOU MajP. smile

RE: Record date that a record is modified.

Well, the following works fine on my copy of Access 2010:

CODE

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me!ModDate.Value = Date
End Sub 

However, I note "my input form was frozen", which suggests to me that your input form is displaying a control bound to ModDate - at which point you have a re-entrancy problem, which you can defend against:

CODE

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Static InUpdate As Boolean
    If Not InUpdate Then
        InUpdate = True
        Me!ModDate.Value = Date
    End If
    InUpdate = False
End Sub 

RE: Record date that a record is modified.

StrongM is correct in using the BeforeUpdate. rccline seemed to like the AfterUpdate which doesn't work.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

RE: Record date that a record is modified.

(OP)
I created a blank db to test the difference between each of the examples above.
All examples worked!

Like most things in VBA, I didn't understand the signifance of choosing BeforeUpdate vs AfterUpdate.
10-13-2014, Eljefegeneo wrote in access-programmers.co.uk/forums: "As a learning lesson, and please correct me if I am wrong, the BeforeUpdate event is for validating data only and the AfterUpdate event can modify the data."

I didn't find anything about Static Booleans, but am guessing that it confines the Update to the form?

In any event gentlemen, thank you very much for providing answers to my question. This simple code is very useful to me. I don't understand why my first example wasn't working in my first application. It works in a fresh environment!

rccline


RE: Record date that a record is modified.

The AfterUpdate event occurs after the record is saved. This would not be a good time save the moddate since it would be a little late.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close