Page 1 of 2 12 LastLast
Results 1 to 15 of 17

Thread: DLookUp Issue

  1. #1
    Join Date
    Apr 2004
    Posts
    73

    Unanswered: DLookUp Issue

    Anyone have any idea why this won't work.

    Code:
    Dim table
    table = "dbo_tblUsers"
    
    Dim PasswordUser As Variant
    Dim SecurityUser As Variant
    Dim SecurityLevel As Variant
    
        If IsNull("ID") Then
            MsgBox "The you must enter a valid Logon ID"
            If IsNull("Password") Then
                MsgBox "The you must enter a valid Password"
            End If
        Else
            SecurityUser = DLookup("user", table, "user=[ID]")
            SecurityLevel = DLookup("level", table, "user=[ID]")
            PasswordUser = DLookup("password", table, "user=[ID]")
            If [ID].Value = SecurityUser Then
                [security].Value = SecurityLevel
                If [password].Value = PasswordUser Then
                    DoCmd.Close acForm, "frmLogon"
                Else
                    MsgBox "Please Enter a valid User Name or Password", vbOKOnly
                End If
            Else
                MsgBox "Please Enter a valid User Name or Password", vbOKOnly
            End If
        End If
    When I run a Debug Session it seems to choke at this point
    Code:
    PasswordUser = DLookup("password", table, "user=[ID]")
    The table has the password in it but for some reason it keeps returning an empty record. It works with the previous 2 statements.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    try DLookup("password", table, "user = " & id)

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    table wants to be a quoted string.
    if you have a table called MYTABLE, you type "MYTABLE"

    izy



    unless you have done
    dim table as string
    table = "MYTABLE"

    then you use the var without the quotes

    currently using SS 2008R2

  4. #4
    Join Date
    Apr 2004
    Posts
    73
    Table is being used as a variable. The table string is defined at the top of the procedure. I tried to put the actual table name in "dbo_tblUsers" and it still comes back empty. There is indeed a password in this field.

  5. #5
    Join Date
    Apr 2004
    Posts
    73
    Originally posted by Teddy
    try DLookup("password", table, "user = " & id)
    Does the same thing. I don't understand this I have never had this problem before.

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    hmmmm.

    first a recommendation: avoid variants - they are huge and slow.

    next, there seems a good chance that "table" is a reserved word in a database application: so retry with

    dim myTable as string
    myTable = "dbo_tblUsers"


    ...and of course:
    SecurityUser = DLookup("user", myTable, "user=[ID]")

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ...and ID is a string?

    so maybe:
    SecurityUser = DLookup("user", myTable, "user= '" & [ID] & "'")
    will help.

    izy
    currently using SS 2008R2

  8. #8
    Join Date
    Apr 2004
    Posts
    73
    I tried both and got the same issue.

    I also tried using a SQL statement
    Code:
    SQL = "SELECT dbo_tblUsers.Password FROM dbo_tblUsers WHERE (((dbo_tblUsers.USER)=[ID]));"
    This doesn't work either. But when I executed the SQL in a query it return the proper value. It won't work in the vba, this has me stumped.

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    cos ID is a string field and it needs to get into quotes:

    WHERE (((dbo_tblUsers.USER)= '" & [ID] & "'));"
    will fly!

    izy
    currently using SS 2008R2

  10. #10
    Join Date
    Apr 2004
    Posts
    73
    Same thing. Still returns an empty string. I even deleted the row from the table and reentered all the data.

  11. #11
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    so you have an ID problem.

    first off,

    If IsNull("ID") Then

    is garbage: "ID" is a literal string containing eye-dee and it CANNOT be null.

    if this is supposed to be a textbox, it doesn't want the quotes.


    try again without the quotes around ID and let us know.



    izy
    Last edited by izyrider; 04-21-04 at 14:53.
    currently using SS 2008R2

  12. #12
    Join Date
    Apr 2004
    Posts
    73
    Same problem. What gets me is the statement just before it works fine and returns a value and the only difference is the field it is returning data from.

  13. #13
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    still looking at your code, but meanwhile, kiil those quotes in:
    If IsNull("Password") Then


    izy
    currently using SS 2008R2

  14. #14
    Join Date
    Apr 2004
    Posts
    73
    Finally I got it to work. For some strange reason I had to disconenct the linked table and reconnect it and now it works. Beats me why but it worked.

  15. #15
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    OK, there's several bizarre things in your code but i don't have time to play tonite.

    either someone else is going to fix it for you, or i'll check back tomorrow. to help them or me, please confirm that you do have a table:
    dbo_tblUsers
    that contains fields called:
    user
    level
    password
    ...and let us know what data types these guys are.

    ...and confirm that ID and Password are the names of textboxes on your form (after you checked their real names using properties¦other¦name) that is attempting to run this code.

    we'll get there.

    izy
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •