Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2009

    Unanswered: Track Changes in Form with sub-form & multiple tabs


    I've made a multi user (4 end users) database in Access 2000. I've made data tables, reference tables etc which sits on a shared drive and I've made individual front ends for the end users which gives them specific views of the data.
    I'd like to track all the changes made to a record which works find with the following code :
    Option Compare Database
    Const cDQ As String = """"
    Sub AuditTrail(frm As Form, recordid As Control)
      'Track changes to data.
      'recordid identifies the pk field's corresponding
      'control in frm, in order to id record.
      Dim ctl As Control
      Dim varBefore As Variant
      Dim varAfter As Variant
      Dim strControlName As String
      Dim strSQL As String
      On Error GoTo ErrHandler
      'Get changed values.
      For Each ctl In frm.Controls
        With ctl
        'Avoid labels and other controls with Value property.
        If .ControlType = acTextBox Then
          If .Value <> .OldValue Then
            varBefore = .OldValue
            varAfter = .Value
            strControlName = .Name
            'Build INSERT INTO statement.
            strSQL = "INSERT INTO " _
               & "Audit (EditDate, User, RecordID, SourceTable, " _
               & " SourceField, BeforeValue, AfterValue) " _
               & "VALUES (Now()," _
               & cDQ & Environ("username") & cDQ & ", " _
               & cDQ & recordid.Value & cDQ & ", " _
               & cDQ & frm.RecordSource & cDQ & ", " _
               & cDQ & .Name & cDQ & ", " _
               & cDQ & varBefore & cDQ & ", " _
               & cDQ & varAfter & cDQ & ")"
            'View evaluated statement in Immediate window.
            Debug.Print strSQL
            DoCmd.SetWarnings False
            DoCmd****nSQL strSQL
            DoCmd.SetWarnings True
          End If
        End If
        End With
      Set ctl = Nothing
      Exit Sub
      MsgBox Err.Description & vbNewLine _
       & Err.Number, vbOKOnly, "Error"
    End Sub
    I call the above mentioned procedure in a single form with :
    in event procedure : Before Update:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
      Call AuditTrail(Me, [Shipper ID])
    End Sub
    Now here's the problem, the above mentioned code seems to work OK when using a single form but as soon as I add in a SubForm (in which ideally I want to track changes made too) everything comes to a halt with the mention of :
    "The expression Before Update you entered as the event property setting produced the following error: A problem occurred while Microsoft Office Access was communicating with the OLE server or ActiveX Control.
    * The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure].
    * There may have been an error evaluating the function, event, or macro."

    Any help on this would be GREATLY appreciated!


  2. #2
    Join Date
    Oct 2009
    wow that's an elaborate audit...

    if you mean that an update of the main form barfs - because the subform exists - then I will hazard the guess that you need to expand the logical definition of your comment:

    'Avoid labels and other controls with Value property.

    On the other hand if you mean it barfs when the subform is updated - that would be a different issue...

  3. #3
    Join Date
    Nov 2009
    Hi - thanks for the quick response - yeah - I dont even get a chance to update the subform so its just because the subform exists....
    I've played around with the "'Avoid labels and other controls with Value property." but with no luck....
    do you have any advise?

  4. #4
    Join Date
    Dec 2004
    Madison, WI
    You could just design an append query to run (to append to a backup or testing table) before opening the form and then utilize any other events to run other append/update type queries before the user saves the record. This sort of gives you a before/after snapshot picture with the ability to put in specific record changes.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Nov 2009
    Hi pkstormy,

    I've tried to append query after the record before update/before load but unfortunatly it appends all 1800+ records in the table...
    I've attached a watered-down version of the database (3 tables, 5 forms and 2 modules), as you open the database and try to update a field and click in the sub-form the error msg appears....
    I thought i managed to get a work around by using 2 modules but that didn't do much....
    Any help would be appreciated!
    Attached Files Attached Files

  6. #6
    Join Date
    Dec 2004
    Madison, WI
    You have to add criteria to the append query so that you ONLY append the record on the form (ie. under your autonumber column in the append query, add =Forms!MyDataFormName!RecordID for criteria.)
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Tags for this Thread

Posting Permissions

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