Form Modification Indicator (A2K)

**AGMCon** · 2003-10-16 23:57

I know I've done this before, but as usual, when I need it again, cannot locate it. Basically, I would like to display the date on an input screen showing when the last time the Form was modified, not the data, but the form itself. If someone would be kind enough to help me with this, I promise to add it to my ever growing collection of "code snippets" which I'm storing in TreePad. Thanks in advance,

Andy

**HansV** · 2003-10-17 00:24

You can put a (locked, disabled) text box on the form with control source

=[CurrentProject].[AllForms]("frmMyForm").[DateModified]

where frmMyForm is the name of the form; set the format of the text box to the date/time format you like. Or you can set the caption of a label in the On Load event of the form:

Me.lblModified.Caption = Format(CurrentProject.AllForms(Me.Name).DateModifi ed, "mm/dd/yyyy hh:mm")

**AGMCon** · 2003-10-17 09:52

Hans,

Thanks kindly for your quick response. I created a text box, with =[CurrentProject].[AllForms]("frm DPM Projects").[DateModified] as Data Source, locked it, disabled it and got the old #Error when I saved it. Where did I go wrong?

**HansV** · 2003-10-17 10:04

CurrentProject and AllForms were introduced in Access 2000, so unless there is a typo in the name of the form, it should work.

Have you tried the other method I suggested (setting the caption of a label in the On Load event)?

**AGMCon** · 2003-10-17 18:58

Hans,

If at first you don

**HansV** · 2003-10-17 19:23

I tested on Access 2002; it works there. I don't have Access 2000, so I can't test it there, or tell you why it doesn't work for you. Anyone else?

Meanwhile, here is a different method using DAO. DAO has been available in Access for much longer than AllForms etc, so it should work. <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>

We use the lblModified label and put code in the On Load event:

Dim dbs As DAO.Database
Dim ctr As DAO.Container
Dim doc As DAO.Document

Set dbs = CurrentDb
Set ctr = dbs.Containers("Forms")
Set doc = ctr.Documents(Me.Name)

Me.lblModified.Caption = doc.Properties("LastUpdated")

Set doc = Nothing
Set ctr = Nothing
Set dbs = Nothing

**Support4John** · 2003-10-17 21:13

Hi Hans

Is this date dyanmic?

When you do a Compact & Repair is the date changed, so you loose the actual date the form was changed?

John

**HansV** · 2003-10-17 21:25

John,

If you use this code in the On Load event of a form, the LastUpdated property will be read each time the form is opened.

A compact and repair doesn't modify the design of database objects, so the Last Updated date/time won't be changed. Compiling the code in a database (Debug | Compile <project> in the Visual Basic Editor) will change the Last Updated date/time for all forms, reports and modules affected by the compilation.

**AGMCon** · 2003-10-17 22:05

Interestinger and interestinger. OK, cut and pasted your exact code into the lblModified label and got back a date from August of this year which meant absolutely nothing to me. So, I made a programmatical change to the form by adding a new field, therefore MODIFYING the form. Saved and exited and came back in and lo and behold, it still shows the August date. I may be losing something in the translation. I had expected to see the date and time when I'd made this change and nothing happened. Oh, well.... I appreciate your help but feel free to drop this little quirk until I possibly find another answer. I hate to take up your valuable time,

Andy

**HansV** · 2003-10-17 22:22

The LastUpdated property returns the date/time the form was created! It's a bug, see ACC2000: DAO LastUpdated Property Returns Incorrect Date/Time. I didn't notice at first because the dates were equal for the form I tested on, but now I see that it doesn't work. According to the MSKB article, the modification date can't be retrieved any more using DAO in Access 2000 and up. Sorry!

It's a pity that the methods I suggested originally don't work for you, for they do return the correct modification date on my form. <img src=/S/sad.gif border=0 alt=sad width=15 height=15>

**AGMCon** · 2003-10-18 00:10

Hans,

Thanks again for your gallant efforts. I'm way too tired tonight to flog this thing any longer. Will give another try over the weekend.

Andy

**charlotte** · 2003-10-18 02:34

Access 2000 doesn't recognize the syntax, Hans. It works in 2002 but not 2000. DateModified and DateCreated weren't exposed properties in 2000 and DateModifed, DateCreated and CurrentView weren't available as dropdown properties at all in 2000, although you could get to CurrentView directly through Forms("myform").CurrentView.

**HansV** · 2003-10-18 09:36

Thanks for testing on Access 2000, Charlotte. So the DAO method doesn't work any more in Access 2000, and the DateModified property isn't available yet. Do you have any idea how to get at the modified date of a form then?

**WendellB** · 2003-10-18 12:20

My <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15> - Since Access 2000 we don't find the date modified to be of significant value, as every time you compile the database all forms and reports (unless they don't have a module) get the current date, even if nothing changed.

**charlotte** · 2003-10-18 13:57

I don't know of any, but then I don't allow users to change object design and I use SourceSafe to track my changes. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

Thread: Form Modification Indicator (A2K)

Thread Tools