CODEWITHSUNDEEP
vbams-accesserror-handling
Alex
Alex

Reputation: 283

Microsoft Access VBA - Is there a way to create a public sub that handles a specific error code?

Is it possible to create a public sub that handles a specific error code? I have multiple text fields and update buttons on my form, which are used to replace text in certain tables with the strings from the text boxes.

However, if the string in the text box contains an apostrophe, the error code '3075' is given. I don't want to write an error handler for each sub; is it possible to create a public sub and call it if error 3075 is encountered?

E.G - this is what the public sub might look like:

Public Sub ErrHandler()
If Err.Number = 3075 Then
MsgBox("You can't use apostrophes in the text box")
End If
End Sub

Then something like this will go in each sub that has a textbox:

On Error Call ErrHandler

I have tried the above code, it doesn't work. Was just wondering if anybody has any other ideas or workarounds for this?

EDIT:

Here is the code for the update button. It replaces the selected item in the listbox(TelephoneList) with the strings from the text boxes ('txtTele' and 'txtDescription;)

The listbox displays items from the 'LPA Telephone' Table.

Private Sub UpdateTelephoneButton_Click()
If Not IsNull(txtTele) And Not IsNull(TelephoneList.Column(0)) Then
    CurrentDb.Execute "UPDATE [LPA Telephone] " & _
    "SET [Telephone] = '" & Me.txtTele & "'" & _
    ", [Description] = '" & Me.txtDescription & "'" & _
    "WHERE [ID] = " & Int(TelephoneList.Column(0)) & ""

    TelephoneList.Requery
End If
End Sub

Upvotes: 2

Views: 204

Answers (3)

david
david

Reputation: 2638

Another standard method is to subclass the err object, like this:

(in global module)

Public Err As ErrObject

(at startup)

Set Err = New ErrObject

An example I find on the web is that given in the book Advanced Microsoft Visual Basic. I won't repeat the full text: class ErrObject starts like this:

Private e() As ErrObjectState

Private Type ErrObjectState
 Description As String
 HelpContext As Long
 HelpFile As String
 Number As Long
End Type

Public Property Get Description() As String
 Description = VBA.Err.Description
End Property

Upvotes: 0

iDevlop
iDevlop

Reputation: 25272

As help specifies it clearly: you can only use

On Error GoTo line  
On Error Resume Next  
On Error GoTo 0

So you could try something like this:

Sub someSubOrFunction()

on error goto hell

doSomeStuff

adios:
exit sub

hell:
Call ErrHandler(Err.Number, "someSubOrFunction" )
resume adios  'label or Next or nothing
end sub

Personally I don't like this approach because the benefits are minimal and you lose a lot in flexibility. I prefer to write my error handling case by case, and eventually use a generic function (or a class) just for error logging.

Upvotes: 1

Ralph
Ralph

Reputation: 9444

Why don't you pass Err.Number as a parameter to the Sub? Then it should work just fine.

On Error Goto ErrorHandler
...yourCode
...yourCode


Exit Sub

ErrorHandler:
Call ErrHandler(Err.Number)
Resume Next

and then

Public Sub ErrHandler(ErrorNumber as Long)

If ErrorNumber = 3075 Then
    MsgBox("You can't use apostrophes in the text box")
End If

End Sub

Note: the above code is not tested. I just wrote it from the top of my head and might need some tweaking. Yet, I hope the basic idea is clear.

Upvotes: 4

Related Questions