Time stamp for user audit

[craigcole5]

I am in the process of implementing a db that will aloow an audit of updates
and users access. My issue is this: I cuurently have an event procedure in
the BeforeUpdate that triggers the changes to be placed in a field on the
form to view what information has been updated and what that current field
showed, to compare the new date.

I think I can do this by placing a command in the OnCurrent property field,
but am unsure how to do this. Any advise will be greatly appreciated.

Regards,

Craig Cole
Transplant Systems Coordinator

 

[Steve Sanford]

Allen Browne has an excelent article on "Creating an Audit Log" at

http://allenbrowne.com/AppAudit.html


HTH

 

[Linq Adams via AccessMonster.com]

To be honest, in reading your post, I'm confused by much of what you say. But
doing this type of thing, unless you're an experienced developer, is a very
daunting task, and Steve's advice is dead on! Allen's article on creating an
audit trail is excellent.

About using the OnCurrent event. This event fires every time you move to a
different record, whether a new record or an existing record, and fires as
soon as the record loads. This means that it also fires when a bound form
opens and the first record appears.

Because of this, it makes it very unlikely that you'd want to use this event
as part of an audit hack. Any code in it will fire every time you visit a
record, even if you're only looking at it or simply passing thru it on the
way to another record, using the navigation arrows.

 

[craigcole5 via AccessMonster.com]

I do want to include this. I thought if I added a tme stamp to that field or
property, I would be able to capture when a user logs intot the system. I
already have a mechanism in place to cath the changes of data.

 

[Steve Sanford]

If you just want to know when a person logs on, create a startup form and set
the visible property to NO.

In either the OPEN event or the LOAD event (I don't use them enough to know
which one touse without trying them), have code to get the userID and insert
it into a table, then open the main form.

Since the form is hidden an only opens once, you will get one entry each
time the MDB is opened.

You could also use the Close or Unload event to also log when they close the
MDB.


HTH

 

[craigcole5 via AccessMonster.com]

Thanks for everyone's help in this matter. Unfortunately, I already have a
startup form that is visible to the users. What I am told I can do, but
simply unsure of how to, is place a time stamp onh the form I want to audit
in the forms' property titled "onCurrrent". Has anyone done this before and
if so, how?

Regards,
Craig

If you just want to know when a person logs on, create a startup form and set
the visible property to NO.

In either the OPEN event or the LOAD event (I don't use them enough to know
which one touse without trying them), have code to get the userID and insert
it into a table, then open the main form.

Since the form is hidden an only opens once, you will get one entry each
time the MDB is opened.

You could also use the Close or Unload event to also log when they close the
MDB.

HTH

I do want to include this. I thought if I added a tme stamp to that field or
property, I would be able to capture when a user logs intot the system. I

[quoted text clipped - 14 lines]

 

[Steve Sanford]

Craig,

Just because you have a "startup form" doesn't mean that you can't create
another form that opens (hidden) *before* your startup form opens.

So......

create a new standard module and paste in the following code:

'I got this code from
' rogersaccesslibrary.com
' a file: AuditTrail.mdb (intermediate)
'http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=399
'---------------------------
Option Compare Database
Option Explicit

Private Declare Function GetComputerNameA Lib "kernel32" (ByVal lpBuffer As
String, nSize As Long) As Long
Private Declare Function GetUserName Lib "ADVAPI32.dll" Alias "GetUserNameA"
(ByVal lpBuffer As String, nSize As Long) As Long

Public Function GetComputerName() As String
On Error GoTo Err_GetComputerName
Dim Username As String * 255
Call GetComputerNameA(Username, 255)
GetComputerName = Left$(Username, InStr(Username, Chr$(0)) - 1)

Exit_GetComputerName:
Exit Function

Err_GetComputerName:
MsgBox Err.Description
Resume Exit_GetComputerName

End Function

Public Function GetCurrentUserName() As String
On Error GoTo Err_GetCurrentUserName
Dim lpBuff As String * 25
Dim ret As Long, Username As String
ret = GetUserName(lpBuff, 25)
Username = Left(lpBuff, InStr(lpBuff, Chr(0)) - 1)
GetCurrentUserName = Username & ""

