To put SQL result into a TextBox

[inoxo]

I have a SQL query in a string of a VBA procedure
(e.g. MyQuery = "select sum(Amount) from Investment")

and I want the result to be shown in a TextBox of my form Me.

I know several ways to run the query and to send the result into the TextBox (e.g. with QueryDef and RecordSet). However I guess Access should offer a more direct way to do such a simple operation.

Does anyone has a solution to do it the simplest way ?

Thanks.

 

[pbaldy]

Try a DSum() as the control source of the textbox.

 

[inoxo]

Thanks Paul, it might help for simple sum queries.

However I am looking for a generalized solution where sql queries are usualy more complex than my example and may return text values.

Let's say the query returns only a single value. What's the simplest way to show it in a textbox ?

 

[pbaldy]

Dlookup() would also be an option. At some point of complexity, you're back to using a recordset. Your question asked for the simplest way.

 

[inoxo]

Thank you Paul.

Yes, in certain cases your ideas could definitely simplify the code.
But I hoped I coul avoid to use a recordset in any case.

Here is my code :

Dim mydb As Database
Dim mySQL As String
Dim myQryTemp As QueryDef
Dim myRecSet As Recordset

Set mydb = CurrentDb()
mySQL = "SELECT sum(...) as MyResult FROM ... WHERE ..."
Set myQryTemp = db.CreateQueryDef("", mySQL)
Set myRecSet = myQryTemp.OpenRecordset
Me.myResultToShow = myRecSet![MyResult]

It's not very long but I find it too long for a so usual operation such as assigning to a variable the value given by a query execution, in any case.

 

[gromit]

So far as I have seen, the only methods are (a) to declare a recordset or (b) use the "D" functions (DLOOKUP, DCOUNT, etc).

You can reduce your code a line or two with
Set mydb = CurrentDb
set myRecSet = db.OpenRecordSet (mySQL)

You might also look at this page http://allenbrowne.com/ser-42.html
which shows an example of a user coded lookup function.

hth,

- g

 

[pbaldy]

I guess I'm not clear on the problem, since again the example you've posted could easily be replaced with 1 line and a DSum(). The domain aggregate functions wouldn't work if your SQL contained multiple tables obviously, but would handle most situations with 1 source table, even with multiple criteria.

As gromit posted, I wouldn't bother with the QueryDef, I'd just open a recordset on MySQL.