Capture Current User and Date Modified

  • Thread starter Thread starter D Collins
  • Start date Start date
D

D Collins

Hello,

I was wondering how I would capture the Current User and
Date Last Modified for a record.

Also, is there a way to capture which field on the record
may have been updated and by whom?

Thanks.
D.
 
Access doesn't do this for you, but it's not difficult.

1. Add 4 new fields to your table to hold this information:
EnteredOn Date/Time for create date/time
EnteredBy Text for user who created
UpdatedOn Date/Time for modified date/time
UpdatedBy Text for user who modified.
If your table has an Inactive field (to mark the record inactive without
deleting), you also want:
InactiveOn Date/Time for deactivated date/time
InactiveBy Text for user who deactivated.

2. Select the module tab of the Database window, and click New. Paste the
code below into the module. Save.

3. In the Before Update event of the *form* (not a control), include this
line:
Call StampRecord(Me)
If you want it to handle the field named "Inactive" as well, use:
Call StampRecord(Me, True)

-----------code begins----------------
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function NetworkUserName() As String
' Returns the network login name
Dim lngLen As Long
Dim strUserName As String

strUserName = String$(254, 0)
lngLen = 255
If apiGetUserName(strUserName, lngLen) <> 0 Then
NetworkUserName = Left$(strUserName, lngLen - 1)
End If
End Function

Public Function StampRecord(frm As Form, _
Optional bHasInactive As Boolean = False) As Boolean
On Error GoTo Err_StampRecord
'Purpose: Stamp the user and date/time into the record.
'Return: True if successful.
'Argument: frm = the bound form to be stamped.
' bHasInactive= True if the form has Inactive field.
'Assumes: Fields named EnteredOn, EnteredBy, UpdatedOn, and UpdatedBy.
'Usage: In Form_BeforeUpdate:
' Call StampRecord(Me, True)
Dim strForm As String
Dim strUser As String

strForm = frm.Name 'For error handler.
strUser = NetworkUserName()

If frm.NewRecord Then
frm!EnteredOn = Now()
frm!EnteredBy = strUser
Else
frm!UpdatedOn = Now()
frm!UpdatedBy = strUser
End If

If bHasInactive Then
With frm!Inactive
If .Value = .OldValue Then
'do nothing
Else
If .Value Then
frm!InactiveOn = Now()
frm!InactiveBy = strUser
Else
frm!InactiveOn = Null
frm!InactiveBy = Null
End If
End If
End With
End If

Exit_StampRecord:
Exit Function

Err_StampRecord:
Call LogError(Err.Number, Err.Description, conMod & "StampRecord()",
"Form = " & strForm)
Resume Exit_StampRecord
End Function
-----------code ends------------------

Notes:
1. Replace the error handler with your own, or you can grab the error
logging function from:
http://allenbrowne.com/ser-23a.html

2. If you are using Access security, you can use the CurrentUser() function
to get the user name. If you are not using security, this code grabs the
user name from Windows.
 
Thank you very much for your help!

-----Original Message-----
Access doesn't do this for you, but it's not difficult.

1. Add 4 new fields to your table to hold this information:
EnteredOn Date/Time for create date/time
EnteredBy Text for user who created
UpdatedOn Date/Time for modified date/time
UpdatedBy Text for user who modified.
If your table has an Inactive field (to mark the record inactive without
deleting), you also want:
InactiveOn Date/Time for deactivated date/time
InactiveBy Text for user who deactivated.

2. Select the module tab of the Database window, and click New. Paste the
code below into the module. Save.

3. In the Before Update event of the *form* (not a control), include this
line:
Call StampRecord(Me)
If you want it to handle the field named "Inactive" as well, use:
Call StampRecord(Me, True)

-----------code begins----------------
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function NetworkUserName() As String
' Returns the network login name
Dim lngLen As Long
Dim strUserName As String

strUserName = String$(254, 0)
lngLen = 255
If apiGetUserName(strUserName, lngLen) <> 0 Then
NetworkUserName = Left$(strUserName, lngLen - 1)
End If
End Function

Public Function StampRecord(frm As Form, _
Optional bHasInactive As Boolean = False) As Boolean
On Error GoTo Err_StampRecord
'Purpose: Stamp the user and date/time into the record.
'Return: True if successful.
'Argument: frm = the bound form to be stamped.
' bHasInactive= True if the form has Inactive field.
'Assumes: Fields named EnteredOn, EnteredBy, UpdatedOn, and UpdatedBy.
'Usage: In Form_BeforeUpdate:
' Call StampRecord(Me, True)
Dim strForm As String
Dim strUser As String

strForm = frm.Name 'For error handler.
strUser = NetworkUserName()

If frm.NewRecord Then
frm!EnteredOn = Now()
frm!EnteredBy = strUser
Else
frm!UpdatedOn = Now()
frm!UpdatedBy = strUser
End If

If bHasInactive Then
With frm!Inactive
If .Value = .OldValue Then
'do nothing
Else
If .Value Then
frm!InactiveOn = Now()
frm!InactiveBy = strUser
Else
frm!InactiveOn = Null
frm!InactiveBy = Null
End If
End If
End With
End If

Exit_StampRecord:
Exit Function

Err_StampRecord:
Call LogError(Err.Number, Err.Description, conMod & "StampRecord()",
"Form = " & strForm)
Resume Exit_StampRecord
End Function
-----------code ends------------------

Notes:
1. Replace the error handler with your own, or you can grab the error
logging function from:
http://allenbrowne.com/ser-23a.html

2. If you are using Access security, you can use the CurrentUser() function
to get the user name. If you are not using security, this code grabs the
user name from Windows.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.




.
Click to expand...
 
Hello,

In the code you sent me, you mention conMod -- The
function gets hung up on this, I was wondering if this is
a constant that needs to be defined somewhere.

Thanks.
 
Darn: copied and pasted, without thinking.
It's not important: it just passes the name of the module where the error
occurred to the generic error logging routine.

At the top of every module we write, we include:
Private Const conMod = "NameOfThisModule".
Then if we copy or move a function from one module to another, nothing needs
to be changed: the constant just passes the private conMod for the new
module to the error logger.

You could replace it with:
Me.Module.Name
but that doesn't work if you create an MDE file.
 
Hi,


You have to bring the table twice, in a query. Here is an example:


SELECT a.ItemID,
a.TheDate As OccuringDate,
MAX(b.TheDate) As PreviousDate

FROM myTable As a LEFT JOIN myTable As b

ON a.ItemID=b.ItemID
AND b.TheDate < a.TheDate

GROUP BY a.ItemID, a.TheDate

ORDER BY a.ItemID, a.TheDate


Save it in a query, and thus, you have, per item, per "date where something
occur", the previous date where something did occur for the same item.
Inner join with this query and the initial table may pick-up the extra data:



SELECT a.*, b.*
FROM (previousQuery As p INNER JOIN myTable As a
ON p.ItemID=a.ItemID and p.OccuringDate=a.TheDate)
LEFT JOIN myTable As b
ON p.ItemID=b.ItemID and p.PreviousDate = b.TheDate



with a.* the "actual" data and b.* the data associated to the previous
date, same item (b.* can be all NULL if there is no known previous date).

Change the LEFT to an INNER if you are only interested to data with known
previous data.


Hoping it may help,
Vanderghast, Access MVP
 
Back
Top