Exit_GetCurrentUserName:
Exit Function

Err_GetCurrentUserName:
MsgBox Err.Description
Resume Exit_GetCurrentUserName
End Function
'---------------------------

Save the module - name it "UserComputer"


Now create a new form, set the visible property to NO, add a text box (that
will hold the user ID). Name the text box "UserID". Add this code:

'---------------------------
Private Sub Form_Load()
Me.UserID = GetCurrentUserName()
DoCmd.OpenForm "YourStartUpFormName"
End Sub
'---------------------------

Change "YourStartUpFormName" in the code above to the name of your startup
form.


So, the hidden form opens first, gets the user ID, then opens your startup
form. You can get the UserID anytime from the hidden form by referencing the
text box on the hidden form.




There is also an advanced Audit trail example at:

http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=398


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Thanks for everyone's help in this matter. Unfortunately, I already have a
startup form that is visible to the users. What I am told I can do, but
simply unsure of how to, is place a time stamp onh the form I want to audit
in the forms' property titled "onCurrrent". Has anyone done this before and
if so, how?

Regards,
Craig

If you just want to know when a person logs on, create a startup form and set
the visible property to NO.

In either the OPEN event or the LOAD event (I don't use them enough to know
which one touse without trying them), have code to get the userID and insert
it into a table, then open the main form.

Since the form is hidden an only opens once, you will get one entry each
time the MDB is opened.

You could also use the Close or Unload event to also log when they close the
MDB.

HTH

I do want to include this. I thought if I added a tme stamp to that field or
property, I would be able to capture when a user logs intot the system. I

[quoted text clipped - 14 lines]

record, even if you're only looking at it or simply passing thru it on the
way to another record, using the navigation arrows.

 

[craigcole5 via AccessMonster.com]

Thanks Steve,

I have tried numerous options to get my issue to be successful and have not
found one yet. I placed your code into my db and in the first private
declaration
"(ByVal lpBuffer As String, nSize As Long) As Long" I receive this error"
Compile error: Expected line number or statement or end of statement?
any suggestions here? I have tried to place the Ens statement and receive the
same error.

Craig,

Just because you have a "startup form" doesn't mean that you can't create
another form that opens (hidden) *before* your startup form opens.

So......

create a new standard module and paste in the following code:

'I got this code from
' rogersaccesslibrary.com
' a file: AuditTrail.mdb (intermediate)
'http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=399
'---------------------------
Option Compare Database
Option Explicit

Private Declare Function GetComputerNameA Lib "kernel32" (ByVal lpBuffer As
String, nSize As Long) As Long
Private Declare Function GetUserName Lib "ADVAPI32.dll" Alias "GetUserNameA"
(ByVal lpBuffer As String, nSize As Long) As Long

Public Function GetComputerName() As String
On Error GoTo Err_GetComputerName
Dim Username As String * 255
Call GetComputerNameA(Username, 255)
GetComputerName = Left$(Username, InStr(Username, Chr$(0)) - 1)

Exit_GetComputerName:
Exit Function

Err_GetComputerName:
MsgBox Err.Description
Resume Exit_GetComputerName

End Function

Public Function GetCurrentUserName() As String
On Error GoTo Err_GetCurrentUserName
Dim lpBuff As String * 25
Dim ret As Long, Username As String
ret = GetUserName(lpBuff, 25)
Username = Left(lpBuff, InStr(lpBuff, Chr(0)) - 1)
GetCurrentUserName = Username & ""

Exit_GetCurrentUserName:
Exit Function

Err_GetCurrentUserName:
MsgBox Err.Description
Resume Exit_GetCurrentUserName
End Function
'---------------------------

Save the module - name it "UserComputer"

Now create a new form, set the visible property to NO, add a text box (that
will hold the user ID). Name the text box "UserID". Add this code:

'---------------------------
Private Sub Form_Load()
Me.UserID = GetCurrentUserName()
DoCmd.OpenForm "YourStartUpFormName"
End Sub
'---------------------------

Change "YourStartUpFormName" in the code above to the name of your startup
form.

So, the hidden form opens first, gets the user ID, then opens your startup
form. You can get the UserID anytime from the hidden form by referencing the
text box on the hidden form.

There is also an advanced Audit trail example at:

http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=398

HTH

Thanks for everyone's help in this matter. Unfortunately, I already have a
startup form that is visible to the users. What I am told I can do, but

[quoted text clipped - 24 lines]

 

[craigcole5 via AccessMonster.com]

OK, I have resolved the initial issue with this code ; ), but now I am
getting an error on the subform that reads "Method or data member not found"
at the Private Sub Form_Load() indicator.

Thanks Steve,

I have tried numerous options to get my issue to be successful and have not
found one yet. I placed your code into my db and in the first private
declaration
"(ByVal lpBuffer As String, nSize As Long) As Long" I receive this error"
Compile error: Expected line number or statement or end of statement?
any suggestions here? I have tried to place the Ens statement and receive the
same error.

Craig,

[quoted text clipped - 79 lines]

 

[Steve Sanford]

I paste in code alot during testing and I forgot to tell you how to "hook up"
the code to the form.

When you paste code for an event in the IDE, the code is not connected to
the form event. So when you paste in the "Form_Load()" code, you have to:

- open the form in design view,
- show the properties dialog for the form,
- click on the "EVENTS" tab,
- make sure that the On Load property shows "[Event Procedure]",
- then click on the elipis (...).

It should open the IDE to the form load code you just pasted in the code page.


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

OK, I have resolved the initial issue with this code ; ), but now I am
getting an error on the subform that reads "Method or data member not found"
at the Private Sub Form_Load() indicator.

Thanks Steve,

I have tried numerous options to get my issue to be successful and have not
found one yet. I placed your code into my db and in the first private
declaration
"(ByVal lpBuffer As String, nSize As Long) As Long" I receive this error"
Compile error: Expected line number or statement or end of statement?
any suggestions here? I have tried to place the Ens statement and receive the
same error.

Craig,

[quoted text clipped - 79 lines]

record, even if you're only looking at it or simply passing thru it on the
way to another record, using the navigation arrows.

 

[craigcole5 via AccessMonster.com]

Steve, I was mucking around with this last eve and I have the code validated,
but on the form, no data is being returned. I am getting $Name# in the UserID
field?

I paste in code alot during testing and I forgot to tell you how to "hook up"
the code to the form.

When you paste code for an event in the IDE, the code is not connected to
the form event. So when you paste in the "Form_Load()" code, you have to:

- open the form in design view,
- show the properties dialog for the form,
- click on the "EVENTS" tab,
- make sure that the On Load property shows "[Event Procedure]",
- then click on the elipis (...).

It should open the IDE to the form load code you just pasted in the code page.

HTH

OK, I have resolved the initial issue with this code ; ), but now I am
getting an error on the subform that reads "Method or data member not found"

[quoted text clipped - 15 lines]

 

[Steve Sanford]

Is the text box (UserID) on the hidden form an unbound control? The hidden
form should also be unbound.


You can open the module "UserComputer" click anywhere in the Function
GetCurrentUserName() code. Step thru the code one line at a time (the {F8}
key) to see what is happening in the code. Be sure and have the "Locals
Window" (menubar/VIEW) visible to see what is in the variables.

If the correct user ID is seen, the problem is somewhere else.

Open the IDE to the (hidden) form open code and set a breakpoint on the line

Me.UserID = GetCurrentUserName()

Change the form from design view to form view. Use the {F8} key to step thru
the code.


The ides is that the hidden form has an unbound text box that holds the user
ID. Whenever you want to get the user ID to you would use

Forms![YourHiddenformName].UserID

to get the user ID.


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Steve, I was mucking around with this last eve and I have the code validated,
but on the form, no data is being returned. I am getting $Name# in the UserID
field?

I paste in code alot during testing and I forgot to tell you how to "hook up"
the code to the form.

When you paste code for an event in the IDE, the code is not connected to
the form event. So when you paste in the "Form_Load()" code, you have to:

- open the form in design view,
- show the properties dialog for the form,
- click on the "EVENTS" tab,
- make sure that the On Load property shows "[Event Procedure]",
- then click on the elipis (...).

It should open the IDE to the form load code you just pasted in the code page.

HTH

