Set Control Property to a Query Result

[RDS2]

I have a text box on a form and I want to set its control source to a field value in a query. In this case the query is a TOPN(N=1 in this instance) and I want the value to dispaly in the text box. Can I enter the SQL code in the control source property box or can I use VBA code to accomplishe this? If VBA code do I need to create a variable to hold the value and set the control source to the variable?

My Query =
"SELECT TOP 1 Tbl_drev.rev_no, Tbl_drev.rev_date, Tbl_drev.rev_desc, Tbl_drev.doc_type, Tbl_drev.loc_num, Tbl_drev.loc_num, Tbl_drev.process_num, Left([rev_desc],300) AS Expr1
FROM Tbl_drev
WHERE (((Tbl_drev.doc_type)=2) AND ((Tbl_drev.loc_num)=[forms]![form11].[text12]) AND ((Tbl_drev.process_num)=[forms]![form11].[text16]))
ORDER BY Tbl_drev.rev_no DESC;"

Any help appreciated.

 

[PaulBricker]

If this is actually a form and not a report, you could either set the RecordSource property for the form to the query and then set the ControlSource for the textbox to the field you want. Or you can use DLookUp() to get the value from the query based on some where clause.

Paul

 

[dhookom]

Or, you could use the generic concatenate function faq701-4233 with your sql.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]