I wish to display the date and time of a tables modification attribute within a form, using code. Can anyone help me?
:confused:
TIA

Try this as the Control Source for a text box...

="Last Modified: " & DLookUp("[DateUpdate]","MSysObjects","[Name] = 'YourTableNameHere'")

OR

MsgBox "Table was last modified on " & DLookUp("[DateUpdate]","MSysObjects","[Name] = 'YourTableNameHere'")

Be warned that the date will change when a design change has been made and also when the db has been compacted. The date is not updated when a record change has been made to the table.

Thanks GHudson,

Thats just what I need and I'm aware of when the date will change.


Kind Regards,
TAC

Try this as the Control Source for a text box...

="Last Modified: " & DLookUp("[DateUpdate]","MSysObjects","[Name] = 'YourTableNameHere'")

can you set it to be the date any part of the database was last modified. in other words, i have several tables that data might or might not have been new data put in so to specify just one table in 'YourTableNameHere' as above wouldn't be ideal.

thanks in advance :D

Here's the SQL for every object you'll need within your database:

SELECT MSysObjects.Name, MSysObjects.DateCreate, MSysObjects.DateUpdate, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Type) Not In (1,2,3,5,-32757)))
ORDER BY MSysObjects.Name;

should you want to present them all in one go.

Otherwise, just change the part of ghudson's code that says "your table" to any object (forms, modules, queries, etc.) within the database.

can you set it to be the date any part of the database was last modified. in other words, i have several tables that data might or might not have been new data put in so to specify just one table in 'YourTableNameHere' as above wouldn't be ideal.As I stated above...Be warned that the date will change when a design change has been made and also when the db has been compacted. The date is not updated when a record change has been made to the table.