Logging changes to records 4

[kupe]

Is there an automatic way to record the date any and every time a record is changed in Access, please, Gurus?

 

[PHV]

Provided the users can't change the record outside your forms (no table nor query direct access) you may consider doing your change logging in the AfterUpdate event procedure of the form.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kupe]

I wondered about that, PH, thanks very much, but I couldn't see how I could get it to record the date and time because by then the form is showing a different record. Cheers

 

[BobStubbs]

I use this code to store the date and time in a field, when a record is updated through a form:

    Me.LastUpdate = Date
    Me.UpdateTime = Now
    DoEvents

-- LastUpdate is a date/time field in the underlying table, formatted as Short Date;
-- UpdateTime is a time field in the underlying table, formatted as Short Time;
-- The code is added to the BeforeUpdate event of the form, so the text boxes change before the new values are saved to the table.

I hope that this helps.

Bob Stubbs

 

[PHV]

OOps, sorry for the typo.
I meant the BeforeUpdate event procedure of the Form, after all the eventual validation checks.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kupe]

Thanks enormously, PH and Bob. That really is brilliant. Very much obliged.

 

[DSburgh]

Would this also work for a form that users make changes directly to the table using datasheet view?

 

[PHV]

I think so.

 

[kupe]

Yes, DSburgh, it does. (I've just tried it, and it records the date and time). In fact, I'm just using

Me.UpdateTime = Now
DoEvents

and it is amazing how quickly you can find how many records were updated for the day, or for a certain part of the day.

It is such a useful extra, I can't think how I have done without it. Thanks to BobStubbs and PHV, I'll be incorporating this into all my dbs. Thanks, gentlemen,

 

[BobStubbs]

Another feature you might want to add ...

If you use user and group security on your database, then each user has a name which you can get at via the CurrentUser variable. Thus you can record who updated a record, as well as when it was updated:

    Me.LastUpdateBy = CurrentUser

... where LastUpdatedBy is a text field in the underlying table. Again, this code goes in the form's BeforeUpdate event.

Bob Stubbs

 

[kupe]

Thanks, Bob, that's really useful too. Much obliged.

 

[DSburgh]

Bob,

does the currentuser variable pull the logon name from Access or from Windows? I was going to create a table to hold all the employees using the database but since security is not going to be necessary I think it would be a lot easier to just log the name of the Windows logon.

 

[PHV]

CurrentUser : Access security user
Environ("UserName") : Windows logon (except on win9x platform)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BobStubbs]

CurrentUser gets the Access user name. Thus, in a database without user and group security, it will just return Admin, which is the default user name provided by the System.mdw file.

However, you can get to the Windows user name via an environment variable. To see this, try this code which you can run from a command button on a test form:

Private Sub Command0_Click()

    MsgBox (Environ$("UserName"))
    
End Sub

To set a field on a form equal to the Windows user name, just use:

    Me.LastUpdateBy = Environ$("UserName")

By the way, Environ$ will get any Windows environment variable for you, if required. To see a full list of these, open a command window and type SET. You will see something like:

SystemDrive=C:
SystemRoot=C:\WINDOWS
TEMP=C:\DOCUME~1\Fred\LOCALS~1\Temp
TMP=C:\DOCUME~1\Fred\LOCALS~1\Temp
USERDOMAIN=STUDY3
USERNAME=Fred
USERPROFILE=C:\Documents and Settings\Fred
windir=C:\WINDOWS

Any parameter on the left of an equals sign can be used with Environ$, to return the current value for your user / PC.


Bob Stubbs

 

[kupe]

Gentlemen

Ah, but what about a form with subforms. If the User makes changes to the subforms, then that is not recorded.

There are two subforms, so would I need to repeat the process on each, please?

 

[BobStubbs]

Yes, that's exactly what I do in one of my databases. This means that changes to the sub-form records are date / time stamped separately from main record changes (the effect my company wanted, as sub-records changed much more often than the 'parent' record).


Bob Stubbs (London, UK)

 

[kupe]

Cheers, Bob. I was disappointed when I presumed I'd have to do it in 3 places, but I see from what you are saying, there are advantages having individual loggings. Thanks very much. All the best.