Textbox to display text instead of Number without using SQL

[Guest]

Ok, I have been searching for a couple of days and decided to ask everyone a
question. My problem is that on my table I have a ID number that is being
stored and my current form reads this table. I understand that you can not
use a SQL in the control source of a textbox. the scenerio is that on my
table I have CLientName being stored from the Customer Table on a previous
form as the CustomerID. I want the textbox to display Business Name instead
of the ID. Or am I limited to using a combobox to display that information?
I have been trying to code this in the area of my sub Form_Open.

I used another box on the form not visible to store the CustomerID to the
table which was the ClientName box on my form and field name in my table

Here is a snippet that I was trying to do

Me!ClientName = Forms![SampleForm]![CustomerID] 'This works in getting the
ID to the table

However trying to get the business name to show up in the box is not working?
Me!CustomerName = "SELECT Customers.CustomerID, Customers.Business" _
& "FROM Customers WHERE Customers.CustomerID =" _
& Me!Receipt!ClientName & "ORDER BY Customers.Business"
I do not understand How I can code this or if there is an easier way would
be greatly appreciated.

 

[Gina Whipp]

Grizz,

Try...

Me!ClientName = Forms![SampleForm]![FieldNameThatHoldsTheBusinessName]

or

DLookup("Business","Customers","CustomerID = " &
[FieldOnTheFormThatStoresTheCustomerID])

 

[Graham Mandeno]

Hi Grizz

Add your Customers table to your form's RecordSource query. If the
RecordSource is currently a table, create a query based on that table and
add the Customers table to it. Then add the CustomerBusiness field to the
query.

You can then add a textbox to your form and bind it to the CustomerBusiness
field. If you want to ensure that the user cannot change the business name
for that customer from the form, then set its Locked property to Yes and
Enabled to No.

If the CustomerID is not a required field in your base table, then change
the join in the query to an outer join. In query design view, right-click
on the join line between the two tables, then "Properties". Select the
option to show all records from your Receipts (or whatever) table and only
the related records from Customers if they exist. The join line should then
have an arrow pointing to Customers.