Results 1 to 8 of 8
  1. #1
    Join Date
    May 2006
    Posts
    178

    Unanswered: Whats wrong with my Dlookup?

    Hi Guys,

    Im trying to search a table to see if a record exists. this is what i ahve so far, can someone please advise me where i have gone wrong.

    I have a table called "User" and the fields are

    [ID][FName][LName][PhoneNo]


    On a form i have a text field called txtID i have a command button with the following code

    Code:
    Private Sub cmdAdd_Click()
    
    Dim strMsg As String
    
    If DLookup("ID", "User", "txtID") = 1 Then
       strMsg = "Record Exists"
       MsgBox (strMsg)
    Else
        strMsg = "Record Does NOT Exist"
        MsgBox (strMsg)
    End If
    
    End Sub

    thanks


    Aboo

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you are not specifying any criteria, or valid criteria
    Description of DLookup() usage, examples, and troubleshooting in Access 2000 gives an example close to what you want....
    =DLookUp("[LastName]", "Employees", "[EmployeeID] = 7")
    ie get the lastname from the table Employees where EmployeeID=7
    if you are supplying a text value then it must be in quotes
    =DLookUp("[EmployeeID]", "Employees", "[LastName] ='" & Aboo & "'")
    or
    =DLookUp("[EmployeeID]", "Employees", "[LastName] ='" & mycontrolname.value & "'")
    or
    =DLookUp("[EmployeeID]", "Employees", "[LastName] ='" & fogetusername() & "'")
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2006
    Posts
    178
    Thanks for the reply Healdem,

    Ive changed it tot he folowing and it doesnt seem to work, i dont even get a msg box pop up.

    Code:
    
    Private Sub cmdAdd_Click()
    
    Dim strMsg As String
    
    If DLookup("ID", "User", "[ID]='" & txtID.value &"'") = 1 Then
       strMsg = "Record Exists"
       MsgBox (strMsg)
    Else
        strMsg = "Record Does NOT Exist"
        MsgBox (strMsg)
    End If
    
    End Sub
    Last edited by aboo; 03-08-09 at 17:15.

  4. #4
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    To solve your problem try using the DCount as example

    If DCount("[id]", "user", "id='" & mycontrolname.value & "'")>0 then
    Msgbox("User ID already exists")
    else
    Msgbox("New User")

    end if

  5. #5
    Join Date
    May 2006
    Posts
    178
    Guys,

    Still doesnt seem to work. What can it be that i am doing wrong?

    ive attached the sample. to this post, can anyone spot where ive made a mistake?

    Regards

    aboo
    Last edited by aboo; 05-08-09 at 16:59.

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    I'm sorry, but which part of the error message

    "Block If Without End If"

    didn't you understand? Especially when Access takes you to the offending bit of code? Add End If at the indicated place and the code works as it should.

    Code:
    If DCount("[id]", "user", "id='" & txtID.Value & "'") > 0 Then
    MsgBox ("User ID already exists")
    Else
    MsgBox ("New User")
    End If
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  7. #7
    Join Date
    May 2006
    Posts
    178
    OMG...

    im sorry guys.

    There is a setting on my application, something regarding "protecting me from unknown conent"

    Nothing was hapenign at all untill i unchecked it...

    im sorry for the confusion....


    Aboo

  8. #8
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Glad you got it fixed!
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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