Results 1 to 4 of 4
  1. #1
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194

    How Do I Identify A Field?

    I'm using the below code from Martin Green @ fontstuff.com. I'd like to add one more field to this but I don't know how. I've tried but I keep getting error 'cannot find the field you are referencing'.



    I'd like to add [CO] (a field within the record) to the audit table.

    Can anyone point out what I'm doing wrong?
    Code:
    Sub AuditChanges(IDField As String, UserAction As String)    On Error GoTo AuditChanges_Err
        Dim cnn As ADODB.Connection
        Dim rst As ADODB.Recordset
        Dim ctl As Control
        Dim datTimeCheck As Date
        Dim strUserID As String
        Dim CO As String
        Set cnn = CurrentProject.Connection
        Set rst = New ADODB.Recordset
        rst.Open "SELECT * FROM Tbl_AuditChanges", cnn, adOpenDynamic, adLockOptimistic
        datTimeCheck = Now()
        strUserID = Environ("USERNAME")
        Select Case UserAction
            Case "EDIT"
                For Each ctl In Screen.ActiveForm.Controls
                    If ctl.Tag = "Audit" Then
                        If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
                            With rst
                                .AddNew
                                ![DateTime] = datTimeCheck
                                ![UserName] = strUserID
                                ![FormName] = Screen.ActiveForm.Name
                                ![Action] = UserAction
                                ![RecordID] = Screen.ActiveForm.Controls(IDField).Value
                                ![FieldName] = ctl.ControlSource
                                ![CO] = Screen.ActiveForm.Controls(CO).Value
                                ![OldValue] = ctl.OldValue
                                ![NewValue] = ctl.Value
                                .Update
                            End With
                        End If
                    End If
                Next ctl
            Case Else
                With rst
                    .AddNew
                    ![DateTime] = datTimeCheck
                    ![UserName] = strUserID
                    ![FormName] = Screen.ActiveForm.Name
                    ![Action] = UserAction
                    ![RecordID] = Screen.ActiveForm.Controls(IDField).Value
                    .Update
                End With
        End Select
    AuditChanges_Exit:
        On Error Resume Next
        rst.Close
        cnn.Close
        Set rst = Nothing
        Set cnn = Nothing
        Exit Sub
    AuditChanges_Err:
        MsgBox Err.Description, vbCritical, "ERROR!"
        Resume AuditChanges_Exit
    End Sub

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Not sure what you're working with, but you never gave the CO string variable a value. If you want to refer to a control with that name:

    ![CO] = Screen.ActiveForm.Controls("CO").Value
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194
    That was it. I didn't have the "" around my field name. Thanks Paul.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problem. You probably don't need the variable, unless I'm missing something.

    Dim CO As String
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Query to identify new record
    By sukhjinder in forum Queries
    Replies: 8
    Last Post: 01-17-2017, 06:34 AM
  2. How to Identify Running Tasks Using VBA
    By EddieN1 in forum Programming
    Replies: 0
    Last Post: 06-15-2016, 04:33 PM
  3. Replies: 3
    Last Post: 01-31-2014, 09:40 AM
  4. Identify which criteria was used
    By ridersjs in forum Queries
    Replies: 2
    Last Post: 11-29-2011, 10:44 AM
  5. Replies: 1
    Last Post: 02-03-2011, 11:19 AM

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
  •  
Other Forums: Microsoft Office Forums