Simple Access Audit Trail In VBA – Environmental Variables

.

Do you need to track who is making changes to your company records?

Sometimes you want to go back to the person who changed a record, and find out why they did it? They had some reasoning behind why they made such a change, but you don’t know.

Well, in this post I am going to show you how you can make a simple Access audit trail in VBA. It’s going to help you figure out who made a record change and when.

Here’s the code:

1
2
3
4
5
6
7
8
9
10
11
12
Private Sub Form_BeforeUpdate(Cancel As Integer)
    Select Case MsgBox("This record was modified, would you like to continue?", vbYesNo)
        Case vbYes
            Me.DateModified = Now()
            Me.UpdatedBy = Environ("UserName")
        Case vbNo
            Me.Undo
            Cancel = True
           
    End Select
   
End Sub

This will automatically enter a current timestamp and who made the change (on whose computer was the change made.)

So this is just a simple form. First add a couple fields to your table and then add a couple fields to your form, and then add the prior code to your form’s BeforeUpdate event.

Why? Because that event fires before the record is committed to the database, and you can back out of it (using the built in “Cancel” argument).

So here I am adding (changing) the contact name.

Here, I get a message box asking if I really want to make the change.

After telling the form that I really what to make the change, it will track who made and change and when.

Environmental Variables may prove quite useful to you.

Here is a little procedure to list all the environmental variables available to you (this code lists 44 ):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
Sub PrintAllEnvironVariables()
    Dim strEnviron, strInsert As String
    Dim varSplit As Variant
   
    Dim intCount As Integer
   
    CurrentDb.Execute "DELETE * FROM tblEnvironVariables"
   
    For intCount = 1 To 255
        strEnviron = Environ$(intCount)
       
        If LenB(strEnviron) = 0& Then GoTo TryNext:
       
            varSplit = Split(strEnviron, "=")
           
            If UBound(varSplit) > 1 Then Stop
           
            strInsert = "INSERT INTO tblEnvironVariables(VariableName,VariableValue) "
            strInsert = strInsert & " VALUES ('" & varSplit(0) & "','" & varSplit(1) & "')"
       
            CurrentDb.Execute strInsert
TryNext:
    Next
   
    MsgBox "complete"
   
End Sub

tblEnvironVariables
VariableName (s):
ALLUSERSPROFILE
APPDATA
CommonProgramFiles
CommonProgramFiles(x86)
CommonProgramW6432
COMPUTERNAME
ComSpec
DriverData
FPS_BROWSER_APP_PROFILE_STRING
FPS_BROWSER_USER_PROFILE_STRING
HAIVISION_HELPER_ALTERNATE_TEMP_PATH_WIN
HOMEDRIVE
HOMEPATH
JAVA_HOME
LOCALAPPDATA
LOGONSERVER
NUMBER_OF_PROCESSORS
OneDrive
OneDriveCommercial
OS
Path
PATHEXT
PROCESSOR_ARCHITECTURE
PROCESSOR_IDENTIFIER
PROCESSOR_LEVEL
PROCESSOR_REVISION
ProgramData
ProgramFiles
ProgramFiles(x86)
ProgramW6432
PSModulePath
PUBLIC
SESSIONNAME
SystemDrive
SystemRoot
TEMP
TMP
UATDATA
USERDNSDOMAIN
USERDOMAIN
USERDOMAIN_ROAMINGPROFILE
USERNAME
USERPROFILE
windir

Any questions, let me know?

Offsite Related Information:

“Go Fund Me” Page


($5 suggested amount)

(…10% of your gift amount will go to charity)

Free! Subscribe To Our YouTube Channel!

Free MS Access VBA Programming Course

Tags: , , , ,