Last modified

[jxd914psu]

How would I go about making a macro to create a date/time of the time a record was last modified? I would want to record this date/time within the same table in its own field.

Thanks.

 

[boblarson]

A few things come to mind -

If you store the last modified date/time in a field within the same record then the last modified date/time actually become the date/time that you modified the record to add the last modified date/time. Seems to me like a pointless exercise to me. What you SHOULD do is create a function that returns that value and could be used in a query or form, etc. and then you can call it any time and actually get a meaningful result while not denormalizing your data.

I've done something actually similar along those lines but I store the last date/time that the record was modified by adding some code in the Before Update event of the table and I have a last modified date/time stamp and username to capture who last modified the records. I can do this by the form because I never let the users access tables or queries directly - only via forms I present to them.

 

[jxd914psu]

Could you show me or go in more depth as to how I would capture the time and user? Same thing you did?

 

[boblarson]

I just made sure to have at least two fields
1. UserName
2. DateTimeStamp

in each of my tables and in the Before Update event of the form I used:

    Me.txt_datetime_stamp =Format(Now, "hh:nn:ss") 
    Me.txt_user_stamp = fOSUserName

To get the user name you can use Environ("username") to simplify but several people have mentioned that is not so secure as people can modify their environment variables. So, you can put this in the general declarations of a STANDARD (not form) module:

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

and then put this function in a STANDARD module (not form module - making sure the module name is DIFFERENT than the function name:

Function fOSUserName() As String
' Returns the network login name
    Dim lngLen As Long, lngX As Long
    Dim strUserName As String
    strUserName = String$(254, 0)
    lngLen = 255
    lngX = apiGetUserName(strUserName, lngLen)
    If lngX <> 0 Then
        fOSUserName = Left$(strUserName, lngLen - 1)
    Else
        fOSUserName = ""
    End If

End Function