track when current user modifies record in Access form

[Jane Walker]

You can track date and time a record is modified in an Access 2003 form - why
can't you add "current user" also if user-level security is set up to track
the user who modified record?

 

[Ofer]

On the before update event of the form you can add the code

Me.[CurrentUserFieldName] = CurrentUser()

To assign the value of the current user to the field every time you update
the record

 

[Jane Walker]

thanks so much - that worked! But I had a macro in the BEFORE UPDATE EVENT -
that used SETVALUE to be Current Date and Current Time.

Is it possible to get all three things to write to the table - Date, Time,
and Current User? or at least Date and Current User? I really appreciate
your help.

On the before update event of the form you can add the code

Me.[CurrentUserFieldName] = CurrentUser()

To assign the value of the current user to the field every time you update
the record
--
\\// Live Long and Prosper \\//
BS"D

You can track date and time a record is modified in an Access 2003 form - why
can't you add "current user" also if user-level security is set up to track
the user who modified record?

 

[Ofer]

It's always better using code then macro, espacially for the error traping

Me.[Username] = CurrentUser()
Me.[DateField] = Date()
Me.[TimeField] = Time()

--
\\// Live Long and Prosper \\//
BS"D

thanks so much - that worked! But I had a macro in the BEFORE UPDATE EVENT -
that used SETVALUE to be Current Date and Current Time.

Is it possible to get all three things to write to the table - Date, Time,
and Current User? or at least Date and Current User? I really appreciate
your help.

On the before update event of the form you can add the code

Me.[CurrentUserFieldName] = CurrentUser()

To assign the value of the current user to the field every time you update
the record
--
\\// Live Long and Prosper \\//
BS"D

You can track date and time a record is modified in an Access 2003 form - why
can't you add "current user" also if user-level security is set up to track
the user who modified record?

 

[Ken Sheridan]

Jane;

The crucial word here is 'modified'. A record can be updated, i.e. the
form's BeforeUpdate and AfterUpdate events can be triggered, without the data
itself changing. A user can change a value and then change it back before
saving the record; an update but not a modification. A method I have used
for this is as follows. Note that the (now quite old) code uses DAO so make
sure you have a reference to the Microsoft DAO 3.6 Object Library
(Tools|References on the VBA menu bar) as Since Access 2000 ADO has been the
default data access technology.

First create the following standard module:

''''Module begins''''
Option Compare Database
Option Explicit

' arrays for storing values from recordsets
Dim aOldVals(), aNewVals()

Public Sub StoreOldVals(rst As DAO.Recordset)

' store values of current row in array
aOldVals = rst.GetRows()

End Sub

Public Sub StoreNewVals(rst As DAO.Recordset)

' store values of edited row in array
aNewVals = rst.GetRows()

End Sub

Public Function RecordHasChanged() As Boolean

Dim n As Integer, intlast As Integer
Dim var As Variant
Dim aOld(), aNew()

intlast = UBound(aOldVals) - 1

' loop through array of original values
' and store in new array
For Each var In aOldVals()
ReDim Preserve aOld(n)
aOld(n) = var
n = n + 1
Next var

n = 0

' loop through array of edited values
' and store in new array
For Each var In aNewVals()
ReDim Preserve aNew(n)
aNew(n) = var
' if any value has changed then return True
If Nz(aNew(n), 0) <> Nz(aOld(n), 0) Then
RecordHasChanged = True
Exit For
End If
n = n + 1
Next var

End Function
''''module ends''''

Then in the form's class module its Current and AfterUpdate event procedures
are as follows. The AfterUpdate event should be used not the BeforeUpdate
event as the latter may be triggered but the record not saved due to some
data error such as an index violation, whereas the latter is triggered after
the record is successfully saved. In the following code the form's
underlying table is MyTable with a numeric primary key MyID and has columns
DateTimeChanged of Date/Time data type and ChangedBy of text data type:

Private Sub Form_Current()

Dim dbs As DAO.Database, rst As DAO.Recordset
Dim strSQL As String

If Not Me.NewRecord Then
strSQL = "SELECT * FROM MyTable WHERE MyID = " & Me.MyID

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)

StoreOldVals rst
End If

End Sub

Private Sub Form_AfterUpdate()

Dim dbs As DAO.Database, rst As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT * FROM Mytable WHERE MyID = " & Me.MyID

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)

StoreNewVals rst

If RecordHasChanged() Then
strSQL = "UPDATE MyTable " & _
"SET DateTimeChanged = #" & _
Format(Now(), "mm/dd/yyyy hh:nn:ss") & _
"#,ChangedBy = """ & CurrentUser() & """ " & _
"WHERE MyID = " & Me.MyID
dbs.Execute strSQL
End If

End Sub

Ken Sheridan
Stafford, England

 

[Jane Walker]

I made a statement for adding Date and Time modified (instead of using Macro
and SetValue), then added your statement on the line after date and time -
and it actually worked !!! I am so happy, happy, happy.....thank you again.

thanks so much - that worked! But I had a macro in the BEFORE UPDATE EVENT -
that used SETVALUE to be Current Date and Current Time.

Is it possible to get all three things to write to the table - Date, Time,
and Current User? or at least Date and Current User? I really appreciate
your help.

On the before update event of the form you can add the code

Me.[CurrentUserFieldName] = CurrentUser()

To assign the value of the current user to the field every time you update
the record
--
\\// Live Long and Prosper \\//
BS"D

You can track date and time a record is modified in an Access 2003 form - why
can't you add "current user" also if user-level security is set up to track
the user who modified record?

 

[Douglas J Steele]

I consider it much better to store Date and Time in a single field, and use
the Now() function to populate it.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

It's always better using code then macro, espacially for the error traping

Me.[Username] = CurrentUser()
Me.[DateField] = Date()
Me.[TimeField] = Time()

--
\\// Live Long and Prosper \\//
BS"D

thanks so much - that worked! But I had a macro in the BEFORE UPDATE EVENT -
that used SETVALUE to be Current Date and Current Time.

Is it possible to get all three things to write to the table - Date, Time,
and Current User? or at least Date and Current User? I really appreciate
your help.

On the before update event of the form you can add the code

Me.[CurrentUserFieldName] = CurrentUser()

To assign the value of the current user to the field every time you update
the record
--
\\// Live Long and Prosper \\//
BS"D


:

You can track date and time a record is modified in an Access 2003 form - why
can't you add "current user" also if user-level security is set up to track
the user who modified record?

 

[Scott]

I tried this and am having difficulties... On the form side... where it has
me.myid the second time it keeps erroring out. I have a screen shot i could
e-mail someone...

I greatly appreciate any assistance.

HAPPY NEW YEAR!!!