Is there a more efficient way to display multiple query results in different text boxes on a form
I have a form with multiple text fields. Each text box is populated with the result of a different query. I am not very familiar with VBA / Access (I am teaching myself) and not sure if what I am doing is the most efficient. I was hoping someone could suggest perhaps a less cumbersome way to populate text fields in a form with different requests, or even suggest a better naming convention.
* edit - I think there is a way to place the formula directly in the textbox rather than doing it via VBA, but I got the #name error.
Private Sub Form_Load()
Dim db As DAO.Database
Dim qdf As QueryDef
Dim rst As Recordset
Dim qdf2 As QueryDef
Dim rst2 As Recordset
Set db = CurrentDb()
Set qdf = db.QueryDefs("QryGetTotalTransactions")
Set rst = qdf.OpenRecordset
Set qdf2 = db.QueryDefs("QryGetTotalTransactionsInManagement")
Set rst2 = qdf2.OpenRecordset
[Form_Portfolio Status].TotalTransaction_txt.Value = rst![Total]
[Form_Portfolio Status].TotalInManagement_txt.Value = rst2![Total]
End Sub
source to share
In each of these two examples, you are retrieving one value from a stored query. So DLookup can get values with less code.
DLookup("[Total]", "QryGetTotalTransactions") DLookup("[Total]", "QryGetTotalTransactionsInManagement")
You can assign the return value DLookup
to the textbox .Value
property ... as you did with the recordset value. Or you can use an expression DLookup
as a Control Source text box.
In terms of efficiency, performance is DLookup
unlikely to be faster than the recordset approach. But you don't need all this code to manage DAO objects. So maybe there could be a developer time saving that should count on something. :-)
source to share