Need to sum field in a form for records that are selected by the u

  • Thread starter Thread starter MChau
  • Start date Start date
M

MChau

I built a form which has 2 combo boxes, the source is a select statement. I
am also using a filter on the form to return the records that I am looking
for. I then want to build a command box to sum a field called Allocation
only for records that are selected by the user. I tried Allan Browne's
solution to a similar question to mine, but the value of the sum returning is
0. Any help would be appreciated.
 
In the forms footer, you would put an unbound textbox. You would set that
textbox default value to.. For example, I'll assume to select records you
use a Yes/No Checkbox

=Sum(iif([TheYes/NoCheckBox] = True,[Allocation],0))
 
If you are using a command button, could you please tell us the code you have
for the command button, so that we can know what field the data is stored in
and what your command button code is doing?
 
Ryan, thanks for the quick response.

I am not using a yes/no checkbox, is that when I did put one on the form and
when I was testing, when I selected the checkbox for the first record, it
selected all the records.

Here is my coding:

Private Sub cmdCalculate_Click()
On Error GoTo Err_cmdCalculate_Click
Dim intSum As Integer

MsgBox ("The value of Total_Allocation is : " & Total_Allocation),
vbOKOnly

intSum = SumSelectedRows(Forms!frm_Allocate, Total_Allocation)
MsgBox ("The value of intSum is : " & intSum), vbOKOnly

Exit_cmdCalculate_Click:
Exit Sub

Err_cmdCalculate_Click:
MsgBox Err.Description
Resume Exit_cmdCalculate_Click

End Sub
Function SumSelectedRows(frm As Form, strField As String) As Currency
'Purpose: Sum the selected records in a continous form/datasheet.
'Arguments: frm = a reference to the form.
' strField = name of the field to sum.
'Return: Summed value as currency.
'Example: To sum the Amount field on the current form:
' MyTotal = GetSum(Me, "Amount")
Dim rs As DAO.Recordset
Dim lng As Long
Dim curTotal As Currency

Set rs = frm.RecordsetClone
If rs.RecordCount > 0 Then
rs.MoveFirst
rs.Move frm.SelTop - 1
Do While lng < frm.SelHeight And Not rs.EOF
curTotal = curTotal + Nz(rs(strField), 0)
rs.MoveNext
lng = lng + 1
Loop
End If

SumSelectedRows = curTotal
Set rs = Nothing
End Function
 
Dim ctl As Control
Dim frm As Form
Dim x As Integer
Dim mySum as Long ' or Float or whatever you need

mySum = 0
Set frm = Me
Set ctl = frm!MyListBox

For x = 0 To ctl.ListCount - 1
If ctl.Selected(x) = True Then mySum=mySum + ctl.Selected(x) ' Bound column
Next x

' mySum is the total for your on-form control.
 
Well yeah, you would. You need to replace "MyListBox" with the actual name of
YOUR listbox control.
 
Sorry, I misunderstood. I normally use a listbox for that kind of thing, and
the user can select multiple values in the listbox. Then you need to spin
through all the entries to see which one(s) is/are selected. If you have two
comboboxes, it's a trivial exercise, because there can only be ONE selection
in each one.

You can automatically trigger a re-summing using the ON CHANGE event for the
comboboxes.
 
Well perhaps, I didn't explain how I setup my form. My 2 combo boxes are in
the form header not in the detail section. In the detail section, I have my
employee name and allocation fields. My question is how do I know which
record(s) the user selects so that I can sum my allocation field. I did not
put a yes/no checkbox in the detail area because when I did & tested, when I
selected the checkbox for the first record, the form selected all the records.

I am not that familiar with coding forms in Access, any help would be
appreciated.
 
Okay, you have a repeating detail form, in which multiple records are
displayed. the user can select one or many records in that repeating detail,
and a sum OF those selected records will be passed to an unbound control
somewhere OTHER than in the detail section. (I say that because in a detail,
the value you enter is passed to all instances of that field in the detail.)
In order to get it to work right, you'd have to have your SUM in the form's
footer.

Does that about sum it up?
 
I actually want to sum when the user hits a command box I created. Is this
possible or am I going the wrong way to build this form?
 
Dennis

I changed my form, I am using a main form and a subform that contains the
data I need. I also added a checkbox in my main table to enable my users to
select that particular record. Now the question is - I have a text box in
the footer of the main form - I want to change the value in the text box when
the user clicks the checkbox in the subform? I tried everything - this is my
code:

textbox is called Forms![frmTest].txtRunningTotal

Private Sub Select_Yes_No_Click()
'sum Running Total Field
If Forms![frmTest].txtRunningTotal.Value = Null And
Forms![frmTest].txtRunningTotal.Value = 0 Then
If Me.Select_Yes_No = True Then
Forms![frmTest].txtRunningTotal.Value =
Forms![frmTest].txtRunningTotal.Value + Me.Total_Allocation.Value
End If
Else
Forms![frmTest].txtRunningTotal.Value =
Forms![frmTest].txtRunningTotal.Value - Me.Total_Allocation.Value
End If
Forms![frmTest].txtRunningTotal.Refresh

End Sub
 
Back
Top