SQL Field Error (1 Viewer)

[reclusivemonkey]

I've created the following to act as an audit trail. All the values seem to be fine in the SQL; however, rather than writing the correct field name into the AllocationAuditField, I get the Value of the field. Can anyone spot where I've gone wrong? tblAllocationsAudit has

AllocationsAuditID - Autonumber
AllocationsAuditCCID - Text
AllocationsAuditField - Text
AllocationsAuditOldValue - Text
AllocationsAuditNewValue - Text
AllocationsAuditDateStamp - Date
AllocationsAuditUserName - Text

Private Sub Form_BeforeUpdate(Cancel As Integer)
' Audit trail macro
    Dim frmObj As Object
    Dim AAField As String
    Dim AAOldValue As String
    Dim AANewValue As String
    Dim AADateStamp As Date
    Dim AAUserName As String
    
    For Each frmObj In Forms!frmCostCentresManagement
        If Left(frmObj.Name, 10) = "Allocation" Then
                If frmObj.Value <> frmObj.OldValue Then
                        AAField = frmObj.Name
                        AAOldValue = frmObj.OldValue
                        AANewValue = frmObj.Value
                        AADateStamp = Date
                        AAUserName = Environ("Username")
                        MsgBox AAField
                        MsgBox AAOldValue
                        MsgBox AANewValue
                        MsgBox AADateStamp
                        MsgBox AAUserName
                        strSQL = "INSERT INTO tblAllocationsAudit (AllocationsAuditCCID, AllocationsAuditField, "
                        strSQL = strSQL + "AllocationsAuditOldValue, AllocationsAuditNewValue, AllocationsAuditDateStamp, "
                        strSQL = strSQL + "AllocationsAuditUserName) VALUES ("
                        strSQL = strSQL + AllocationCostCentreID.Value & ", "
                        strSQL = strSQL + AAField & ", "
                        strSQL = strSQL + AAOldValue & ", "
                        strSQL = strSQL + AANewValue & ", "
                        strSQL = strSQL + "#" & AADateStamp & "#, "
                        strSQL = strSQL + AAUserName & ")"
                        Debug.Print strSQL
                        DoCmd.RunSQL (strSQL)
                    Else
                End If
            Else
        End If
    Next frmObj

End Sub

 

[nateobot]

What happens if you surround it in apostrophes like below:

strSQL = strSQL & "'" & AAField & "', "

Likely won't change, but what it seems to be doing is pulling the default property for AAField (in this case Value), rather than entering the actual text (its name).

 

[reclusivemonkey]

Likely won't change,

Pah, rubbish! That was exactly the problem! I added the single quotes and I got just the value I wanted. Thanks a lot Nate ;-)