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?
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.