OK, I have resolved the initial issue with this code ; ), but now I am
getting an error on the subform that reads "Method or data member not found"

[quoted text clipped - 15 lines]

record, even if you're only looking at it or simply passing thru it on the
way to another record, using the navigation arrows.

 

[craigcole5 via AccessMonster.com]

on the line Me.UserID = GetCurrentUserName(), the .UserID becomes highlighted
in blue and the Private Sub Form_Load() above this line is yellow. I truely
appriciate your help and knowledge on this subject.

Is the text box (UserID) on the hidden form an unbound control? The hidden
form should also be unbound.

You can open the module "UserComputer" click anywhere in the Function
GetCurrentUserName() code. Step thru the code one line at a time (the {F8}
key) to see what is happening in the code. Be sure and have the "Locals
Window" (menubar/VIEW) visible to see what is in the variables.

If the correct user ID is seen, the problem is somewhere else.

Open the IDE to the (hidden) form open code and set a breakpoint on the line

Me.UserID = GetCurrentUserName()

Change the form from design view to form view. Use the {F8} key to step thru
the code.

The ides is that the hidden form has an unbound text box that holds the user
ID. Whenever you want to get the user ID to you would use

Forms![YourHiddenformName].UserID

to get the user ID.

HTH

Steve, I was mucking around with this last eve and I have the code validated,
but on the form, no data is being returned. I am getting $Name# in the UserID

[quoted text clipped - 20 lines]

 

[Steve Sanford]

The only way I can get the .UserID to become highlighted in blue and the
Private Sub Form_Load() to yellow is when the text box name is not "UserID".

Check the name of the text box.


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

on the line Me.UserID = GetCurrentUserName(), the .UserID becomes highlighted
in blue and the Private Sub Form_Load() above this line is yellow. I truely
appriciate your help and knowledge on this subject.

Is the text box (UserID) on the hidden form an unbound control? The hidden
form should also be unbound.

You can open the module "UserComputer" click anywhere in the Function
GetCurrentUserName() code. Step thru the code one line at a time (the {F8}
key) to see what is happening in the code. Be sure and have the "Locals
Window" (menubar/VIEW) visible to see what is in the variables.

If the correct user ID is seen, the problem is somewhere else.

Open the IDE to the (hidden) form open code and set a breakpoint on the line

Me.UserID = GetCurrentUserName()

Change the form from design view to form view. Use the {F8} key to step thru
the code.

The ides is that the hidden form has an unbound text box that holds the user
ID. Whenever you want to get the user ID to you would use

Forms![YourHiddenformName].UserID

to get the user ID.

HTH

Steve, I was mucking around with this last eve and I have the code validated,
but on the form, no data is being returned. I am getting $Name# in the UserID

[quoted text clipped - 20 lines]

record, even if you're only looking at it or simply passing thru it on the
way to another record, using the navigation arrows.

 

[craigcole5 via AccessMonster.com]

verified. One thing I do notice is that within the body of code, the me.
UserID is in all lowercase and I can not change it to read me.UserID. Not
sure if this makes a difference ans causing the issue?

The only way I can get the .UserID to become highlighted in blue and the
Private Sub Form_Load() to yellow is when the text box name is not "UserID".

Check the name of the text box.

HTH

on the line Me.UserID = GetCurrentUserName(), the .UserID becomes highlighted
in blue and the Private Sub Form_Load() above this line is yellow. I truely

[quoted text clipped - 30 lines]

 

[Steve Sanford]

OK, just to recap:

In a standard module (I named it UserComputer) you have

these declarations:

Option Compare Database
Option Explicit

Private Declare Function GetComputerNameA Lib "kernel32" (ByVal lpBuffer As
String, nSize As Long) As Long
Private Declare Function GetUserName Lib "ADVAPI32.dll" Alias "GetUserNameA"
(ByVal lpBuffer As String, nSize As Long) As Long


and these two functions:

Public Function GetComputerName() As String

Public Function GetCurrentUserName() As String


On the form that is to stay hidden, you have a text box named UserID. On
this same form (the hidden form) you have this code:

'---------------------------
Private Sub Form_Load()
Me.UserID = GetCurrentUserName()
DoCmd.OpenForm "YourStartUpFormName" '<change to your form
End Sub
'---------------------------

