Problem with subquery

[bakerdenn]

I'm trying to do something that seems trivial to me, but I can't see to get it to work correctly. I am building a database for a university academic advisement center. One of the forms needs to be able to display the student's ID number, name, and information concerning them changing majors.

The data for the ID and name are not entered on this form, they are only there for the user to reference. The only piece of data related to the user that will be stored in my MajorChange table is the student's ID number.

The student's ID number is passed to this form via a temporary variable (StudentEagleID) created by a macro when the change major button is clicked. This information is passed successfully and loads into the ID field of my MajorChange form.



I am trying to use a subquery in Expression Builder to call the student's name from the Students table to populate the name fields (Last, First, MI). The syntax of my expression is as follows:

=(SELECT [Students].[LastName] FROM [Students] WHERE [Students].[EagleID]= [TempVars]![StudentEagleID] )
However, when the form loads I get #Name? in the last name field.

I can copy and paste the SQL query into a query and it executes perfectly, returning the desired data. Is there something that I'm missing here?

[thhui]

In expression,
use dlookup function instead of
sql select statement