Saturday, December 08, 2007

Last Modified Date

I was looking for a way to display the last modified date on a form or report, but the two methods I had tried did not give the correct results.

In this example, I was using a form named frmSwitchboard...

Method 1
SELECT DateUpdate
FROM MSysObjects
WHERE Name="frmSwitchboard" AND Type=-32768

Method 2
CurrentDb.Containers("Forms").Documents("frmSwitchboard").LastUpdated

..both of these returned the creation date, not the true last modified date. I also tried looping through all the object properties, but there weren't any date properties to be found.

I Google'd around and found this thread:

Utter Access Discussion Forums - Date an Object was last modified

and then adapted what I found to create these two simple functions that you can reference on your form or report that will indeed display the same Modified date that shows in the Database Window:

Function LastModified_Form() As Date
LastModified_Form =

CurrentProject.AllForms(Application.CurrentObjectName).DateModified
End Function

Function LastModified_Report() As Date
LastModified_Report =

CurrentProject.AllReports(Application.CurrentObjectName).DateModified
End Function

Note that line breaks were added to better fit the Blogger window; within each function the statement should be all on one line.

Simply add an unbound text box and set the ControlSource as follows:

on a form, use =LastModified_Form()

on a report, use =LastModified_Report()

No comments: