Totalling cells (1 Viewer)

R

ryetee

Registered User.
Local time
Today, 03:24
Joined
Jul 30, 2013
Messages
952
Hi I have a form with various numbers on and in the form footer I sum these.
The user now is getting all excited and wants the ability to highlight cells in a column, like he can in Excel, and get the total of these instead. I've looked at how to do this for a few hours but can't see that it is within my capability. Anyone out there know if it can be done?
 
MajP

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 22:24
Joined
May 21, 2018
Messages
8,525
I tried this with a subform in datasheet view and with a price field and a quantity field. I put two textboxes on the main form. I set the timer down to 500. This worked well
'Set the Form's Timer Property to 500, and paste the following code in the Timer Event

Code: 
'************* Code Start **************
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
Private Sub Form_Timer()
  Dim i As Long
  Dim sumPriceSelected As Currency
  Dim sumQuantitySelected
If Me.SelHeight = 0 Then Exit Sub
   With Me.RecordsetClone
        .MoveFirst
        .Move Me.SelTop - 1
        For i = 1 To Me.SelHeight
            sumPriceSelected = sumPriceSelected + !UnitPrice
            sumQuantitySelected = sumQuantitySelected + !Quantity
            .MoveNext
        Next i
    End With
   Me.Parent.txtSumPriceSelected = sumPriceSelected
   Me.Parent.txtSumQuantitySelected = sumQuantitySelected
End Sub

If you select records it shows the total price and total quantity.
 
R

ryetee

Registered User.
Local time
Today, 03:24
Joined
Jul 30, 2013
Messages
952
MajP said:
I tried this with a subform in datasheet view and with a price field and a quantity field. I put two textboxes on the main form. I set the timer down to 500. This worked well
'Set the Form's Timer Property to 500, and paste the following code in the Timer Event

Code: 
'************* Code Start **************
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
Private Sub Form_Timer()
  Dim i As Long
  Dim sumPriceSelected As Currency
  Dim sumQuantitySelected
If Me.SelHeight = 0 Then Exit Sub
   With Me.RecordsetClone
        .MoveFirst
        .Move Me.SelTop - 1
        For i = 1 To Me.SelHeight
            sumPriceSelected = sumPriceSelected + !UnitPrice
            sumQuantitySelected = sumQuantitySelected + !Quantity
            .MoveNext
        Next i
    End With
   Me.Parent.txtSumPriceSelected = sumPriceSelected
   Me.Parent.txtSumQuantitySelected = sumQuantitySelected
End Sub

If you select records it shows the total price and total quantity.
Click to expand...
CHeers I'll give it a go. I'm using a continuous form and I've disable the record selectors but I'll give it a go.
 
MajP

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 22:24
Joined
May 21, 2018
Messages
8,525
I'm using a continuous form and I've disable the record selectors but I'll give it a go
Click to expand...
I think there is no way to select records. You will have to roll your own "selection", using conditional formatting and storing a "selection start" and "selection End"
 
R

ryetee

Registered User.
Local time
Today, 03:24
Joined
Jul 30, 2013
Messages
952
MajP said:
I tried this with a subform in datasheet view and with a price field and a quantity field. I put two textboxes on the main form. I set the timer down to 500. This worked well
'Set the Form's Timer Property to 500, and paste the following code in the Timer Event

Code: 
'************* Code Start **************
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
Private Sub Form_Timer()
  Dim i As Long
  Dim sumPriceSelected As Currency
  Dim sumQuantitySelected
If Me.SelHeight = 0 Then Exit Sub
   With Me.RecordsetClone
        .MoveFirst
        .Move Me.SelTop - 1
        For i = 1 To Me.SelHeight
            sumPriceSelected = sumPriceSelected + !UnitPrice
            sumQuantitySelected = sumQuantitySelected + !Quantity
            .MoveNext
        Next i
    End With
   Me.Parent.txtSumPriceSelected = sumPriceSelected
   Me.Parent.txtSumQuantitySelected = sumQuantitySelected
End Sub

If you select records it shows the total price and total quantity.
Click to expand...


This works so many thanks for that. I have a couple of questions one is from me and the other I know I'm going to get asked!

So is there anyway to select a record without having the record selectors of the form set to on? I usually disable this straight away so the users can't do anything that isn't controlled by the code.

