I have a text box that needs to be filled / populated with the Sum of
all the payments made by the worker selected in the combo box. The
following query will get the Sum based on whatever is selected in the
combo box:

SELECT DISTINCTROW Sum(tblPayments.PaymentAmount) AS
SumOfPaymentAmount
FROM tblPayments
WHERE (((tblPayments.WorkerName)=[frmCASA-4payments]!
[cboWorkerName]));

How do I get those query results into the text box, or get the Sum
based on the combo selection into the textbox.

I can requery the text box every time the combo box changes, if I can
get the sum in the text box.

RCGUA wrote:

Assumes your field name is called TextFld. In the form's module you
might have.

Private Function GetSum()
Dim r As Recordset
Dim s As String
s = "Select ...." 'your above select. Or else queryname
Set r = Currentdb.Openrecordset(s,dbopensnapshot)
If r.recordcount > 0 then
Me.TextFld = r!SumOfPaymentAmount
else
Me.TextFld = 0
Endif
r.close
set r = nothing
End Function

Then in the control source enter
=GetSum()

You might need to enter some code like
Me.TextFld.Requery
in some event that trips the need to resum.

On Jul 27, 5:38*pm, Salad <o... (AT) vinegar (DOT) com> wrote:
I tried putting the query in like this (below), but it didn't seem to
"r" correctly because after that the code just stopped without an
error message and it didn't seem to have a record count.

Private Function GetSum()
Dim r As Recordset
Dim s As String
s = "qrySumOfEmployeePayments" 'your select Or else queryname
Set r = CurrentDb.OpenRecordset(s, dbOpenSnapshot)
If r.RecordCount > 0 Then
Me.txtTotalEmployeePayments = r!s ' SumOfPaymentAmount
Else
Me.txtTotalEmployeePayments = 0
End If
r.Close
Set r = Nothing
End Function

Use the Query Builder to build a Select Query that returns all the payments
made by each worker. Then, in design view, click View | Totals, on the
menu. Group by Worker ID, and set Sum on the amount. Use it as the domain
in a DSUM function, and refer to the TextBox on the Form showing the Worker
ID in the criteria argument. There's pretty good Help on DSUM... use the
Help on a Module window.

Larry Linson
Microsoft Office Access MVP

"RCGUA" <stv.nelson1 (AT) gmail (DOT) com> wrote

On Jul 27, 5:38 pm, Salad <o... (AT) vinegar (DOT) com> wrote:
I tried putting the query in like this (below), but it didn't seem to
"r" correctly because after that the code just stopped without an
error message and it didn't seem to have a record count.

Private Function GetSum()
Dim r As Recordset
Dim s As String
s = "qrySumOfEmployeePayments" 'your select Or else queryname
Set r = CurrentDb.OpenRecordset(s, dbOpenSnapshot)
If r.RecordCount > 0 Then
Me.txtTotalEmployeePayments = r!s ' SumOfPaymentAmount
Else
Me.txtTotalEmployeePayments = 0
End If
r.Close
Set r = Nothing
End Function

On Jul 27, 6:32*pm, "Larry Linson" <boun... (AT) localhost (DOT) not> wrote:
I tried both methods described above and I must be doing something
wrong with the syntax or some little thing. I have tried every which
way and I still cannot get the textbox to show the sum. At best, I
get "Name?" in the textbox where the query is running but not picking
up the name from the query.

The database is available on the Internet, if that helps. It's at:
http://manoamano.org/casa4/casa-4_v6.mdb

On the form named "frmCASA-4payments", right below the "Employee
Payments" box, there is a "Total Employee Payments" box, that is the
textbox I cannot seem to get the Sum of the payments for the worker
when the "Worker Name" combo box is updated.

On Jul 28, 8:53*am, RCGUA <stv.nels... (AT) gmail (DOT) com> wrote:
===== Solved! ===== Thank you very much Larry & Salad for all your
help and pointing me in the right direction.=====

I set the control source for the textbox with the DSUM text below.
The Combo39 is a combo box that picks the Worker Name.

=DSum("[PaymentAmount]","tblPayments","[WorkerName] = Forms!
[frmCASA-4payments]!Combo39")