DLookup question

[Daryl2106]

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

[boblarson]

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.

[Daryl2106]

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

[boblarson]

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:

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.