Call module

[Gareth Newman]

Hi,
I am using a module called "AuditTrail" for recording changes made to records.
It works fine on primary forms with the code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Call AuditTrail(Me, Job_number)

End Sub

with job_number being the primary key index in the linked table.

The form has a number of subforms however that relate to different tables and I cannot get the module to work from these.
I am sure it is just a simple syntax issue but having tried a number of options, I cannot find the answer. Any help would be appreciated.

Gareth

 

[Gasman]

Forms: Refer to Form and Subform properties and controls

 

[Josef P.]

In the form used as a subform, you can also respond to BeforeUpdate.
The call must then probably be adjusted - Job_number is probably the primary key, which will be different in the subform.

BTW:

I am using a module called "AuditTrail" for recording changes made to records.

Call invokes a procedure in a module.

 

[Gareth Newman]

In the form used as a subform, you can also respond to BeforeUpdate.
The call must then probably be adjusted - Job_number is probably the primary key, which will be different in the subform.

BTW:

Call invokes a procedure in a module.

Tried that with the same code bar the primary key which is "index" in the subform.

>Private Sub Form_BeforeUpdate(Cancel As Integer)
>Call AuditTrail(Me, Index)
>End Sub

When this line executes, I get a run time error 13 "Type mismatch". While the line is highlighted yellow however, if i hover over index, it has the right index number.

 

[Gasman]

Type mismatch means what is says.
You are supplying the incorrect data type for the function.
What is the data type of Job number and Index?

I would have thought Index is a reserved name and should be avoided?
And it is

List of reserved words in Access - Microsoft 365 Apps

Lists the reserved words that you should not use in field, object, and variable names. It is not practical to provide a list of all reserved words, such as built-in function names or Access user-defined names.

learn.microsoft.com

 

[adhoustonj]

What is the code for the AuditTrail?

 

[MajP]

If your audit trail function argument is declared as integer and index is long you will get an error eventhough both are numeric.

 

[Gareth Newman]

What is the code for the AuditTrail?

The AuditTrail code is:

Option Compare Database

Const cDQ As String = """"

Public Sub AuditTrail(frm As Form, RecordId As Control)
' Track changes to data
' 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 = acComboBox Or .ControlType = acTextBox Then
If .Value <> .OldValue Then
varBefore = .OldValue
varAfter = .Value
strControlName = .Name
' BUILD INSERT INTO statement
strSQL = "INSERT INTO " _
& "Audit (EditDate, 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
'ElseIf .ControlType = acSubform Then
'AuditTrail.frm(ctl.Name).Form
End If


End With
Next
Set ctl = Nothing
Exit Sub

ErrHandler:
MsgBox Err.Description & vbNewLine _
& Err.Number, vbOKOnly, "Error"
End Sub

 

[MajP]

Type

Call AuditTrail(Me, Me.

and tell me if intellisense comes up after "me.", and see "index" is a choice. My guess is you do not (and should not) have a control called index. If you do have a control called index rename it to something else and use the new name instead.