Argee's Access Blog

Gord over at UtterAccess is putting the finishing touches on an enhancement to the UtterAccess forum that will be as significant for the Access community as was the introduction to the web of UtterAccess itself almost eight years ago. I’m afraid you will have to wait for the public launch to learn more details but I can guarantee that you won’t be disappointed. At UtterAccess, we pride ourselves as the only source for all of your Microsoft Access help needs. If every you had any doubt about that claim, this new feature will convince you that UtterAccess is what we clam and much, much more.

Clint Covington posted  Access 2010 deprecated features and components in the Access Product Team Blog yesterday that certain features have been deprecated in Access 2010:

• Calendar Control
• Snapshot Format
• Data Access Pages
• Paradox, Lotus, and Red2 ISAM’s
• Replication conflict viewer

If you have applications developed in older Access versions that you want to port to Access 2010, read Clint's article to see what effect these changes will have on your application and some suggested workarounds.You should also keep these deprecations in mind for any new applications you may develop in pre-2010 Access versions.

Quote of the Day Not only is the universe stranger than we imagine, it is stranger than we can imagine. -Sir Arthur Eddington

Using forms to control routine data entry should be standard operating procedure for any non-trivial production Access database. In plain language, don’t give users (even yourself when you acting as a user and not the developer) access to the raw tables. Provide instead forms for entering, viewing, and editing records. In this article we will look at how to use two simple form event handlers to automate recording of when a record is created and when each record was most recently updated.

Event handlers are the key to adding custom functionality to applications. In this case, we want to keep track of when a record is created and that date it was last edited so we will need to work with the Before Insert and Before Update events. First, a little background.

When you create and edit a new record, the record does not exist in a table until you do something to cause the record to be saved. You are actually editing a memory image of what will become the new record when it is saved. Actions like moving to a different record, or pressing Shift-Enter initiate the save (or insert) process. As its name implies, the Before Insert fires before the record is inserted into the table.

To be completely accurate, the Before Insert event fires when the user types the first character of a new record (see msdn article). At this point the record does not yet exist. This give you, the developer, the opportunity to take some additional actions by adding appropriate code to the Before Insert  event handler. When the Before Insert handler finishes, the saving process continues. Along the way, the Before Update event fires, offering another opportunity to do something in addition to simply saving the record.

The chain of events is quite similar for existing records which are being edited. The only real difference is that the Before Insert event doesn’t fire because we are updating an existing record. The record is already in the table so the memory image of the record doesn’t have to be inserted into the table; the table just has to be updated to reflect the changes you have made to the record’s data. So when you do something to put the save process in effect, the Before Update  event will fire before that table data is actually modified.

If you want only a date stamp (without time) the function Date() would do the trick. However if you want the date and time of additions and modifications to be recorded, the Now() function will return a serial date value which it has read from the system clock. The two event handlers are so simple as to appear almost trivial:

Private Sub Form_BeforeInsert(Cancel As Integer)
    Me.DateCreated = Now
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me.DateModified = Now
End Sub

That’s all it takes. With these two form events in place, every time a new record is created using the form, the record’s DateCreated value will be set to the date and time the user started typing data. Similarly, when the form is used to edit data the, the record’s DateModified value will be set to the time that the changes are saved. An interesting side effect of using this approach is that new records have values assigned to both fields. The difference between the DateCreated and DateModified values indicates how long it has take the user to enter data into the new record.

Keep in mind that, while this approach might appear to be the basis of an audit trail solution, it only keeps track of record creation and last modification date and time. If you want to maintain modification history data you should use a separate, related table with a new record appended to it for each record creation/modification/deletion action.

Download the sample database from here. This is a zip compressed file contained the example in both Access 2000 and 2007 format.