Add query to Access control

[Guest]

How do I add a non-parameter SQL Query to a Text Box Control?
I have a form based on a table. One of the controls is a ComboBox that
allows selection of values from a Primary Key in another table. Upon
selection of the Value, I would like to populate another Text Box on the form
with a value from the other table. E.g.: By selecting CustomerID in the
Combo Box, the Text Box populates with CustomerName. Adding a normal SQL
Query to the Control Source value in the Text Box to the does not seem to
work. (E.g. (SELECT [CustomerName] from [Customer] WHERE [MyTable].[CustID]
= [Customer].[CustID]?) I do not want a parameter query that prompts for the
value when the form is opened. Is this possible?

 

[tina]

you can't use a SQL statement in a textbox's ControlSource property. there
are several ways to display the value in the textbox control. since you're
working with a a combobox control, the easiest way is to add the field to
the combobox's RowSource. you can set the column width to zero so it doesn't
display in the droplist, and make sure you increase the ColumnCount property
by 1.

having done the above, let's say you have three fields in the combo box
control's RowSource, and the value you want to display in the textbox
control is in the third field. just put an expression in the textbox's
ControlSource property, as

=ComboboxName.Column(2)

in your expression, use the real name of the combobox control, and the
correct index number for the column - notice that the column index is
zero-based, so the third field in the RowSource has a Column index of 2.

hth