Insert Primary key value into audit trail table

[codingal]

Hi experts,

I am trying to implement "audit trail" in my database to track data changed in some subforms and sub-subforms. I need to track the value for the subform prmary key so I will know what row of the subform data was changed. But I am having difficult to get the primary key value into the table. I keep getting error "Object variable or With block variable not set". Please help! below is my code:

Public Function WriteAudit(frm As Form, lngID As Long) As Boolean
On Error GoTo err_WriteAudit

Dim ctlC As Control
Dim idx As Index
Dim strSQL As String
Dim bOK As Boolean
Dim strOldVal
Dim db As Database
Dim rst As Recordset

bOK = False

DoCmd.SetWarnings False

' For each control.
For Each ctlC In frm.Controls
If TypeOf ctlC Is TextBox Or TypeOf ctlC Is ComboBox Or TypeOf ctlC Is CheckBox Then
If ctlC.Value <> ctlC.OldValue Or (IsNull(ctlC.OldValue) Or IsNull(ctlC.Value)) Then
If Not IsNull(ctlC.Value) Or Not IsNull(ctlC.OldValue) Then
strSQL = "INSERT INTO tblAudit ( StudentID, TabFormChanged, PrimaryField, FieldChanged, FieldChangedFrom, FieldChangedTo, UpdateUser, DateofChange ) " & _
" SELECT " & lngID & " , " & _
"'" & frm.Name & "', " & _
"'" & idx.Primary & "', " & _
"'" & ctlC.Name & "', " & _
"'" & ctlC.OldValue & "', " & _
"'" & ctlC.Value & "', " & _
"'" & CurrentUser() & "', " & _
"'" & 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

Thanks!

 

[Remou]

You do not set this in your code:

idx.Primary

 

[codingal]

Thanks Remou, but how do I correct it and get the primary key's value?

 

[Remou]

Which primary key? Forms can be based on more than one table and primary keys belong to tables. You may be able to do something with this:

Set rs = frm.RecordsetClone
For Each fld In rs.Fields
    If InStr(tdflist, rs(fld.Name).SourceTable) = 0 Then
        Set tdf = db.TableDefs(rs(fld.Name).SourceTable)
        
        For i = 0 To tdf.Indexes.Count - 1
            If tdf.Indexes(i).Primary = True Then
                Debug.Print tdf.Indexes(i).Fields
            End If
        Next
        tdflist = tdflist & "," & rs(fld.Name).SourceTable
    End If
Next