Secondly can you only select adjacent records? I know he'll want to select 1-4 and then number 8 for example! I can probably get around this by filtering but wanted to know if it's doable.
 
MajP

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 22:24
Joined
May 21, 2018
Messages
8,525
This worked pretty well. The user has to hit shift and mouse down to select the range. I used conditional formatting to color those in selection Grey.

Code: 
ption Compare Database
Option Explicit

Private TopRecord As Long
Private BottomRecord As Long
Private lastRecord As Long


Private Sub Form_Load()
  TopRecord = -1
  BottomRecord = -1
  lastRecord = -1
End Sub

Private Sub UnitPrice_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
  Dim TempRecord As Long
  Dim Pos As Long
  Dim rs As DAO.Recordset
  Dim I As Integer
  Dim sumUnitPrice
  
  If Shift Then
    Set rs = Me.Recordset
    Pos = rs.AbsolutePosition
    Debug.Print "In Pos " & Pos & " ToP " & TopRecord & " Bottom" & BottomRecord & " Last " & lastRecord
  
    If lastRecord = Pos Then
      TopRecord = Pos
      BottomRecord = Pos
    ElseIf TopRecord = -1 Then
      TopRecord = Pos
    ElseIf Pos < TopRecord Then
      'BottomRecord = TopRecord
      TopRecord = Pos
    ElseIf Pos >= TopRecord And Pos <= BottomRecord Then
      TopRecord = Pos
    ElseIf Pos > TopRecord Then
      BottomRecord = Pos
    End If
  End If
  Me.Recalc
  If TopRecord <> -1 And BottomRecord <> -1 Then
     rs.AbsolutePosition = TopRecord
     Do
       sumUnitPrice = sumUnitPrice + rs!UnitPrice
       rs.MoveNext
     Loop Until rs.AbsolutePosition > BottomRecord
  End If
  lastRecord = Pos
  Me.txtSumUnitPrice = sumUnitPrice
  Debug.Print "Out Pos " & Pos & " ToP " & TopRecord & " Bottom" & BottomRecord & " Last " & lastRecord
  
End Sub
Public Function InSelection(OrderDetailID As Long) As Boolean
  Dim rs As DAO.Recordset
  Dim Pos As Long
  Set rs = Me.RecordsetClone
  rs.FindFirst "OrderDetailID = " & OrderDetailID
  Pos = rs.AbsolutePosition
  If Pos >= TopRecord And Pos <= BottomRecord Then InSelection = True
  'Debug.Print InSelection
End Function
'------------------------------------------------------------------
'Conditional Formatting: Expression Is InSelection([orderDetailID])=True  set rows to grey
 
MajP

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 22:24
Joined
May 21, 2018
Messages
8,525
Code: 
Secondly can you only select adjacent records? I know he'll want to select 1-4 and then number 8 for example! I can probably get around this by filtering but wanted to know if it's doable.

I think you can then make this simpler to do that. Add a boolean "Selected" field. If you double click a field or hit the checkbox it gets selected (use conditional formatting to show selected rows as well). You can then modify the code I posted to instead of summing the records in the selection to update them to "selected". That way they can select a range and also select individual records.

Having the ability to select multiple ranges would be really complicated in my opintion. You would have to modify the code I provided with a collection to store each range start and stop.
 
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 19:24
Joined
Aug 30, 2003
Messages
36,124
If you want to switch to a multi-select listbox, it would be simple to sum values from the selected records.
 
R

ryetee

Registered User.
Local time
Today, 03:24
Joined
Jul 30, 2013
Messages
952
pbaldy said:
If you want to switch to a multi-select listbox, it would be simple to sum values from the selected records.
Click to expand...

OK never come across that before I'll take a look.
Taken a look and no idea where to start!
 
Last edited:
MajP

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 22:24
Joined
May 21, 2018
Messages
8,525
OK never come across that before I'll take a look.
Taken a look and no idea where to start!
Click to expand...
If that is acceptable, that is likely the cleanest approach. You can add features to sort by column and filter the listbox as well. These other approaches work, but are a little clunky.
 
MajP

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 22:24
Joined
May 21, 2018
Messages
8,525
If you go multiselect listbox here is some demo code. This sums three different columns and displays the results

Code: 
Private Sub cmdRemove_Click()
  'removes selections
  Dim lst As Access.ListBox
  Set lst = Me.lstProduct
  Dim i As Integer
  For i = 0 To lst.ListCount - 1
    lst.Selected(i) = False
  Next i
End Sub

Private Sub lstProduct_AfterUpdate()
  UpdateTotals
End Sub
Private Sub UpdateTotals()
  'updates totals based on selections
  Dim i As Integer
  Dim lst As Access.ListBox
  Set lst = Me.lstProduct
  Dim totalprice As Currency
  Dim totalStock As Integer
  Dim totalOrdered As Integer
  For i = 0 To lst.ItemsSelected.Count - 1
    Debug.Print lst.Column(3, lst.ItemsSelected(i))
    totalprice = totalprice + lst.Column(3, lst.ItemsSelected(i))
    totalStock = totalStock + lst.Column(4, lst.ItemsSelected(i))
    totalOrdered = totalOrdered + lst.Column(5, lst.ItemsSelected(i))
  Next i
  Me.txtUnitPrice = totalprice
  Me.txtOrdered = totalOrdered
  Me.txtStock = totalStock
End Sub

Less involved than trying to roll your own selections.
 
R

ryetee

Registered User.
Local time
Today, 03:24
Joined
Jul 30, 2013
Messages
952
MajP said:
This worked pretty well. The user has to hit shift and mouse down to select the range. I used conditional formatting to color those in selection Grey.

Code: 
ption Compare Database
Option Explicit

Private TopRecord As Long
Private BottomRecord As Long
Private lastRecord As Long


Private Sub Form_Load()
  TopRecord = -1
  BottomRecord = -1
  lastRecord = -1
End Sub

Private Sub UnitPrice_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
  Dim TempRecord As Long
  Dim Pos As Long
  Dim rs As DAO.Recordset
  Dim I As Integer
  Dim sumUnitPrice
  
  If Shift Then
    Set rs = Me.Recordset
    Pos = rs.AbsolutePosition
    Debug.Print "In Pos " & Pos & " ToP " & TopRecord & " Bottom" & BottomRecord & " Last " & lastRecord
  
    If lastRecord = Pos Then
      TopRecord = Pos
      BottomRecord = Pos
    ElseIf TopRecord = -1 Then
      TopRecord = Pos
    ElseIf Pos < TopRecord Then
      'BottomRecord = TopRecord
      TopRecord = Pos
    ElseIf Pos >= TopRecord And Pos <= BottomRecord Then
      TopRecord = Pos
    ElseIf Pos > TopRecord Then
      BottomRecord = Pos
    End If
  End If
  Me.Recalc
  If TopRecord <> -1 And BottomRecord <> -1 Then
     rs.AbsolutePosition = TopRecord
     Do
       sumUnitPrice = sumUnitPrice + rs!UnitPrice
       rs.MoveNext
     Loop Until rs.AbsolutePosition > BottomRecord
  End If
  lastRecord = Pos
  Me.txtSumUnitPrice = sumUnitPrice
  Debug.Print "Out Pos " & Pos & " ToP " & TopRecord & " Bottom" & BottomRecord & " Last " & lastRecord
  
End Sub
Public Function InSelection(OrderDetailID As Long) As Boolean
  Dim rs As DAO.Recordset
  Dim Pos As Long
  Set rs = Me.RecordsetClone
  rs.FindFirst "OrderDetailID = " & OrderDetailID
  Pos = rs.AbsolutePosition
  If Pos >= TopRecord And Pos <= BottomRecord Then InSelection = True
  'Debug.Print InSelection
End Function
'------------------------------------------------------------------
'Conditional Formatting: Expression Is InSelection([orderDetailID])=True  set rows to grey
Click to expand...

I'll give it a whirl.
I'm not keen on enabling the record selectors and the timer event don't half get in the way when you're trying to debug stuff!!

Anyway appreciate your help.
 
R

ryetee

Registered User.
Local time
Today, 03:24
Joined
Jul 30, 2013
Messages
952
ryetee said:
OK never come across that before I'll take a look.
Taken a look and no idea where to start!
Click to expand...

What I have is

Row 1 sum 1
Row 2 sum 2
Row 3 sum 3

