Results 1 to 4 of 4
  1. #1
    Daryl2106 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    167

    DLookup question

    Hi,

    I am new to access. I have the following code to lookup assocated fields with a CourseNumber that is selected from a combobox. The information is then stored in a CourseEnrollment table linked to student info.

    The CourseNumber and associated fields are located in a LU_CoursesOffered table. The LU_Courses Offered table has been populated by chossing assocted data from combo boxes based on other LU tables.

    I am getting the primary keys returned for the associated data rather than the text stored with that key. Any info would be greatly appreciated.

    Private Sub Combo103_AfterUpdate()
    CourseName = DLookup("CourseName", "LU_CoursesOffered", "CollegCourseNum = " & Nz([CollegeCourseNum], 0))
    LevelSection = DLookup("LevelSection", "LU_CoursesOffered", "CollegeCourseNum = " & Nz([CollegeCourseNum], 0))


    AcadGrad = DLookup("AcadGrad", "LU_CoursesOffered", "CollegeCourseNum = " & Nz([CollegeCourseNum], 0))
    Instructor = DLookup("Instructor", "LU_CoursesOffered", "CollegeCourseNum = " & Nz([CollegeCourseNum], 0))
    End Sub

  2. #2
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    It would appear that you may have lookups defined at table level (which is bad - see here for why) and for this you would have to use a different method - I suggest a recordset with a query that will pull all of the info you want at one time and then populate the text boxes (by the way you should be referring to your text boxes like:

    Me.CourseName
    Me.LevelSection
    Me.AcadGrad

    so Access will know that you are wanting the specific form. It has been known to get confused before if you don't do that.

  3. #3
    Daryl2106 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    167
    Hi Bob,

    Thanks so much. That was much easier. I am still getting primary keys entered into the field for which I want the text. Could you explain the "Me." a little more. If I create a LU table with no primary key, I do get the text I want, but I suspect you should never have a table without a key.

    Thanks again and take care,

    Daryl

  4. #4
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    Sorry, I forgot to include the link for the lookups at table level:
    http://www.mvps.org/access/lookupfields.htm

    And about the keyword ME:
    Quote Originally Posted by from my website glossary
    This is a programming shortcut which lets the programmer refer to the current CLASS object. Most of the time, in Access, you would be referring to a form or report using this. It can also apply to custom classes as well, but is less prevalent unless custom classes are being used.
    Let's say you have a form named "frmMain" and you have a text box there called "txtEntry" and you wanted to refer to that text box in code. A fully-qualified reference would be Forms!frmMain.txtEntry but you could also refer to it using the shortcut (as long as the code is ON frmMain): Me.txtEntry


    You should be storing the key in the table and NOT the text. You can always get the text using a query.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Dlookup
    By pcandeias0 in forum Programming
    Replies: 3
    Last Post: 07-09-2011, 02:31 PM
  2. Form Design Question with DLookup (newbie)
    By wongc61 in forum Access
    Replies: 4
    Last Post: 07-08-2011, 03:22 PM
  3. DLookup help
    By denners05 in forum Access
    Replies: 1
    Last Post: 06-11-2011, 12:55 PM
  4. DLookup question
    By Lockrin in forum Access
    Replies: 2
    Last Post: 07-13-2010, 06:22 AM
  5. Dlookup question
    By nkenney in forum Forms
    Replies: 3
    Last Post: 07-01-2009, 06:36 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums