Use SELECT to asign value to text box?

[el zorro]

I have a text box that I want to display a specific value from a table when I
click a control. But I can't get this to work in the click event statement of
the control:

Forms!FormA!TextBox.ControlSource = "SELECT TableA.number, TableA.item FROM
TableA WHERE TableA.number =" & 5

The number 5 will be replaced with a value from the form if I can get the
first part to work, but so far all I get in TextBox is "#Name?"

Probably a stupid error somewhere on my part. Or maybe text boxes don't
accept SELECT statements? Any ideas?

THanks!

 

[Jeanette Cunningham]

Hi el zorro,
use DLookup instead of a query.
You can set the text box's control source to the DLookup.
Here is an example:

=Nz(DLookup("[FieldName]", "NameOfTableOrQuery", "[KeyID] = " &
Me.KeyID),"")
Use vba help on dlookup for more information.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[el zorro]

Thanks, Jeanette. DlookUp was just what I needed!

Hi el zorro,
use DLookup instead of a query.
You can set the text box's control source to the DLookup.
Here is an example:

=Nz(DLookup("[FieldName]", "NameOfTableOrQuery", "[KeyID] = " &
Me.KeyID),"")
Use vba help on dlookup for more information.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I have a text box that I want to display a specific value from a table when
I
click a control. But I can't get this to work in the click event statement
of
the control:

Forms!FormA!TextBox.ControlSource = "SELECT TableA.number, TableA.item
FROM
TableA WHERE TableA.number =" & 5

The number 5 will be replaced with a value from the form if I can get the
first part to work, but so far all I get in TextBox is "#Name?"

Probably a stupid error somewhere on my part. Or maybe text boxes don't
accept SELECT statements? Any ideas?

THanks!