Proper syntax for raising an error that a function returns

Question

This function returns either an error number, or 0 if it ran smoothly.

Public Function DeleteTable(tblName As String) As Long
    On Error Resume Next
    DoCmd.DeleteObject acTable, tblName
    DeleteTable = Err
End Function

This sub works fine...

Private Sub cmdRefresh_Click()
    Dim ReturnedErr As Long
    ReturnedErr = DeleteTable("tblFoo")
    If ReturnedErr Then Err.Raise ReturnedErr

    'do stuff
End Sub

But is there a way to write it where I don't have to pass the returned value into a new variable? Seems clunky. Is something like this possible?

Private Sub cmdRefresh_Click()
    err.raise DeleteTable("tblFoo")
    '(doesn't work because an err.raise of 0 ... raises an error)

    'do stuff
End Sub

Answer 1

Option Explicit

Public Sub DeleteTable(tblName As String) ' a Sub is just fine
    On Error Resume Next
    Err.Raise 1 ' <- just for testing: any error, other than 0
End Sub
Public Sub test()
    DeleteTable ("tblFoo") ' <- don't return the err.number in DeleteTable
    If Err.Number Then     ' <- the error of the last statement will be preserved
       'Err.Raise Err.Number ' <- You can raise the error now, it is always a valid error number here
       'Or show a better message than MS does:
       MsgBox "Error(" & Err.Number & ") " & Err.Description & vbCrLf & vbCrLf & "in '" & Mid(Application.CodeDb.Name,1+InstrRev(Application.CodeDb.Name,"\")) & "', Function 'DeleteTable'"
    Else
       'do stuff
    End If
End Sub