Multi User Log System

  • Thread starter Thread starter Vanessa
  • Start date Start date
V

Vanessa

Hi there,

Am I being really dumb, I am setting up a multi user access system, and

I am having a pain with one (what I think would be a simple) element,
perhaps I have been too close to the project but I am stumped.


What I want to do is automatically take information from one table to
another depending on a afterupdate or something, as a log of what
people have entered, this cannot be held on the same table as the info
can be edited several times a day on the same record and we have to be
able to count outcomes, so ultimatly I cant use append query as the
information change can happen quickly, but we need a record for each
outcome on a seperate table.

I failed to mention, that the last outcome has to be on the
original table as well as adding a unique log in the log table,


I've tried some VB but that didn't work


I just don't have the answer, arghh!!!

Cheers
Vanessa
 
Vanessa said:
Hi there,

Am I being really dumb, I am setting up a multi user access system, and

I am having a pain with one (what I think would be a simple) element,
perhaps I have been too close to the project but I am stumped.


What I want to do is automatically take information from one table to
another depending on a afterupdate or something, as a log of what
people have entered, this cannot be held on the same table as the info
can be edited several times a day on the same record and we have to be
able to count outcomes, so ultimatly I cant use append query as the
information change can happen quickly, but we need a record for each
outcome on a seperate table.

I failed to mention, that the last outcome has to be on the
original table as well as adding a unique log in the log table,


I've tried some VB but that didn't work


I just don't have the answer, arghh!!!
Click to expand...

What you need to do is cycle through all data controls on the form,
determine which have been changed and record the old and new values. This
is the code that I run which you may be able to adapt. In the form's before
update event I'm calling a public function and passing the form object to it
(the contents of Me.txtIssueNo is my unique record identifier and the
transaction argument can ether be Before Update or Delete):

Call libHistory(Me, Me.txtIssueNo, "BeforeUpdate")

Public Function libHistory(frmForm As Form, lngID As Long, strTrans As
String)

'Author: Keith Wilby
'Date: 05 July 2005
'Purpose: Record data transactions in tblHistory
'Called from: Form_BeforeUpdate & Delete events

Dim ctl As Control
Dim db As Database, rs As Recordset, strSQL As String, strUser As String
Set db = CurrentDb
strSQL = "Select * From qryHistory;"
Set rs = db.OpenRecordset(strSQL)
strUser = libUserName()

For Each ctl In frmForm
'Ignore controls such as labels
If ctl.Name Like "cmd*" Then GoTo Skip
If ctl.Name Like "btn*" Then GoTo Skip
If ctl.Name Like "txtXPID" Then GoTo Skip
If ctl.Name Like "*Master*" And frmForm.Name Like "sfrm*" Then GoTo
Skip 'Don't record the MasterID as a seperate transaction
If ctl.SpecialEffect = 0 Then GoTo Skip
If ctl.Name Like "txt*" Or ctl.Name Like "cbo*" Or ctl.Name Like
"ogr*" Or ctl.Name Like "chk*" Then
'MsgBox ctl.name
'Record null to value, value to null, and value changes
If ((IsNull(ctl.OldValue) And Not IsNull(ctl.Value)) Or
(IsNull(ctl.Value) And Not IsNull(ctl.OldValue)) _
Or ctl.OldValue <> ctl.Value) Or strTrans = "Delete" Or strTrans
= "AppendComment" Then
With rs
.AddNew
![DataSource] = frmForm.Name
![ID] = lngID
![FieldName] = ctl.ControlSource
![OldValue] = ctl.OldValue 'Don't record Old Value for
an appended comment
If strTrans = "Delete" Then 'Record the fact that the
record was deleted
![NewValue] = strTrans
Else
![NewValue] = ctl.Value
End If
![UpdatedBy] = strUser
![UpdatedWhen] = Now()
.Update
End With
End If
End If
Skip:
Next

rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

End Function

There may well be a more elegant way to code this but this has been tried
and tested in a busy production environment and has not failed once in over
a year. Hope it helps.

Keith.
www.keithwilby.com
 
Back
Top