dlookup and query for multiple data (1 Viewer)

[thewrightdesign]

Not sure this is where this discussion goes but here it is.

I'm using access 2003 on windows xp.

Table structure is as follows:

StudentInfo:
StudentID pk
LastName
FirstName
MI
etc

Classes:
ClassPrefix pk
CourseName
CreditHours
CourseCost
etc

Grades:
ID pk
StudentID
Semester
Year
ClassPrefix
Grade
etc

Relationships: Any table with StudentID has a relationship with the StudentID pk on the StudentInfo table. The Grades table also has a relationship to the Classes table via ClassPrefix.

I have a Grades form that stores student grade information. I have used DLookup to pull LastName and FirstName of the students off the StudentInfo table. This is working perfectly.

I have tried to get the ClassPrefix to autopopulate the CourseName field on the Grades table but am having no luck. If I use ClassPrefix from the Grade table as the control source for the combo box on that same form, it will show fine but nothing I code into the CourseName field box is working to get it to autopopulate based off the Classes table using the ClassPrefix as the criteria.

Two samples of what I have tried:

=DLookUp("CourseName","classes","[ClassPrefix] = " & [ClassPrefix])

=DLookUp("CourseName","classes","[ClassPrefix] = " & Forms!classes!ClassPrefix)

edited to add: these codes have gone on the CourseName text field box under control source for the Grades form.

What am I missing here?

Thanks in advance as always

Elise

 

[DCrake]

If class prefix is a string then it needs single quotes around it

=DLookUp("CourseName","classes","[ClassPrefix] = '" & [ClassPrefix] & "'")

 

[thewrightdesign]

If class prefix is a string then it needs single quotes around it

=DLookUp("CourseName","classes","[ClassPrefix] = '" & [ClassPrefix] & "'")

That did it! I knew I had the idea right, could not figure out why it wasn't working!! Thanks for the fast response!

Elise