Results 1 to 4 of 4

Thread: DLookup Problem

  1. #1
    Join Date
    Jan 2009

    Unanswered: DLookup Problem

    Hello to all the members ot this forum

    First of all I want to apologize for my bad english.

    I am new to access 2007, and totally noob , but i realy need some major help on one question , that i have no one to ask.

    So , the deal is like that: I have two tables - tblStudents and tblPayments

    In tblStudents lets say I have two columns - "IDNumber" (auto number) and "FirstName" (text field)

    In tblPayments i have "ID"(which is the auto number of the payment and is not related to the problrem) , the next column is "IDNumber" (Long Integer) , after that i have "FirstName" (Text field) and finally one field for the money that they pay , but that is not the point.

    I need to make a form of tblPayments in which when i fill the "IDNumber" manually (not from a combo box , because the numbers are like 1000) , then the second controller - "FirstName" automatically to return the name of the student. So i spend two days reading for DLookUp functions , but i am not getting any progress at all. I have tried many things and nothing worked, i get the same mistake -

    "The expression you entered contains invalid syntax"

    So i started from the beigining and tried only a A Function with No Criteria:

    = DLookUp("[FirstName]", "tblStudents")
    (which i wrote in the field Control Source of the controller "FirstName") and I get "The expression you entered contains invalid syntax"

    so I have treied :

    FirstName = DLookUp("[FirstName]", "tblStudents") , and

    = DLookUp("FirstName", "tblStudents")

    and so on and on .....

    Not to mention that in the beginning i have tried the full function :

    =DLookup("[FirstName]", "tblStudents", "[IDNumber] =" & Forms![Payments]!IDNumber)

    FirstName=DLookup("[FirstName]", "tblStudents", "[IDNumber] =" & Forms![Payments]!IDNumber)

    =DLookup("[FirstName]", "tblStudents", "IDNumber=" "IDNumber"

    and so on and on.... everyting was wrong

    Please help me just with the Function with No Criteria ,and i think i can handle it from there.

  2. #2
    Join Date
    Sep 2006
    If tblPayments.IDNumber = tblStudents.IDNumber create a query with a join between the two files.


  3. #3
    Join Date
    Jun 2007
    Maitland NSW,Australia
    I would use a combo box with IDNumber and FirstName. As you type the number the Auto Expand if set to Yes will start displaying the IDNumbers as the numbers are entered until you entered the number or select the number from the "filtered" list.

    If a combo box is used you are able to use the Column property.
    Suppose you combo is called Students with the fields IDnumber and FirstName. In a combo box the first field or column has a reference value of 0 and the second column has a reference value of 1

    Now you want to populate the Name in the FirstName field on the form, then we use the following Me!FirstName=Me!students.column(1)

  4. #4
    Join Date
    Nov 2007
    Adelaide, South Australia
    I wouldn't bother.

    Firstname is not part of the tblPayment design... or it shouldn't be. There is no need to make redundant entries. All you need is the ID.

    If the form you are dealing with has both the payments and student details linked by the Student.ID and Payment.IDNumber fields, then Firstname will be displayed as soon as a student is selected.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!

    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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