Grab data from a selection rectangle on a form in datasheet view

[Paul Hitchcock]

I have a subform in datasheet view and the users want to be able to
select multiple cells in a column and display the sum value of the
selected cells, similar to selecting multiple cells in Excel?

I've managed to accomplish this, but it only works if they are looking
at the data unfiltered and in the same record order as the form's
recordset.

Is there another way to do this so that it still works even if they
have a filter on or they have sorted by another column?

Below is the code for the MouseUp event of the BoxCount control that
they are trying to view the sum of:

Private Sub BoxCount_MouseUp(Button As Integer, Shift As Integer, x As
Single, y As Single)
On Error GoTo ErrTrap

If Me.SelHeight > 1 And Me.SelWidth = 1 Then
If Me.SelLeft = Me.BoxCount.ColumnOrder + 1 Then

'User has selected multiple rows in the BoxCount column

Dim r As Integer
Dim SumBoxCount As Integer
Dim rst As New ADODB.Recordset

Set rst = Me.RecordsetClone
rst.AbsolutePosition = Me.SelTop

For r = 1 To Me.SelHeight
SumBoxCount = SumBoxCount + Nz(rst!BoxCount, 0)
rst.MoveNext
Next r

rst.Close
Set rst = Nothing

MsgBox "Sum: " & SumBoxCount, , "Box Count"

End If
End If

ExitSub:
Exit Sub

ErrTrap:
ShowError Me.Name, "BoxCount_MouseUp"
Resume ExitSub

End Sub

 

[Dirk Goldgar]

I have a subform in datasheet view and the users want to be able to
select multiple cells in a column and display the sum value of the
selected cells, similar to selecting multiple cells in Excel?

I've managed to accomplish this, but it only works if they are looking
at the data unfiltered and in the same record order as the form's
recordset.

Is there another way to do this so that it still works even if they
have a filter on or they have sorted by another column?

Below is the code for the MouseUp event of the BoxCount control that
they are trying to view the sum of:

Private Sub BoxCount_MouseUp(Button As Integer, Shift As Integer, x As
Single, y As Single)
On Error GoTo ErrTrap

If Me.SelHeight > 1 And Me.SelWidth = 1 Then
If Me.SelLeft = Me.BoxCount.ColumnOrder + 1 Then

'User has selected multiple rows in the BoxCount column

Dim r As Integer
Dim SumBoxCount As Integer
Dim rst As New ADODB.Recordset

Set rst = Me.RecordsetClone
rst.AbsolutePosition = Me.SelTop

For r = 1 To Me.SelHeight
SumBoxCount = SumBoxCount + Nz(rst!BoxCount, 0)
rst.MoveNext
Next r

rst.Close
Set rst = Nothing

MsgBox "Sum: " & SumBoxCount, , "Box Count"

End If
End If

ExitSub:
Exit Sub

ErrTrap:
ShowError Me.Name, "BoxCount_MouseUp"
Resume ExitSub

End Sub

Are you doing this in an ADP? I'm guessing you are, or the form's
RecordsetClone would be a DAO recordset, not an ADODB recordset. Please
verify, though. In either case, I don't think you should be using the
New keyword in the declaration of rst, since you don't want to create a
new recordset, but rather gain a reference to one that either already
exists or will be manufactured for you by the Form object.

Those points aside, I'm not sure why this code is not working right when
the user has filtered or sorted the form, as I understand you to be
saying. The form's RecordsetClone, at least in an .mdb, *ought* (AFAIK)
to reflect the current filtering and sorting of the form. I'll have to
check this out, after you confirm the guesses I made above.

 

[Dirk Goldgar]

