Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2005
    Posts
    240

    Unanswered: Help on Audit Log script

    Ok I have got a script for creating an audit log, which I've found and have been changing as I go along.
    But what I would like it to do is store the information it gets into another table with the same fields. Although I want it to check for a record first in the table, if it's not there add a new record, but if it is edit the record.

    Although I can't get this working and it returns the error:

    Run-time error '3021': No current record.
    This is my code:

    Code:
        Dim MyForm As Form
        Dim ctl As Control
        Dim strUser As String
        Dim dbs As Database
        Dim rs As DAO.Recordset
        Dim strSQL As String
        Set MyForm = Screen.ActiveForm
        Set dbs = CurrentDb
        strUser = fOSUserName
    
    ' Set date and current user if form has been updated.
        MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & "Changes made on " & Now & " by " & strUser & ";"
    
    ' If new record, record it in audit trail and exit sub.
        If MyForm.NewRecord = True Then
           MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & "New Record in " & Form.Caption & " on " & Form_frmOrganisation.EmpName & ";"
           Exit Sub
        End If
    
    ' Check each data entry control for change and record old value of 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 Is Me!Updates Then  'do nothing
                 
                 ElseIf ctl.Value <> ctl.OldValue Then
                    MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & "     " & ctl.Name & ": Changed from: " & ctl.OldValue & " to: " & ctl.Value
                 End If
           End Select
    TryNextC:
             Next ctl
             
    ' Add user and time updated to Organisation form
        If Not Me.NewRecord Then
        Me.Last_Updated_By.Value = strUser
        End If
        If Not Me.NewRecord Then
        Me.Date_Updated.Value = Date
        End If
        If Not Me.NewRecord Then
        Me.Time_Updated.Value = Time()
        End If
        
        strSQL = "SELECT * FROM dbo_tblAudit_Log"
        Set rs = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
        With rs
            .FindFirst "[Emp_ID] = Me.[Emp_ID]"
            If Not .NoMatch Then
            .AddNew
            !Emp_ID = Me.[Emp_ID]
            !Last_Updated_By = Me.[Last_Updated_By]
            !Date_Updated = Me.[Date_Updated]
            !Time_Updated = Me.[Time_Updated]
            !Updates = Me.[Updates]
            .Update
            Else
            .Edit
            !Emp_ID = Me.[Emp_ID]
            !Last_Updated_By = Me.[Last_Updated_By]
            !Date_Updated = Me.[Date_Updated]
            !Time_Updated = Me.[Time_Updated]
            !Updates = Me.[Updates]
            .Update
            End If
        End With
    It seems to be the line .FindFirst that it doesn't like.

    Also is the code I'm using slightly old skool, if you know what I mean, or pretty valid for todays standards?

  2. #2
    Join Date
    Jun 2005
    Posts
    102
    Hi Kev,

    I too want to create a working audit log, that logs the user name and the records that user edited. But I do not know how to approach this because I am a noob when it comes to VB. Is it possible if I can get a sample of your form, logs, tables, etc. To see, how your logging method is. Thanks.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    If you are considering using a audit log there are several issues you need to be aware of. The triggering of events in Access isn't neccesarily intuitive. You need to handle delete, edits and inserts differently. when you insert a record you also edit a record

    A delete is easy, providing you have a unique key to the record to be deleted - but then your physical design has that already doesn't it.

    You need to be able to separate where your users have inserted a record using copy & paste.

    You need to resolve how you handle relationships if you have set a delete cascase or cascade update then you need to manually intervene to identify what records will be changed in the child tables.

    One trick I have deployed over the years is to write the audit log as an sql statement. The reason - it means you have a self documenting method of updating the tables based on a known 'good' backup and the audit trail upto and including where it went wrong. it also means that you can back out any changes made by a specific person or on a specific date. Originally this was triggered to provide a means of replicating the db before repliction became available. It also helped proting data between online and back end data.

  4. #4
    Join Date
    Sep 2005
    Posts
    240
    Hi rodrunner

    I can give you a link to a thread on another forum, which has a sample database that you can download (there are two one for Access 2000, and one slightly changed for 2003).

    This method however only seems to work 100% on seperate forms, rather than forms with subforms. If you use this you will need to include a memo field in your table that will log the audit, and place this on the form. If you get into any problems then I'll try and help you out.

    Link: http://www.access-programmers.co.uk/...ad.php?t=44231

    Edit: I've managed to get it to work in subforms and across a tab control set on a form, let me know if you need help on that.

    healdem:

    Interesting idea, might look into it.
    Last edited by KevCB226; 10-21-05 at 10:42.

  5. #5
    Join Date
    Nov 2005
    Posts
    1
    I need to get it working on a subform. Can you please guide me? I have beens truggling since a week on this one..

  6. #6
    Join Date
    Sep 2005
    Posts
    240
    Yeah sure, just so I know how your forms are set up. Have you got your main form with the audit trail textbox on, and then a subform without the audit trail text box on?

    Could you also post the code you've got for your main form, so I can have a look at it.

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •