Name in TextBox - problem with ControlSource?

[Ian]

I have a form with clientID based on a query.

I'd like a textbox that will pull the clients name from the Tbl_Client
(based on the clientID).

I created a small query with the SQL statement:
SELECT Tbl_Client.ClientID, [ClientLN] & ", " & [ClientFN] AS ClientName
FROM Tbl_Client
WHERE (((Tbl_Client.ClientID)=[forms].[frm_apptdetail].[clientid]));

Then a textbox for the form (frm_apptdetail) with the control source set to:
=[Qry_NameforApptDetail]![ClientName]

but all I get in the textbox is #Name?

Anyidea what I've done wrong or is there a more elegant way for the textbox
to pull the info?

Thanks Ian.

 

[John W. Vinson]

I have a form with clientID based on a query.

I'd like a textbox that will pull the clients name from the Tbl_Client
(based on the clientID).

I created a small query with the SQL statement:
SELECT Tbl_Client.ClientID, [ClientLN] & ", " & [ClientFN] AS ClientName
FROM Tbl_Client
WHERE (((Tbl_Client.ClientID)=[forms].[frm_apptdetail].[clientid]));

Then a textbox for the form (frm_apptdetail) with the control source set to:
=[Qry_NameforApptDetail]![ClientName]

but all I get in the textbox is #Name?

Anyidea what I've done wrong or is there a more elegant way for the textbox
to pull the info?

Thanks Ian.

Linq's DLookUp solution is certainly a good one; another option would be to
bind the ClientID field to a Combo Box on the form. You could base the combo
on a query like yours above (without the criteria but sorting by ClientLN,
ClientFN); if you set the combo's Column Widths property to something like
0";1.0" you'll store the ID while displaying the name. Or you can leave the
column widths to display the ID (assuming that the ID is meaningful to your
users), and put a textbox on the form with a control source

=comboboxname.Column(1)

to display the second column, the name (the Column property is zero based).