Is there a quick-and-dirty way to extract the LastUpdated metadata property from an underlying table and stick in into a report?
Is there a quick-and-dirty way to extract the LastUpdated metadata property from an underlying table and stick in into a report?
The info is stored in the hidden system table MSysObjects in a field called DateUpdate
So use a DLookup in that control
That assumes you only have objects with unique names.Code:=Dlookup("DateUpdate","MSysObjects","Name='YourTableName'")
If you also have e.g. a form with the same name, Access may get confused.
In that case you also need to use the Type and Flags fields
Type=1 for local tables, Flags=0 for non system tables
The result will include the date and time. Format as short date if you don't want the time part
Access does not track when data was last edited.
The LastUpdated property is the last date the table structure was modified.
Another way to get that date in VBA:
CurrentDb.TableDefs("YourTableName").Properties("L astUpdated")
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Thanks June for the correction - that will teach me to answer when I'd just woken up!
You are quite correct in stating that these give the same information
Code:Dlookup("DateUpdate","MSysObjects","Name='YourTableName'")In each case, the result is when the table structure was last modifiedCode:CurrentDb.TableDefs("YourTableName").Properties("LastUpdated")
This is also what is shown in the navigation pane
If you want the date/time the data was last modified, you need to use a timestamp field created for that purpose:
1. Add a field called DateModified to the table design with date/time datatype
2. Add the field to the form used to edit data - format as general date if you want to see it - but it can be hidden
3. Add the following code to your form
4. Add the DateModified field to your reportCode:Private Sub Form_BeforeUpdate(Cancel As Integer) Me.DateModified = Now() End Sub
Last edited by isladogs; 03-05-2018 at 06:42 AM. Reason: Added info on use of timestamp field