Audit Trail

How does the quick search work?
 
here is in code:

Code: 
Private Sub QuickSearch_AfterUpdate()

        Dim rs As Object
        
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[Claim #] = " & str(Me![QuickSearch])
    Me.Bookmark = rs.Bookmark
    
    Me.txtSSN = Me.QuickSearch.Column(1)
End Sub

When you click on record in the list box it populates the rest of the form with all the info pertaining to that record selected.
 
So you have a text box on your form called "Me.txtSSN" and it is being updated by code, I think that's your problem, I've noticed that when controls are updated by code, the updates are not always seen correctly by MS Access, not the same as it when you update them manually anyway!

You could try a form refresh after the text box "Me.txtSSN" has been updated, see if that puts it right, other than that ...............
 
Rem that line out and I still get the same error message.

here is where it is getting stuck:

Code: 
Private Sub QuickSearch_AfterUpdate()

        Dim rs As Object
        
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[Claim #] = " & str(Me![QuickSearch])
    Me.Bookmark = rs.Bookmark
    
    'Me.txtSSN = Me.QuickSearch.Column(1)
End Sub

Me.Bookmark = rs.Bookmark

Any ideas?
 
What happens if you Rem the whole subroutine contents out?
 
Then the whole form is locked on the current record. When I click on a record in the search list box it doesn't change to that record.
 
I just used this and it works great ghudson!

One question though:

We share the DB on a network drive. All users are recorded as "Admin" in the audit trail field. Is there a way to pull the windows user name for each user?

thanks!
 
Environ Info

Try these:
Code: 
 Function fGetWinUserName()
    fGetWinUserName = VBA.Environ("UserName")
End Function      ' fGetWinUserName
 
 Function fGetComputerName()
    fGetComputerName = VBA.Environ("COMPUTERNAME")
End Function      ' fGetComputerName

Note: can use Environ("UserName") or Environ("COMPUTERNAME") but not for all access versions.
 
thanks - one other question since I am pretty poor at VB. Do I paste this in the current VBA code that GHUDSON provided.

below is his code which is really niuce

Public Function Audit_Trail()
On Error GoTo Err_Audit_Trail

'ACC2000: How to Create an Audit Trail of Record Changes in a Form
'http://support.microsoft.com/default.aspx?scid=kb;en-us;197592

Dim MyForm As Form
Dim ctl As Control
Dim sUser As String
Set MyForm = Screen.ActiveForm
' sUser = "User: " & UsersID 'You need to identify your users if you are not using Access security with workgroups.
sUser = CurrentUser

'If new record, record it in audit trail and exit function.
If MyForm.NewRecord = True Then
MyForm!AuditTrail = MyForm!tbAuditTrail & "New Record added on " & Now & " by " & sUser & ";"
Exit Function
End If

'Set date and current user if the form (current record) has been modified.
MyForm!AuditTrail = MyForm!tbAuditTrail & vbCrLf & vbLf & "Changes made on " & Now & " by " & sUser & ";"

'Check each data entry control for change and record old value of the control.
For Each ctl In MyForm.Controls

'Only check data entry type controls.
Select Case ctl.ControlType
Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox
If ctl.name = "tbAuditTrail" Then GoTo TryNextControl 'Skip AuditTrail field.
'If new and old value do not equal
If ctl.Value <> ctl.OldValue Then
MyForm!AuditTrail = MyForm!tbAuditTrail & vbCrLf & ctl.name & ": Changed From: " & ctl.OldValue & ", To: " & ctl.Value
'If old value is Null and new value is not Null
ElseIf IsNull(ctl.OldValue) And Len(ctl.Value) > 0 Or ctl.OldValue = "" And Len(ctl.Value) > 0 Then
MyForm!AuditTrail = MyForm!tbAuditTrail & vbCrLf & ctl.name & ": Was Previoulsy Null, New Value: " & ctl.Value
'If new value is Null and old value is not Null
ElseIf IsNull(ctl.Value) And Len(ctl.OldValue) > 0 Or ctl.Value = "" And Len(ctl.OldValue) > 0 Then
MyForm!AuditTrail = MyForm!tbAuditTrail & vbCrLf & ctl.name & ": Changed From: " & ctl.OldValue & ", To: Null"
End If
End Select



thanks!!!!
 
Change this part:
Code: 
Dim MyForm As Form
Dim ctl As Control
Dim sUser As String
Set MyForm = Screen.ActiveForm
' sUser = "User: " & UsersID 'You need to identify your users if you are not using Access security with workgroups.
sUser = CurrentUser
to
Code: 
Dim MyForm As Form
Dim ctl As Control
Dim sUser As String
   Set MyForm = Screen.ActiveForm
    sUser = [B]Environ("currentuser")[/B]
 
Uncle Gizmo said:
Try these:
Code: 
 Function fGetWinUserName()
    fGetWinUserName = VBA.Environ("UserName")
End Function      ' fGetWinUserName
 
 Function fGetComputerName()
    fGetComputerName = VBA.Environ("COMPUTERNAME")
End Function      ' fGetComputerName

Note: can use Environ("UserName") or Environ("COMPUTERNAME") but not for all access versions.
Click to expand...

This is even better -
http://www.mvps.org/access/api/api0008.htm
 
Thanks to All!!!!

I used ("UserName") and it worked!!!

You are the best!
 
Someone posted some information recently (within the last six months) on saving user preferences to the local environment, as far as I can remember there is a way of making your own custom local environment property to save your own custom information to.

I don't know if I've got this right! But can anyone else remember what I'm on about? I forgot to save a link.

Cheers Tony
 
Last edited:
Lyn Worked But..

I was having a problem with auditing and foud Lyn's solution. It worked to a point however here is what I am facing.

I have one main form and one sub form. The audit works great on the sub form but on the main form I get this error

"Compile Error: Invalid use of property" When I remove the following code my audit on the subform works but of course not on the main form.

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Form_BeforeUpdate_Err

Call AuditTrail(Me)

Form_BeforeUpdate_Exit:
Exit Sub

Form_BeforeUpdate_Err:
MsgBox Err.Number & " - " & Err.Description
Resume Form_BeforeUpdate_Exit
End Sub

I guess I am asking how can I run an audit on the main form and the subform at the same time?

Thanks
Fen How
 
Help Please?

I may have posted this in another thread by mistake. Forgive me if if this is the case.

I was having a problem with auditing and foud Lyn's solution. It worked to a point however here is what I am facing.

I have one main form and one sub form. The audit works great on the sub form but on the main form I get this error

"Compile Error: Invalid use of property" When I remove the following code my audit on the subform works but of course not on the main form.

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Form_BeforeUpdate_Err

Call AuditTrail(Me)

Form_BeforeUpdate_Exit:
Exit Sub

Form_BeforeUpdate_Err:
MsgBox Err.Number & " - " & Err.Description
Resume Form_BeforeUpdate_Exit
End Sub

I guess I am asking how can I run an audit on the main form and the subform at the same time?

Thanks
Fen How
 
I tried that, it seems to halt on:Call Audit Trail

Private Sub Form_BeforeUpdate(Cancel As Integer)

On Error GoTo Form_BeforeUpdate_Err

Call AuditTrail(Me.Name)

Form_BeforeUpdate_Exit:
Exit Sub

Form_BeforeUpdate_Err:
MsgBox Err.Number & " - " & Err.Description
Resume Form_BeforeUpdate_Exit
End Sub

Fen
 
For those with a mature database and too lazy to add the needed fields, here's a shotgun approach:

Code: 
Private Sub AddAuditTrailField()

Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim fld As DAO.Field
Dim SkipTable As Boolean
Const SystemObject As String = "MSys"

Set db = CurrentDb

For Each tbl In db.TableDefs
    SkipTable = False
    If SystemObject <> Left(tbl.Name, 4) Then
        For Each fld In tbl.Fields
            If fld.Name = "MemAuditTrail" Then
                SkipTable = True
            End If
        Next fld
        
        If Not SkipTable Then
            Set fld = tbl.CreateField("MemAuditTrail", dbMemo)
            tbl.Fields.Append fld
        End If
    End If
Next tbl

Set fld = Nothing
Set tbl = Nothing
Set db = Nothing

End Sub

I say shotgun because it will add field to *all* tables that doesn't have it, whether you need it or not.

Now, if table had a tag property or something like that....
 
Hi All,

I am using infopath to connect to a Access database for an application. I would like to track the changes done to a record using the infopath form. Would like the changes to be tracked in a seperate table and display them using infopath when the record is currently viewed.

access and infopath are 2007 versions.

any ideas on how to do the same.

Thanks much
 
You must log in or register to reply here.

Similar threads

E
Best Practices for User Access Control in Access Databases - Emmanuel Katto Uganda
Replies
3
Views
225
Pat Hartman
P
P
LEAVE MANAGEMENT
Replies
14
Views
388
williamharry
williamharry
N
Solved How to force MS Access display a primary key on the forms after entering data in the first field
Replies
6
Views
238
Pat Hartman
P
M
Solved Form Filter and RecordSource Don't Query New Record
Replies
13
Views
344
Pat Hartman
P
A
Access Audittrail
Replies
5
Views
250
CJ_London
C

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom