Complie error:User defined type not defined

[aman]

Hi Guys

I have been trying to add audit trail functionality to my database.

I have written following code on the Add button on form:

If Me.NewRecord Then
        Call AuditChanges(me.txtGenMeetingID, "NEW")
    Else
        Call AuditChanges(me.txtGenMeetingID, "EDIT")
End If

The following code is present in a module. I am getting compile error at the following Red line.

[COLOR=red]Sub AuditChanges(IDField As Number, UserAction As String)
[/COLOR]   On Error GoTo AuditChanges_Err
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim ctl As Control
    Dim datTimeCheck As Date
     Dim strUserID As String
    'Set cnn = CurrentProject.Connection
    Set rst = New ADODB.Recordset
    'rst.Open "SELECT * FROM tblAuditTrail", cnn, adOpenDynamic, adLockOptimistic
    Set rst = CurrentDb.OpenRecordset("tblAuditTrail")
    datTimeCheck = Now()
     strUserID = Environ("USERNAME")
    Select Case UserAction
        Case "EDIT"
            For Each ctl In Screen.ActiveForm.Controls
                If ctl.Tag = "C" Then
                    If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
                        With rst
                            .AddNew
                            ![DateTime] = datTimeCheck
                            ![UserName] = strUserID
                            ![FormName] = Screen.ActiveForm.Name
                            ![Action] = UserAction
                            ![RecordID] = Screen.ActiveForm.Controls(IDField).Value
                            ![FieldName] = ctl.ControlSource
                            ![OldValue] = ctl.OldValue
                            ![NewValue] = ctl.Value
                            .Update
                        End With
                    End If
                End If
            Next ctl
        Case Else
            With rst
                .AddNew
                ![DateTime] = datTimeCheck
                ![UserName] = strUserID
                ![FormName] = Screen.ActiveForm.Name
                ![Action] = UserAction
                ![RecordID] = Screen.ActiveForm.Controls(IDField).Value
                .Update
            End With
    End Select
AuditChanges_Exit:
    On Error Resume Next
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
    Exit Sub
AuditChanges_Err:
    MsgBox Err.Description, vbCritical, "ERROR!"
    Resume AuditChanges_Exit
End Sub

 

[Minty]

I think you have to declare a sub as either private or public?

 

[Galaxiom]

Number is not a standard type.

Use Integer or Long

 

[Galaxiom]

I think you have to declare a sub as either private or public?

If I remember correctly, a scope-unspecified Sub in an Object module is Private by default and Public in a Standard Module.

 

[aman]

I have changed the data type from number to integer but getting type mismatch error.
the textbox on the form named txtGenMeetingID stores data from the field GenMeetingID in the table and this field has autonumber data type.

Any help guys on this one ?

 

[aman]

even if I change the data type to variant then still same error :

Public Sub AuditChanges(IDField As Integer, UserAction As String)
    On Error GoTo AuditChanges_Err

Public Sub AuditChanges(IDField As variant, UserAction As String)
    On Error GoTo AuditChanges_Err

 

[Gasman]

I would have thought you would need to use Long?

 

[JHB]

Only for testing suppose try using a hard coded value.

Call AuditChanges(10, "New")

 

[aman]

Guys, The error was in the other part of the function. I have fixed that. But My following code (written in the form) doesn't seem to call the function in the module. Can anyone please help me in this?

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
        Call AuditChanges(Me.txtGenMeetingID, "NEW")
    Else
        Call AuditChanges(Me.txtGenMeetingID, "EDIT")
End If
   
End Sub
Private Sub Form_AfterDelConfirm(Status As Integer)
    If Status = acDeleteOK Then Call AuditChanges("txtGenMeetingID", "DELETE")
End Sub

 

[Minty]

Put a message box in your function right at the beginning

msgbox "Function called ID:"& IDField  & " Action:" & UserAction

You will see if it's actually being called.

 

[The_Doc_Man]

I have sometimes seen problems where the in-line IF doesn't appear to correctly call a sub like that. Change it to a block IF.

Private Sub Form_AfterDelConfirm(Status As Integer)
    If Status = acDeleteOK Then
        Call AuditChanges("txtGenMeetingID", "DELETE")
    End If
End Sub

The generated code in theory is the same size and runs equally fast. I don't know that I can tell you why it happens, but I have seen this problem myself and that little change to the code fixed it.