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

If CageCode is numeric then try:
If Nz(DLookup("[Sequence]", "CageSequence", "[CageCode] = " & ThisCode), "") = "" Then GoTo NewCage

 
If CageCode is text then try:

If Nz(DLookup("[Sequence]", "CageSequence", "[CageCode] = '" & ThisCode) & "'", "") = "" Then GoTo NewCage
 
You could alternately test using IsNull(), or Len(), ... many different ways to perform such a check.
 

---

Daniel Pineault, 2010-2019 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Answer 2

Firstly declare GetCageFileNumber as a Variant.  Then:

    GetCageFileNumber = DLookup("[Sequence]", "CageSequence", "[CageCode] = " & Nz(ThisCode,0))

and then execute the SQL statement conditionally:

    If Not IsNull(GetCageFileNumber) Then
        dbs.Execute sSQL, dbFailOnError
    Else
        Goto NewCage
    End If

PS:  I've just noticed that the ThisCode argument is declared as a String, in which case you don't need to call the Nz function, but the value should be wrapped in literal quotes characters, so:

    GetCageFileNumber = DLookup("[Sequence]", "CageSequence", "[CageCode] = """ & ThisCode & """")

---

Ken Sheridan, Stafford, England

Answer 3

Thank you Daniel.
Unfortunately I am now getting the same error coming up where "ThisCode" IS in the "CageSequence" table. So I guess I may well have an Access problem which should be fixed before I get to take this any further! My best option may be to uninstall Access and do a clean install, but that's a job for tommorrow now!!

Answer 4

Also, your second SQL statement doesn't make a lot of sense as, amongst other things, it does not wrap the ThisCode string expression in literal quotes characters:

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

I think we need chapter and verse from you as to the data types of the Sequence and CageNumber columns in GageSequence.

---

Ken Sheridan, Stafford, England