Storing last modified (97)

Topic

New Reply

I have a field to store the date and time a record was last modified. I’m trying to use the After Update event for a form to set the value of this field to Now(), but I’m getting stuck in the form!! It won’t allow to move to the next record. Help?!

Reply | Quote

Replies

Use the BeforeUpdate event instead…

Reply | Quote

Dur!! So obvious.. Thanks much!

Reply | Quote

Oops! Not quite there yet.. It doesn’t work on the BeforeUpdate event of the form! Do you mean for all the individual fields?

Reply | Quote

I often use the BeforeUpdate event of a form (not of individual controls) to do this. Here is an actual example from one of my databases:

Private Sub Form_BeforeUpdate(Cancel As Integer)
ModificationDate = Date
ModifiedBy = NetUser
End Sub

NetUser is a custom function that returns the network login name of the user.

Reply | Quote

Hans, could you post the NetUser function, and show how one would use it?

Thanks!!

Reply | Quote

Hi Bob,

The NetUser function uses the Windows API function WNetGetUserA to retrieve the network login name of the current user. Please note that this is different from the Access login name. You can use NetUser just as you would use any other function – in expressions and in code. The sample code in my reply higher up in this thread uses it to set the name of the user who last modified a record. Another use could be to display the login name on a form or report – place a text box, for instance in the header or footer section, with control source =NetUser().

Here is the code. If you put it in a general module, it will be available throughout the database. The first bit must go in the declaration section of the module, at the top.

Private Declare Function WNetGetUser Lib “mpr.dll” Alias “WNetGetUserA” _
(ByVal lpName As String, ByVal lpUserName As String, lpnLength As Long) As Long

Public Function NetUser() As String
Dim strName As String, strUserName As String
strName = vbNullString
strUserName = Space(255)
If WNetGetUser(strName, strUserName, Len(strUserName)) = 0 Then
NetUser = Left(strUserName, InStr(strUserName, vbNullChar) – 1)
Else
NetUser = “-unknown-”
End If
End Function

Reply | Quote

…Ignore me! It’s working perfectly. Thanks again.

Reply | Quote