Sum selected items in subform

  • Thread starter Thread starter Steven
  • Start date Start date
S

Steven

I have a form with a subform. One of the fields in the subform is "Amount"
and is a Double with 2 decimals.

For example lets say the subform has 20 records. I select 5 of these
records using the Record Selector on the subform. I would like to have a
Command Button on the form that when clicked it will show the sum total of
the Amount field for the records selected. I just want to show the result in
a message box.

Is this possible?

Thank you,

Steven
 
Steven said:
I have a form with a subform. One of the fields in the subform is "Amount"
and is a Double with 2 decimals.

For example lets say the subform has 20 records. I select 5 of these
records using the Record Selector on the subform. I would like to have a
Command Button on the form that when clicked it will show the sum total of
the Amount field for the records selected. I just want to show the result
in
a message box.

Is this possible?
Click to expand...


Yes, this is possible, but it's a bit complicated because when you click the
button, the selection on the subform is lost. Therefore, you have to
capture the selection (identified by the form's SelTop and SelHeight
propertes)
 
Steven said:
I have a form with a subform. One of the fields in the subform is "Amount"
and is a Double with 2 decimals.

For example lets say the subform has 20 records. I select 5 of these
records using the Record Selector on the subform. I would like to have a
Command Button on the form that when clicked it will show the sum total of
the Amount field for the records selected. I just want to show the result
in
a message box.

Is this possible?
Click to expand...


Sorry, I hit Send too soon. As I was saying ...

Yes, this is possible, but it's a bit complicated because when you click the
button, the selection on the subform is lost. Therefore, you have to
capture the selection (identified by the form's SelTop and SelHeight
propertes) before you leave the subform. Here's one way.

In the Declarations section of your subform's code module, create two public
variables:

Public CurrentSelectionTop As Long
Public CurrentSelectionHeight As Long

In the subform's Click event, use an event procedure like this to capture
the information about the current selection:

'------ start of code ------
Private Sub Form_Click()

CurrentSelectionTop = Me.SelTop
CurrentSelectionHeight = Me.SelHeight

End Sub
'------ end of code ------

It's my understanding that your command button will be on the main form. If
that's so, let its Click event procedure look something like this
(substituting your subform and control names where appropriate):

'------ start of code ------
Private Sub cmdTotalSelected_Click()

Dim curTotal As Currency
Dim lngFirstRec As Long
Dim lngNRecs As Long

With Me.sfYourSubform.Form

lngFirstRec = .CurrentSelectionTop
lngNRecs = .CurrentSelectionHeight

If lngNRecs > 0 Then

With .RecordsetClone

.AbsolutePosition = lngFirstRec - 1

While lngNRecs > 0
curTotal = curTotal + Nz(!Amount, 0)
lngNRecs = lngNRecs - 1
If lngNRecs > 0 Then
.MoveNext
End If
Wend

End With

End If

End With

MsgBox "The total is " & curTotal

End Sub
'------ end of code ------

Note: I've assumed that your Amount field has the Currency data type.
Adjust the code as needed if it is not.
 
Hello,
I realize this thread is 2 1/2 years old, so I'm hoping this will still reach you. I'm also looking to looking to sum values in the datasheet view on an Access 2007 split form by a user's selected records. I want to be able to select a finite number of records in the datasheet section of the split form (not use the totals row function) and display that on a control for the user.

I have been able to tell which row/record/cell the cursor or selection is currently on and by using your SelTop and SelHeight code above, am able to acquire what the user actually has highlighted/selected. The problem I'm having is getting the sum of the selected records to display on a form.

I am attempting to assign the value to a label (lblSum) by calculating and assigning the sum to an Integer (intSum).
The ints and lngs are assigned in the same subroutine and the sds is a global form pointer to be able access the split forms datasheet.My attempt is below and uses a good bit of your code.

Pic to the form view
http://i.imgur.com/nKW7N.png

Code: 
lblSum.Caption = "0"

On Error Resume Next    ' (A)
With sds
        .SelTop = 1
        .SelHeight = 1
        .SelWidth = .Controls.Count
        
lngFirstRec = .SelTop
lngNextRecs = .SelHeight

If lngNextRecs > 0 Then

With Me.RecordsetClone

Me.RecordsetClone.AbsolutePosition = lngFirstRec - 1

While lngNextRecs > 1
intSum = intSum + Nz(![Build Qty], 0)
lngNextRecs = lngNextRecs - 1
If lngNextRecs > 0 Then
.MoveNext
End If
Wend

End With

End If

End With
   
lblSum.Caption = intSum
Any further insight would be appreciated.
Thanks tremendously in advance,
dbro34
 
Back
Top