Record date that a record is modified. 3

[rccline]

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.

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

Thank you.

rccline

 

[strongm]

Try the Form's Beforeupdate event instead.

 

[rccline]

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

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

Then, I tried this...

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

.. I get "Syntax Error.

 

[rccline]

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

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

 

[PWise]

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

 

[rccline]

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'.

 

[MajP]

Need to add date delimiters "#"

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

 

[rccline]

YEAH!! That worked... How wonderful...

THANK YOU MajP.

 

[strongm]

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

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

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:

[blue]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[/blue]

 

[dhookom]

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

 

[rccline]

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

 

[dhookom]

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