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

2
votes

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
3
vbams-accesserror-handling
You shouldn't have problems putting apostrophe's in text fields. You should post your update code as well. – Zaider
@Zaider I've added the code for the update button. It may be the way that I've written the code that causes me not to be able to use apostrophes - they seem to be interfering with these sections: = '" & Me.txtTele & "'" = '" & Me.txtDescription & "'" – Alex
The problem with doing your SQL statements like that is once you hit an apostrophe it changes the SQL. You could look into using paramaterized queries which would be best or used a function like Public Function RQuote(s As Variant) As Variant RQuote = Replace(s, """", """""") End Function That would replace them and allow users to use apostrophe's. – Zaider

3 Answers

4
votes

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.

1
votes

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.

0
votes

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