with MajP's original answer I can total rows 1 and 2, 2 and 3 but not 1 and 3.
Not sure how a list box will help me here.
 
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 19:24
Joined
Aug 30, 2003
Messages
36,124
A list box will let you select non-contiguous rows (records) in any combination. It has the disadvantage of being read-only if you wanted users to edit the data, but you could pop up a form to edit the selected record.
 
R

ryetee

Registered User.
Local time
Today, 03:24
Joined
Jul 30, 2013
Messages
952
pbaldy said:
A list box will let you select non-contiguous rows (records) in any combination. It has the disadvantage of being read-only if you wanted users to edit the data, but you could pop up a form to edit the selected record.
Click to expand...

I can see a list box is a list of items. I can't see how to add that to my form.
From my example on each row I have a sum, sum 1 sum 2 and sum3.
Are you suggesting I have a list box on each row and the contents are sum 1,sum 2 and sum3?
I may be a bit thick here but I don't get it!!
 
R

ryetee

Registered User.
Local time
Today, 03:24
Joined
Jul 30, 2013
Messages
952
MajP said:
If you go multiselect listbox here is some demo code. This sums three different columns and displays the results

Code: 
Private Sub cmdRemove_Click()
  'removes selections
  Dim lst As Access.ListBox
  Set lst = Me.lstProduct
  Dim i As Integer
  For i = 0 To lst.ListCount - 1
    lst.Selected(i) = False
  Next i
End Sub

Private Sub lstProduct_AfterUpdate()
  UpdateTotals
End Sub
Private Sub UpdateTotals()
  'updates totals based on selections
  Dim i As Integer
  Dim lst As Access.ListBox
  Set lst = Me.lstProduct
  Dim totalprice As Currency
  Dim totalStock As Integer
  Dim totalOrdered As Integer
  For i = 0 To lst.ItemsSelected.Count - 1
    Debug.Print lst.Column(3, lst.ItemsSelected(i))
    totalprice = totalprice + lst.Column(3, lst.ItemsSelected(i))
    totalStock = totalStock + lst.Column(4, lst.ItemsSelected(i))
    totalOrdered = totalOrdered + lst.Column(5, lst.ItemsSelected(i))
  Next i
  Me.txtUnitPrice = totalprice
  Me.txtOrdered = totalOrdered
  Me.txtStock = totalStock
End Sub

Less involved than trying to roll your own selections.
Click to expand...
I'll take a look but I'm still not sure what my form is going to look like!!
 
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 19:24
Joined
Aug 30, 2003
Messages
36,124
Instead of the form being bound to the table/query and displaying the records, it would (could) be unbound. You'd put a listbox on it that had the same row source as the form's previous record source. In other words the listbox is displaying records instead of the form itself. The code you were given would sum the appropriate fields in the listbox for the record(s) selected into textboxes on the form.
 
MajP

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 22:24
Joined
May 21, 2018
Messages
8,525
As pointed out, this may not work if you need to edit directly on your form with a lot of different type of fields and controls. If this form was mainly for analysis and not entry/editing, the form itself would be a big listbox with a bunch of columns. Below the listbox are fields for total data. You can potentially sort and filter the listbox. Then you can select specific records and view the sums. You could have a way to pop up a form to edit a row if needed since the listbox is not editable.
This interface may not support your needs, depending how the db UI is designed. This could be just an additional form focused on doing just these summations, instead of a replacement for your current form.
 
MajP

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 22:24
Joined
May 21, 2018
Messages
8,525
Another possibility is this could be below your current rows in a large footer. It would then allow you to select rows. If you synched the listbox and the continous form you could use conditional formatting to select rows in the listbox and color them in the continous form. There may not be much real estate to do that.
 
You must log in or register to reply here.

Similar threads

M
Display subform confrol total to mainform
Replies
18
Views
456
Cotswold
Cotswold
T
Cannot Sum a Calculated Control in the Report Footer
Replies
11
Views
310
The_Doc_Man
The_Doc_Man
oxicottin
Solved Highlight cell on print
Replies
4
Views
215
oxicottin
oxicottin
A
Form Text Box #Name?
Replies
4
Views
404
Pat Hartman
P
kengooch
Count Total Records on a filtered form based upon the value of a cell
Replies
1
Views
402
Gasman
Gasman

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom