Audit Trail (1 Viewer)

[Uncle Gizmo]

How does the quick search work?

 

[Bigmo2u]

here is in 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.

 

[Uncle Gizmo]

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 ...............

 

[Bigmo2u]

Rem that line out and I still get the same error message.

here is where it is getting stuck:

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?

 

[Uncle Gizmo]

What happens if you Rem the whole subroutine contents out?

 

[Bigmo2u]

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.

 

[aftershokk]

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!

 

[Uncle Gizmo]

Environ Info

Try these:

 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.

 

[aftershokk]

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!!!!

 

[boblarson]

Change this part:

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

Dim MyForm As Form
Dim ctl As Control
Dim sUser As String
   Set MyForm = Screen.ActiveForm
    sUser = [B]Environ("currentuser")[/B]

 

[boblarson]

Try these:

 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.

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

 

[aftershokk]

Thanks to All!!!!

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

You are the best!

 

[Uncle Gizmo]

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

 

[Uncle Gizmo]

Found it!

http://www.databasejournal.com/features/msaccess/article.php/3605656

 

[fenhow]

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

 

[fenhow]

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

 

[boblarson]

Call AuditTrail(Me)

I may be off here, but should it not be:

Call AuditTrail(Me.Name)

 

[fenhow]

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

 

[Banana]

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

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....

 

[sqaengineer]

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