none
Gettin a runtime error 2471

    Question

  • I believe that this code should work but i gives an 2471 error.

    When I press debug the bold line is highlighted. "UName" is an unbound text box, "Users" is a table, "UserName" and "UserID" are fields in the table. I am trying to retrieve the UserID (PKey) number for other use.

    Dim UNum As String
    UNum = ""
    UNum = DLookup("[UserID]", "Users", "[UserName]=" & UName)
    If UNum = "" Then
        MsgBox "User not found!", vbExclamation, "Not Found"
        UName = Null
    End If

    Any thoughts, questions?

    Thursday, July 21, 2011 4:08 PM

Answers

  • Try:

    UNum = DLookup("[UserID]", "Users", "[UserName] = Forms![LoginForm]![UName]")

    This is syntax I use mostly.

    • Marked as answer by Danbolder Thursday, July 21, 2011 5:27 PM
    Thursday, July 21, 2011 4:47 PM
  • Of course, use Nz() to avoid null error:

    UNum = Nz(DLookup("[UserID]", "Users", "[UserName] = '" & Me.UName & "'"))
    



    Andrey V Artemyev | Saint-Petersburg, Russia
    • Marked as answer by Danbolder Thursday, July 21, 2011 5:26 PM
    Thursday, July 21, 2011 4:47 PM

All replies

  • You might try to put the Form reference in your code, something like this:

     

    UNum = DLookup("[UserID]", "Users", "[UserName]=" & Forms![frmYourFormName]![UName])

     

    Hope this helps,

     


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Thursday, July 21, 2011 4:11 PM
  • Hi,

    string variables should be with apostrophs or quotes around. So, the solution is

    UNum = DLookup("[UserID]", "Users", "[UserName]=""" & UName & """)
    


    or

    UNum = DLookup("[UserID]", "Users", "[UserName]='" & UName & "'")



    Andrey V Artemyev | Saint-Petersburg, Russia
    Thursday, July 21, 2011 4:12 PM
  • Yes, Daniel also makes a good statement, it's better to use the full reference path to a form field/control. Or you can simply use Me.UName for the curent form.
    Andrey V Artemyev | Saint-Petersburg, Russia
    Thursday, July 21, 2011 4:14 PM
  • Tried

    UNum = DLookup("[UserID]", "Users", "[UserName] = " & Forms![LoginForm]!UName)

    Same error.

    Thursday, July 21, 2011 4:39 PM
  • Try with the quotes as Andrey mentioned:

     

    UNum = DLookup("[UserID]", "Users", "[UserName] = '" & Forms![LoginForm]!UName & "'")

     

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Thursday, July 21, 2011 4:42 PM
  • Tried

    UNum = DLookup("[UserID]", "Users", "[UserName] = " & Me.UName)

    Same error.

    Tried

    UNum = DLookup("[UserID]", "Users", "[UserName] = '" & UName & "'")

    Get invalid use of null if not found.

    Thursday, July 21, 2011 4:45 PM
  • Try:

    UNum = DLookup("[UserID]", "Users", "[UserName] = Forms![LoginForm]![UName]")

    This is syntax I use mostly.

    • Marked as answer by Danbolder Thursday, July 21, 2011 5:27 PM
    Thursday, July 21, 2011 4:47 PM
  • Of course, use Nz() to avoid null error:

    UNum = Nz(DLookup("[UserID]", "Users", "[UserName] = '" & Me.UName & "'"))
    



    Andrey V Artemyev | Saint-Petersburg, Russia
    • Marked as answer by Danbolder Thursday, July 21, 2011 5:26 PM
    Thursday, July 21, 2011 4:47 PM
  • UNum = DLookup("[UserID]", "Users", "[UserName] = '" & UName & "'")

    UNum = DLookup("[UserID]", "Users", "[UserName] = Forms![LoginForm]![UName]")

    Both work and return the appropriate ID number.

    Both give invalid use of null if not found.

    Thursday, July 21, 2011 4:59 PM
  • i got to face the same error, but it's becoz of having unwanted spaces in the syntax of DLookup. After removing these spaces, it's all well.
    Monday, January 30, 2012 10:12 AM