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.

 

[Pat Hartman]

I also don't know what you are looking for. But, in general the domain functions are the least efficient way to do anything. In most cases DLookup()s can be replaced by simply changing the RecordSource of the form or report to a query that joins the two tables. And, in many cases DSum(), DAvg(), etc. can be replaced by the coresponding Sum(), Avg(), etc functions in the footers of forms or reports.

When you use domain functions, you can use a table as the recordsource or a query and of course the query might be complex.