MS Access Dlookup referencing a joined query

Question

So I'm building a form in MS Access 2010, and I want to populate a text box with the result of the following query, which is saved in Access as 'GRT_tblEstablishments Names Query':

SELECT est.school_name
FROM GRT_tblEstablishments est, GRT_school_record schrec
WHERE schrec.ID = 4
AND schrec.Current_School = True
AND est.ID = schrec.School_ID;

I'm using a DLookUp in the Control Source box as follows:

=DLookUp("school_name","GRT_tblEstablishments Names Query","[schrec.ID] = ID")

The problem I seem to be having is feeding the schrec.ID to the query from the DLookUp... All I get is #error when I load the form... I've been fighting it all afternoon with no success... I'm sure it must be something really simple, but my Access skills are reasonably rudimentary... What am I doing wrong?

Thanks!

Gustav Gustav · Accepted Answer · 2018-09-13T14:46:55

The ID must be concatenated - and be careful with spaces:

=DLookUp("[school_name]","[GRT_tblEstablishments Names Query]","[schrec].[ID] = " & ID & "")

MS Access Dlookup referencing a joined query

1 Answers