using audit code for more than one form 1

[Shaz1]

Good Morning,

I currently have working code which stores changed information into a table. This code uses an autonumber [EquipID] field that applies to an EQUIPMENT table. Now I would like to include the code for other forms with another autonumber field [DwgID]which updates the changes from the DRAWING table.
For the new code I've tried adding
' strSQL = "INSERT INTO AUDITDWG ( DwgID, FieldChanged, FieldChangedFrom, FieldChangedTo, User, DateofHit ) " & _'

This code works but while inserting to the AUDITDWG table I get an error : Invalid SQL statement; Expected 'Delete', 'Insert', 'Procedure', 'Select', or 'Update'.

Any help with this would be awesome.



For each control.
For Each ctlC In frm.Controls
If TypeOf ctlC Is TextBox Or TypeOf ctlC Is ComboBox Then
If ctlC.Value <> ctlC.OldValue Or IsNull(ctlC.OldValue) Then
If Not IsNull(ctlC.Value) Then
strSQL = "INSERT INTO AUDIT ( EquipTagID, FieldChanged, FieldChangedFrom, FieldChangedTo, User, DateofHit ) " & _
strSQL = "INSERT INTO AUDITDWG ( DwgID, FieldChanged, FieldChangedFrom, FieldChangedTo, User, DateofHit ) " & _
" SELECT " & lngID & " , " & _
"'" & ctlC.Name & "', " & _
"'" & ctlC.OldValue & "', " & _
"'" & ctlC.Value & "', " & _
"'" & GetUserName_TSB & "', " & _
"'" & Now & "'"
'Debug.Print strSQL
DoCmd.RunSQL strSQL
End If
End If
End If
Next ctlC

WriteAudit = bOK

exit_WriteAudit:
DoCmd.SetWarnings True
Exit Function

err_WriteAudit:
MsgBox Err.Description
Resume exit_WriteAudit

End Function

 

[Remou]

Leaving out any question of why the same data is being stored twice, you need to double the whole thing:

strSQL = "INSERT INTO AUDIT ( EquipTagID, FieldChanged, FieldChangedFrom, FieldChangedTo, User, DateofHit ) " & _
      " SELECT " & lngID & " , " & _
      "'" & ctlC.Name & "', " & _
      "'" & ctlC.OldValue & "', " & _
      "'" & ctlC.Value & "', " & _
      "'" & GetUserName_TSB & "', " & _
      "'" & Now & "'"
strSQL2 = "INSERT INTO AUDITDWG ( DwgID, FieldChanged, FieldChangedFrom, FieldChangedTo, User, DateofHit ) " & _
      " SELECT " & lngID & " , " & _
      "'" & ctlC.Name & "', " & _
      "'" & ctlC.OldValue & "', " & _
      "'" & ctlC.Value & "', " & _
      "'" & GetUserName_TSB & "', " & _
      "'" & Now & "'"
DoCmd.RunSQL strSQL
DoCmd.RunSQL strSQL2

 

[Shaz1]

HI Remou!

Thanks again, I'm new at VB and trying many things out and of course without a programming background some things are not common sense to me. I will definately use the help you have given me thus far.

I think I understand how this code works now.

Thank you so much!

Shaz1