Concatenate Using Fields From Unrelated Table

[gnarpeggio]

Hello,

I'm slowly getting used to the benefits of concatenation, but my question might be a tricky one that I can't seem to find an answer to.

In my DB, I have a form based on a query from my main table that displays results depending on which employee the user chose on the previous form by evaluating a DLookup expression.

The AddTo (Addressed To) field on the query criteria is set to a DLookup function:

DLookup(AddToLookup, tblAddToSearch)

This will ensure that the query result changes when the user chooses a different name on the "Select Employee" form.

Now for the tricky part...

The AddTo field is a combo box list that draws from my Staff table. In my staff table, I want to concatenate the First and Last name fields into one text box within the query result form. Initially, I thought using the following would work:

ControlSource Property...

=Staff.FirstName & " " & Staff.LastName

But Access is producing the dreaded #Name? error in runtime.

The Staff table has a One-to-Many relationship with my Addressed To and Referred To fields within my main table. Could this be causing the problem?

I am stumped!!!