(sending again, as my first reply hasn't appeared after some time)

I have a subform in datasheet view and the users want to be able to
select multiple cells in a column and display the sum value of the
selected cells, similar to selecting multiple cells in Excel?

I've managed to accomplish this, but it only works if they are looking
at the data unfiltered and in the same record order as the form's
recordset.

Is there another way to do this so that it still works even if they
have a filter on or they have sorted by another column?

Below is the code for the MouseUp event of the BoxCount control that
they are trying to view the sum of:

Private Sub BoxCount_MouseUp(Button As Integer, Shift As Integer, x As
Single, y As Single)
On Error GoTo ErrTrap

If Me.SelHeight > 1 And Me.SelWidth = 1 Then
If Me.SelLeft = Me.BoxCount.ColumnOrder + 1 Then

'User has selected multiple rows in the BoxCount column

Dim r As Integer
Dim SumBoxCount As Integer
Dim rst As New ADODB.Recordset

Set rst = Me.RecordsetClone
rst.AbsolutePosition = Me.SelTop

For r = 1 To Me.SelHeight
SumBoxCount = SumBoxCount + Nz(rst!BoxCount, 0)
rst.MoveNext
Next r

rst.Close
Set rst = Nothing

MsgBox "Sum: " & SumBoxCount, , "Box Count"

End If
End If

ExitSub:
Exit Sub

ErrTrap:
ShowError Me.Name, "BoxCount_MouseUp"
Resume ExitSub

End Sub

Are you doing this in an ADP? I'm guessing you are, or the form's
RecordsetClone would be a DAO recordset, not an ADODB recordset. Please
verify, though. In either case, I don't think you should be using the
New keyword in the declaration of rst, since you don't want to create a
new recordset, but rather gain a reference to one that either already
exists or will be manufactured for you by the Form object.

Those points aside, I'm not sure why this code is not working right when
the user has filtered or sorted the form, as I understand you to be
saying. The form's RecordsetClone, at least in an .mdb, *ought* (AFAIK)
to reflect the current filtering and sorting of the form. I'll have to
check this out, after you confirm the guesses I made above.

 

[Paul Hitchcock]

Yes, it is in an ADP.

No matter what sort or filter I apply, here is what happens (in this
example, the form had 1056 records):

After applying a filter I have 4 records showing and I select rows 3
and 4. SelTop returns 3. SelHeight matches the correct amount of rows
I've selected visually, and returns 2.

When I reference any record in the RecordsetClone (or Recordset)
object, the ordinal positions do not match the current view, but rather
ALWAYS return data as if I were looking at the unadulterated underlying
query. So the sum returned by the above example is always the sum of
records 3 and 4 if I were looking at the form's Record Source in
datasheet view, not the sum of records 3 and 4 of the current filtered
view.

After that, I remove all filters and sorts and the code works fine.
Then I sort by the BoxCount field. I select rows 3 and 4 again (which
because of the sort are not the same records 3 and 4 in the underlying
query), and I get the exact same sum as before.

The only solution I can think is to create a new recordset based on a
combination of the underlying query and the current filter and sort,
then grab the data from the new recordset instead. I think I can make
that work, I am just hoping that I'm overlooking a much simpler method.

 

[Dirk Goldgar]

Yes, it is in an ADP.

No matter what sort or filter I apply, here is what happens (in this
example, the form had 1056 records):

After applying a filter I have 4 records showing and I select rows 3
and 4. SelTop returns 3. SelHeight matches the correct amount of rows
I've selected visually, and returns 2.

When I reference any record in the RecordsetClone (or Recordset)
object, the ordinal positions do not match the current view, but
rather ALWAYS return data as if I were looking at the unadulterated
underlying query. So the sum returned by the above example is always
the sum of records 3 and 4 if I were looking at the form's Record
Source in datasheet view, not the sum of records 3 and 4 of the
current filtered view.

After that, I remove all filters and sorts and the code works fine.
Then I sort by the BoxCount field. I select rows 3 and 4 again (which
because of the sort are not the same records 3 and 4 in the underlying
query), and I get the exact same sum as before.

The only solution I can think is to create a new recordset based on a
combination of the underlying query and the current filter and sort,
then grab the data from the new recordset instead. I think I can make
that work, I am just hoping that I'm overlooking a much simpler
method.

I'll have to try this out in a test ADP -- I don't have one handy, as I
mostly work in MDBs. I'm not sure what's going on with the
RecordsetClone, but it may have to do with Access trying to reduce round
trips to the server. I think I remember reading that RecordsetClone is
handled differently in an ADP than in an MDB, but I could be mistaken.

Here are two alternative things you might try:

1) Requery the RecordsetClone after you've got a reference to it:

Set rst = Me.RecordsetClone
rst.Requery

2) Instead of using the RecordsetClone property, clone the form's
recordset yourself:

Set rst = Me.Recordset.Clone

Maybe that would work.

If these both give you the unfiltered, unordered recordset, you could
try opening a filtered, ordered recordset from the one you've got.

 

[Paul Hitchcock]

I've tried the same thing with the Clone and the actual Recordset with
the same results.

I modified the code to create a new recordset based on a combination of
the recordsource, filter, and sort. I had to do some formatting for
SQL Server to understand my date filters.

This works, but it sometimes doesn't sort exactly the same if you sort
by a column that has the same values. For instance, I sorted by a date
column, and in a few spots where there were multiple records with the
same date, the records were not in the same order from the original
recordset to the new. When I sort by a unique ID column, then the
results are consistent.

Here is the new code:

If Me.SelHeight > 1 And Me.SelWidth = 1 Then
If Me.SelLeft = Me.BoxCount.ColumnOrder + 1 Then

'User has selected multiple rows in the BoxCount column

Dim r As Integer
Dim SumBoxCount As Integer
Dim rst As New ADODB.Recordset
Dim flt As String
Dim sql As String

flt = Me.Filter
flt = Replace(flt, "Between #", "Between CONVERT(DATETIME,
'")
flt = Replace(flt, "AND #", "AND CONVERT(DATETIME, '")
flt = Replace(flt, "#", "', 102)")
sql = "SELECT BoxCount FROM " & Me.RecordSource & " WHERE "
& flt & " ORDER BY " & Me.OrderBy

rst.OPEN sql, CurrentProject.Connection, adOpenForwardOnly,
adLockReadOnly
rst.AbsolutePosition = Me.SelTop

For r = 1 To Me.SelHeight
SumBoxCount = SumBoxCount + Nz(rst!BoxCount, 0)
If Not rst.EOF Then
rst.MoveNext
End If
Next r

rst.Close
Set rst = Nothing

MsgBox "Sum: " & SumBoxCount, , "Box Count"

End If
End If

Ultimately what I wish I had here is a SelectedRecords collection that
is an array of bookmarks for all selected records on a form. This
exists for ListBoxes, why not forms? If anybody at Microsoft is
reading this, could you add this to the next version?