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

  • 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
        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.

    Thursday, July 30, 2020 10:12 AM

All replies

  • 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

    Thursday, July 30, 2020 10:46 AM
  • 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
            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

    • Edited by Ken Sheridan Thursday, July 30, 2020 1:03 PM Postscript added.
    Thursday, July 30, 2020 12:52 PM
  • 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!!
    • Edited by TonyJW Thursday, July 30, 2020 1:08 PM
    Thursday, July 30, 2020 1:06 PM
  • 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

    Thursday, July 30, 2020 1:18 PM