Textbox control source problem

If you want the query to return a value pertaining to the record shown
on
the
current form, then you need to somehow tell Access which record that is!

That much I understand I thought the fact that I have established
persistent relations between the tables establishes just that?! At any
rate, I've tried to explicitly incorporate the relations, but it still
doesn't work for me.

Well... no.

The relationship says that every record in jtblItemsStatus must have a
StatusID field which matches some record in tblStatus. That's ALL THAT IT
MEANS. It doesn't automagically tell a form *which* record that might be.

John, the subform based on the query given in my OP works. If I change a
record on the MainForm, the data in the subform (whose recordsource is a
query built from jtblItemsStatus) change accordingly. It's the ID for *that
parent table* (ItemsID in the join table) for which I want the latest date
record in the jointable so that I may use the FK of tblStatus to lookup the
value of fldStatus.


DLookUp("[QryforCurrentStatusTxtBox]![fldStatus]","[QryforCurrentStatusTxtBox]","[QryforCurrentStatusTxtBox]![ItemsID]=[tblItems]![ID]")

Doesn't work. Something wrong with the criterion part.

Take the criterion out of the quotemarks. Since the only recordset
referenced
inside the DLookUp is the query, there's no need to qualify the
fieldnames:

DLookUp("[fldStatus]", "[QryforCurrentStatusTxtBox]", "[ItemsID]=" &
[tblItems]![ID])

Still doesn't work. Get #Name? error in the control and when I "?" the
function in the debug immediate window I get "External reference not
defined." Tried various syntactical mods, all with same result. When you
say "only recordset referenced inside" you do see that I'm referencing
tblItems, the parent of the join table?

I have a feeling I'm making a very fundamental error here. Forest for trees
kinda deal.

-Ron