Tracking Users Who Change Records

  • Thread starter Thread starter Rizzag
  • Start date Start date
R

Rizzag

How do I track users that change records within a form?
My database sits on a network drive and is accessible by
many. Although I have security set up, I want to be able
to view a report or query on who changes the data. Please
help. Thanks.
 
To do this I wrote a fairly simple sub which loops through the controls collection for the form & tests to see if the value has changed. If the value has changed a new entry is made in my history table, logging who, when & what was changed.

Sub HistoryLog(FormName As String, RefNo As String)
On Error GoTo History_Err
Dim ctl As Control, frm As Form, strNewValue As String, strOldValue As String
Set frm = Forms(FormName)
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset

'Open the appropriate recordset
rst.Open ("SELECT * FROM HistoryTable"), cnn, adOpenStatic, adLockPessimistic

'Loop through all controls
For Each ctl In frm.Controls

'Only examine TextBoxes or ComboBoxes
If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Or ctl.ControlType = acCheckBox Then


'Only examine visible, unlocked controls
If ctl.Locked = False And ctl.Visible = True Then

'Dont allow nulls to propogate
strNewValue = Nz(ctl.Value, "Empty")
strOldValue = Nz(ctl.OldValue, "Empty")

'Dont allow Zero-Length-Strings to propogate
If strNewValue = "" Then strNewValue = "Empty"
If strOldValue = "" Then strNewValue = "Empty"

'If value in control has changed create a history record
If strOldValue <> strNewValue Then
rst.AddNew
rst.Fields("RefNo") = RefNo
rst.Fields("ModField") = ctl.Name
rst.Fields("OldValue") = strOldValue
rst.Fields("NewValue") = strNewValue
rst.Update
Else
'Do nothing
End If
Else
'Do Nothing
End If
End If
Next ctl

History_Exit:
Exit Sub

History_Err:
MsgBox Error$
Resume History_Exit

End Sub

Then I just use the BeforeUpdate event of the data-entry forms to kick off the procedure.

N.B. My underling history table automatically inserts the username (Using Environ("UserName")) & InsertionDate (Using Now()) for the column default values.



--
Cheers,


James Goodman MCSE, MCDBA
http://www.angelfire.com/sports/f1pictures
 
Rizzag said:
How do I track users that change records within a form?
My database sits on a network drive and is accessible by
many. Although I have security set up, I want to be able
to view a report or query on who changes the data. Please
help. Thanks.
Click to expand...

You can grab the login user name from an api:

http://www.mvps.org/access/api/api0008.htm

Then just store that in a field in either a history table, or if you only
require knowledge of the last user who changed the record, a field in the
same table. With Access User-level security enabled, it is even easier than
that. There is a function within Access that returns it: Username()
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
I found your message as I tried to find an answer to the
same concern. I have put fields into my table to place
the network UserID (or UserName or whatever it is called)
and date stamps.

I have used "Now()" as the date stamp by making it the
Default Value for my "Date Created" field, but I am not
sure how to populate the "Date Modified" field. The real
head-scratcher for me is how to grab the network user
identifier to put into my "Created/Modified By" field.

I will be watching the responses to your query and hope
that someone else out there can help both of us.
 
Back
Top