and you have opened the form's property dialog and clicked on the "On Load"
property. This shows "[Event Procedure]" and you clicked on the elipis (...)
and can see the code?


In the IDE, have you tried compiling the code? (Menu bar/DEBUG/COMPILE)

Any errors???


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

verified. One thing I do notice is that within the body of code, the me.
UserID is in all lowercase and I can not change it to read me.UserID. Not
sure if this makes a difference ans causing the issue?

The only way I can get the .UserID to become highlighted in blue and the
Private Sub Form_Load() to yellow is when the text box name is not "UserID".

Check the name of the text box.

HTH

on the line Me.UserID = GetCurrentUserName(), the .UserID becomes highlighted
in blue and the Private Sub Form_Load() above this line is yellow. I truely

[quoted text clipped - 30 lines]

record, even if you're only looking at it or simply passing thru it on the
way to another record, using the navigation arrows.

 

[craigcole5 via AccessMonster.com]

I have and receive the same compile error:
Private Sub Form_Load() (yellow)
.userid = GetCurrentUserName() (blue)
DoCmd.OpenForm "Form1" "My form name"
End Sub

OK, just to recap:

In a standard module (I named it UserComputer) you have

these declarations:

Option Compare Database
Option Explicit

Private Declare Function GetComputerNameA Lib "kernel32" (ByVal lpBuffer As
String, nSize As Long) As Long
Private Declare Function GetUserName Lib "ADVAPI32.dll" Alias "GetUserNameA"
(ByVal lpBuffer As String, nSize As Long) As Long

and these two functions:

Public Function GetComputerName() As String

Public Function GetCurrentUserName() As String

On the form that is to stay hidden, you have a text box named UserID. On
this same form (the hidden form) you have this code:

'---------------------------
Private Sub Form_Load()
Me.UserID = GetCurrentUserName()
DoCmd.OpenForm "YourStartUpFormName" '<change to your form
End Sub
'---------------------------

and you have opened the form's property dialog and clicked on the "On Load"
property. This shows "[Event Procedure]" and you clicked on the elipis (...)
and can see the code?

In the IDE, have you tried compiling the code? (Menu bar/DEBUG/COMPILE)

Any errors???

HTH

verified. One thing I do notice is that within the body of code, the me.
UserID is in all lowercase and I can not change it to read me.UserID. Not

[quoted text clipped - 11 lines]

 

[Steve Sanford]

I have and receive the same compile error:
Private Sub Form_Load() (yellow)
.userid = GetCurrentUserName() (blue)
DoCmd.OpenForm "Form1" "My form name"
End Sub

I made a new MDB and created new forms, the module and pasted in the code. I
do not have any errors or problems.

In your code above, you are missing the "Me" in front of ".UserID".
Delete your sub, and paste in the following:

'---------------------------
Private Sub Form_Load()
Me.UserID = GetCurrentUserName()
DoCmd.OpenForm "Form1"
End Sub
'---------------------------

Save it and compile the code.


In an earlier post, you said "I already have a startup form that is visible
to the users". Is that form named "Form1"??

What is the name of the hidden form?

HTH

 

[craigcole5 via AccessMonster.com]

The Me is in place, I was just showing the portion of the code that is
highlighted. The name of my visible form is Form1 and the invisible forms
title is UserID. I did delete and entered the code again previously to your
suggestion, same outcome.

Regards,

Craig

 

[Steve Sanford]

Craig,

At this point I am at a loss.

The code runs without an error on my computer (Win XP/A2K/A2K3).

I have a form named "frmHidden". It has a text box named "UserID".

I added the code.

I have a form named "Form1".

I set the STARTUP option to open "frmHidden" (but visible) when Access start.

My userID is in the text box - no errors... and "Form1" opens.


Try creating a new "Test" mdb. Import the standard module. Create two new
forms - don't import them. On the hidden form, add the text box and the form
open code. On Form1, put any control you want - just so it has something on
it.

Set it up so that when the mdb opens, it opens the hidden form.


If it *still* doesn't work, (if you want) do a compile and repair, and email
it (the test mdb) to me (in A2K or A2K3 format). I will look at it tonight...

HTH