How to intercept error messages.

[exaccess]

Hello to All,
I have written the following code for checking whether a query exists.

Function QueryExists(strQueryName As String) As Boolean
'----------------------------------------------------------------------------------------
'   Checks whether a query already exists
'----------------------------------------------------------------------------------------
    Dim db As DAO.Database
    Dim tdf As DAO.QueryDef
    On Error GoTo err_handler
    Set db = CurrentDb
    Set tdf = db.QueryDefs(strQueryName)
    QueryExists = True
QueryExists_Exit:
    Exit Function
err_handler:
    If Err.Number = 3265 Then
        QueryExists = False
        Resume QueryExists_Exit
    Else
        MsgBox Err.Description, vbExclamation, "Error #: " & Err.Number
        QueryExists = False
        Resume QueryExists_Exit
    End If
End Function

Here I try to intercept the error message in vain. What am I missing here? Can somebody help please?

 

[Frothingslosh]

Edited:

The code you posted works fine for me. What seems to be the problem?

Also, the 'code you've written' looks strangely identical to the code SOS posted here: http://www.access-programmers.co.uk/forums/showthread.php?t=190747

 

[exaccess]

Edited:

The code you posted works fine for me. What seems to be the problem?

Also, the 'code you've written' looks strangely identical to the code SOS posted here: http://www.access-programmers.co.uk/forums/showthread.php?t=190747

The problem is the error message is displayed by the system ie I can not stop it and then the program hangs there waiting for a response.
You are right the code looks similar. I may have taken it from somewhere I do not remember, but definitely not from that link.

 

[bob fitz]

So what is the error message that you get. I get no message. The function runs when called and returns an appropriate boolean value.

 

[exaccess]

So what is the error message that you get. I get no message. The function runs when called and returns an appropriate boolean value.

Run-time error '3265':

Item not found in this collection.

 

[gemma-the-husky]

its really strange

your code is saying

if the error is 3265, then just return false - otherwise, report the error
that you are actually getting. But what is happening is that you are getting the report for error 3265.

I would change err.number to just err - and double check that the top bit is correct.

 err_handler:
    If Err = 3265 Then
        QueryExists = False
        Resume QueryExists_Exit
    Else
        MsgBox Err.Description, vbExclamation, "Error #: " & Err.Number
        QueryExists = False
        Resume QueryExists_Exit
    End If
End Function

 

[Frothingslosh]

If gemma's suggestion doesn't work, may I ask what version of Access you're using?

You can also try this code. It doesn't rely on error trapping, but it will also use a little more processing power and run a little more slowly, although you shouldn't honestly notice a difference unless you have a ton of queries.

Public Function QueryExists(ByVal strQueryName As String) As Boolean
 
On Error GoTo QueryExists_Err
 
'Declarations
Dim qdf As DAO.QueryDef
 
    'Defaults
    QueryExists = False     'Not usually necessary, but I like to be explicit
 
    'Loop through the entire QueryDefs collection.
    For Each qdf In CurrentDb().QueryDefs
 
        'Check each query name against strQueryName.
        If qdf.Name = strQueryName Then
 
            'Set QueryExists to TRUE.
            QueryExists = True
 
            'Leave the loop.
            Exit For
        End If
    Next qdf
 
QueryExists_Exit:
 
    'Clean up.
    If Not qdf Is Nothing Then Set qdf = Nothing
 
    'Terminate.
    Exit Function
 
QueryExists_Err:
 
    MsgBox "Error Number: " & Err & vbCrLf & "Description: " & Err.Description
    Resume QueryExists_Exit
 
End Function

 

[exaccess]

Both cases work. Many thanks to both of you.

 

[ChrisO]

This will also happen if error handling is set to Break on All Errors.

Chris.