DLookup produces runtime error 2471 - No error trapping that I have tried works

Question

The exact wording of the error displayed is "Run-time error: The expression you entered as a query perameter produced this error:" and it then gives "ThisCode"

Function GetCageFileNumber(ThisCode As String) As Integer
    
    Dim sSQL As String
    
    If IsError(DLookup("[Sequence]", "CageSequence", "[CageCode] = " & ThisCode)) Then GoTo NewCage
    
    GetCageFileNumber = DLookup("[Sequence]", "CageSequence", "[CageCode] = " & ThisCode) + 1
    
    sSQL = "UPDATE CageSequence SET [Sequence] = " & GetCageFileNumber & " WHERE [CageCode] = " & ThisCode
    
    dbs.Execute sSQL, dbFailOnError

    Exit Function

NewCage:
    
    GetCageFileNumber = 1
    
    sSQL = "INSERT INTO CageSequence (" & ThisCode & " , 1)"

    dbs.Execute sSQL, dbFailOnError

End Function

This really is a question of - How do you trap a DLookup runtime error? I've tried a few variations but so far nothing traps the error! I have set this up to produce an error. "ThisCode" is not contained in the table "CageSequence". I need to trap this runtime error (2471) so as to be able to drop into "NewCage:" to be able to add the "ThisCode" to the "CageSequence" table.

Answer 1

I never did find a reason for this lack of error trapping. I have my suspicion that if the value Dlookup is looking for does not exist in the table then an untrapable error occurs. It's just a suspicion and I have no way of confirming if this is the case. In the end I coded around the problem as below:-

Function FindCageFileNumber(ThisCode As String) As Integer

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM CageSequence")
    
FindCageFileNumber = -1

rs.MoveFirst

Do Until rs.EOF = True
    
    If rs!CageCode = ThisCode Then
        FindCageFileNumber = rs!Sequence + 1
        rs.Edit
        rs!Sequence = FindCageFileNumber
        rs.Update
        Exit Do
    End If
    
    rs.MoveNext
Loop

If FindCageFileNumber = -1 Then
    rs.AddNew
    rs!CageCode = ThisCode
    rs!Sequence = 1
    rs.Update
    FindCageFileNumber = 1
End If

rs.Close
Set rs = Nothing

End Function