VBA - Calling module results in "expected variable procedure not module"

Question

Right. So I have decided that when I move to splitting the FE/BE of my database I want to make use of 'Allen Brown's Error Handling in VBA' as this will allow the process to stop, inform user that action failed and log the error automatically for me to review at a later date.

Only problem is I keep getting the error "expected variable procedure not module"

Now I did adapt Allen's code slightly so instead of being called 'LogError' I've changed all that to 'LogAutoErrors'

This is the first sub that I have added the call code that is throwing the above error

Private Sub ImportAttendees_Click()
On Error GoTo ImportAttendees_Click_Err

Dim SelectedFile    As String
Dim FilePicker      As FileDialog
Dim SQLdelete       As String

Set FilePicker = Application.FileDialog(msoFileDialogFilePicker)
FilePicker.AllowMultiSelect = False
FilePicker.Filters.Add "Excel", "*.xls*", 1
FilePicker.InitialFileName = "C:\Users\"
FilePicker.Title = "Select New Attendee List Location..."
FilePicker.Show

If FilePicker.SelectedItems.Count <> 0 Then
    SelectedFile = FilePicker.SelectedItems(1)

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tbl_STG_AttendeeImport", SelectedFile, True

    MsgBox prompt:="Data Staged - Ready For Import", buttons:=vbInformation, Title:="Data Loaded"
End If

Me.Refresh

Exit Sub

ErrorHandler:

ImportAttendees_Click_Err: ' Label to jump to on error.
MsgBox prompt:="E-Link encountered an error when processing the last action. E-Link has cancelled the last action and the error has been logged with the system administrator", buttons:=vbInformation, Title:="Database Process Error"
Call logAutoErrors(Err.Number, Err.Description, "ImportAttendees_Click()")
Resume Exit_ImportAttendees_Click
End Select
End Sub

The expected result is whenever I get errors (e.g. my favorite run-time error 3061) it inserts into the error table and cancels actions.

EDIT: Here is the amended Allen Browne code, only thing I changed was one field name and the module name

Function logAutoErrors(ByVal lngErrNumber As Long, ByVal strErrDescription As String, _
    strCallingProc As String, Optional vParameters, Optional bShowUser As Boolean = False) As Boolean
On Error GoTo Err_logAutoErrors
    ' Purpose: Generic error handler.
    ' Logs errors to table "tLogError".
    ' Arguments: lngErrNumber - value of Err.Number
    ' strErrDescription - value of Err.Description
    ' strCallingProc - name of sub|function that generated the error.
    ' vParameters - optional string: List of parameters to record.
    ' bShowUser - optional boolean: If False, suppresses display.
    ' Author: Allen Browne, allen@allenbrowne.com

    Dim strMsg As String      ' String for display in MsgBox
    Dim rst As DAO.Recordset  ' The tLogError table

    Select Case lngErrNumber
    Case 0
        Debug.Print strCallingProc & " called error 0."
    Case 2501                ' Cancelled
        'Do nothing.
    Case 3314, 2101, 2115    ' Can't save.
        If bShowUser Then
            strMsg = "Record cannot be saved at this time." & vbCrLf & _
                "Complete the entry, or press  to undo."
            MsgBox strMsg, vbExclamation, strCallingProc
        End If
    Case Else
        If bShowUser Then
            strMsg = "Error " & lngErrNumber & ": " & strErrDescription
            MsgBox strMsg, vbExclamation, strCallingProc
        End If
        Set rst = CurrentDb.OpenRecordset("tbl_ADM_ErrorLog", , dbAppendOnly)
        rst.AddNew
            rst![ErrNumber] = lngErrNumber
            rst![ErrDescription] = Left$(strErrDescription, 255)
            rst![ErrDate] = Now()
            rst![CallingProc] = strCallingProc
            rst![UserID] = TempVars!AUID
            If Not IsMissing(vParameters) Then
                rst![Parameters] = Left(vParameters, 255)
            End If
        rst.Update
        rst.Close
        LogError = True
    End Select

Exit_logAutoErrors:
    Set rst = Nothing
    Exit Function

Err_logAutoErrors:
    strMsg = "An unexpected situation arose in your program." & vbCrLf & _
        "Please write down the following details:" & vbCrLf & vbCrLf & _
        "Calling Proc: " & strCallingProc & vbCrLf & _
        "Error Number " & lngErrNumber & vbCrLf & strErrDescription & vbCrLf & vbCrLf & _
        "Unable to record because Error " & Err.Number & vbCrLf & Err.Description
    MsgBox strMsg, vbCritical, "logAutoErrors()"
    Resume Exit_logAutoErrors
End Function

HackSlash · Accepted Answer

This can happen if you have a Function or Sub that shares the same name as the Module. Every name must be unique. Even the Modules.

EXAMPLE: Notice the modules on the left. That is where your problem will be found.

You had some strange things going on with your line labels. You also had an End Select with no select statement.

Try this:

Private Sub ImportAttendees_Click()
    On Error GoTo ErrorHandler

    Dim SelectedFile    As String
    Dim FilePicker      As FileDialog
    Dim SQLdelete       As String

    Set FilePicker = Application.FileDialog(msoFileDialogFilePicker)
    With FilePicker
        .AllowMultiSelect = False
        .Filters.Add "Excel", "*.xls*", 1
        .InitialFileName = "C:\Users\"
        .Title = "Select New Attendee List Location..."
        .Show
    End With

    If FilePicker.SelectedItems.Count <> 0 Then
        SelectedFile = FilePicker.SelectedItems(1)

        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tbl_STG_AttendeeImport", SelectedFile, True

        MsgBox prompt:="Data Staged - Ready For Import", Buttons:=vbInformation, Title:="Data Loaded"
    End If

    Me.Refresh

    Exit Sub

ErrorHandler:

    MsgBox prompt:="E-Link encountered an error when processing the last action. E-Link has cancelled the last action and the error has been logged with the system administrator", Buttons:=vbInformation, Title:="Database Process Error"
    logAutoErrors Err.Number, Err.Description, "ImportAttendees_Click()"
    Resume
End Sub

VBA - Calling module results in "expected variable procedure not module"

Answers (2)

Related Questions

VBA - Calling module results in &quot;expected variable procedure not module&quot;

Answers (2)

Related Questions

VBA - Calling module results in "expected variable procedure not module"