Recoding Module to Direct to Subforms on a Navigation Form (1 Viewer)

CadenP

New member
Local time
Today, 06:42
Joined
Nov 28, 2018
Messages
1
Hi! I recently used austin06's youtube tutorial on how to add an auditing function to my database. It worked initially, however I decided that I'd go ahead and put the front end of the database on a Navigation Form. Upon this update, I lost the ability to use the auditing function. I know why it's doing that, however I am not savvy with coding so if anyone could assist me I'd be extremely grateful!

Below is the module I created that originally worked:
Code:
Option Compare Database

Public Function AuditChanges(RecordID As String, UserAction As String)


Dim DB As Database
Dim RST As Recordset
Dim clt As Control
Dim UserLogin As String

Set DB = CurrentDb
Set RST = DB.OpenRecordset("select * from audittrail", dbOpenDynaset)

UserLogin = Environ("Username")
Select Case UserAction
      Case "New"
          With RST
          .AddNew
          ![DateTime] = Now()
          !UserName = UserLogin
          !FormName = Screen.ActiveForm.Name
          !Action = UserAction
          !RecordID = Screen.ActiveForm.Controls(RecordID).Value
          .Update
          
          End With
          
      Case "Delete"
         With RST
          .AddNew
          ![DateTime] = Now()
          !UserName = UserLogin
          !FormName = Screen.ActiveForm.Name
          !Action = UserAction
          !RecordID = Screen.ActiveForm.Controls(RecordID).Value
          .Update
          End With
          
      Case "Edit"
        For Each clt In Screen.ActiveForm.Controls
        If (clt.ControlType = acTextBox _
                Or clt.ControlType = acComboBox) Then
                If Nz(clt.Value) <> Nz(clt.OldValue) Then
                With RST
                    .AddNew
                    ![DateTime] = Now()
                    !UserName = UserLogin
                    !FormName = Screen.ActiveForm.Name
                    !Action = UserAction
                    !RecordID = Screen.ActiveForm.Controls(RecordID).Value
                    !FieldName = clt.ControlSource
                    !OldValue = clt.OldValue
                    !newValue = clt.Value
                    .Update
                End With
            End If
        End If
    Next clt
End Select
RST.Close
DB.Close
Set RST = Nothing
Set DB = Nothing



End Function

Below is the code that I applied to each individual form:
Code:
Option Compare Database

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
    Call AuditChanges("VendorID", "New")
Else
    Call AuditChanges("VendorID", "Edit")
End If
End Sub

This will sound annoying, but there is also one tab on my Navigation Form that leads into another Navigation Form. I imagine this creates a "Subform on a Subform," issue, but if y'all can help I would very much appreciate it! Thank you!
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 11:42
Joined
Jul 9, 2003
Messages
16,244
I've noticed that your post has yet to receive a reply. I'm posting this message to bump it up the list so that it gets a second view.

I have no experience with navigation forms as the general advice is to avoid them! I would assume that this might be the case for most others here. However, I do know some here have tackled navigation forms with a modicum of success and may be inclined to respond...
 

Users who are viewing this thread

Top Bottom