How to get field updated with query result

[Guest]

I’m looking for a straight forward way to update a field with the result of a
query.
Can someone please assist me with the syntax.

I’m running a series of queries when a form is closed, to yield a date from
several records in a table. I’ve got the logic behind the queries to filter,
group (etc.) and produces a single field query result. Now how can I update
a field on a different form with this result.

Thanks,
Ken

 

[Brendan Reynolds]

We're talking a single field, single row query, yes? Something like ...

SELECT Sum(tblTest.TestNumber) AS SumOfTestNumber
FROM tblTest;

With the above asumption, you can either open a recordset to retreive the
result, or you can use the DLookup function ...

Private Sub cmdTest_Click()

'using recordset ...
Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset
With rst
.ActiveConnection = CurrentProject.Connection
.Source = "qryTest"
.Open
Me.txtTest1 = .Fields("SumOfTestNumber")
.Close
End With

'using DLookup ...
Me.txtTest2 = DLookup("SumOfTestNumber", "qryTest")

End Sub