Is there a more efficient way to display multiple query results in different text boxes on a Form

Question

I have a form with several different text boxes. Each text box is populated with the result of a different query. I'm not very familiar with VBA / Access (I am teaching myself) and am not sure if what I'm doing is the most efficient. I was hoping someone could suggest maybe a less cumbersome way of populating text boxes on a form with different queries, or perhaps even suggest a better variable naming convention.

*edit - I think there is a way to place a formula directly in a text box rather than doing it through VBA but I received a #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

Answer 1

In each of those two examples, you're retrieving a single value from a saved query. So DLookup could get the values with less code.

DLookup("[Total]", "QryGetTotalTransactions")
DLookup("[Total]", "QryGetTotalTransactionsInManagement")

You could assign the DLookup return value to a text box's .Value property ... like you were doing with the recordset value. Or you could use the DLookup expression as the text box Control Source.

Regarding efficiency, DLookup performance is not likely to be faster than the recordset approach. But you wouldn't need all that code to manage DAO objects. So perhaps there could be savings in developer time, which should count for something. :-)