user tracking table

[scottkar]

Trying to track what users change in access 2013 form. I am new to this and it gives me a syntax error. no results show on table after making changes. thanks in advance for your help. Here is what I copied off of a how to site.


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 (Timestamp, 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.RunSQL strSQL
DoCmd.SetWarnings True
End If
End If
End With
Next
Set ctl = Nothing
Exit Sub
ErrHandler:
MsgBox Err.Description & vbNewLine _
& Err.Number, vbOKOnly, "Error"
End Sub

 

[Ranman256]

You could have told us WHERE the error is.
Vb would stop on that line. (Turn off error handler)

 

[The_Doc_Man]

I'm going to take a leap of logic here - which usually leaves me screaming as I jump to yet another confusion. However, you said

no results show on table after making changes.

You are speaking as though this code actually attempted to run. Therefore, it compiled and the syntax error was not from the VBA compiler. The only part of your code that is NOT analyzed by the compiler is the SQL statement. Therefore, your error must be in that statement.

Looking at the statement, what I see is a proliferation of double quotes using cDQ. The problem is that when cDQ = """" gets executed, you have a single instance of the double-quote character, which you then use in substitution for a string that ALSO is delimited by the double quote character. The cDQ, once substituted in the string in the particular way you did it, TERMINATES the string - prematurely. Hence the most likely place for the syntax error, an incomplete INSERT INTO definition.

This is a case where cDQ should perhaps be cSQ, as cSQ = "'" after which you place the single-quote as the "bracketing" character around your concatenations but continue to use double quotes around the literal fragments that you are concatenating. I think your problem (and it is a common one) is that the double-quote is serving two purposes in the same line and thus is confusing the SQL parser. Which is why it is possible to have single-quote delimitation in SQL statements.