[AccessD] Force selection of multiple records on a datasheet subform

[Bill Benson]

Hah, very rarely, I get Access to do what I want. The resources issue has
really kicked my butt, but I have a painful workaround for that as well.

I know this is a different thread but the other one I started is so long
lol.

I have found though that even when I stop my routine when the current
process' s memory hits 1 3GB that (1) I am making an assumption that there
were > 1.3 gb to start with, which might be a risky assumption, and (2) I
have seen Access run out of resources even before that limit is hit. So, as
at least one other commenter mentioned, I don't think available memory is
the only manner in which Access runs out of "resources". Despise Access
2013 with utter loathing.
On Jun 10, 2015 6:22 PM, "Darryl Collins" <darryl at whittleconsulting.com.au>
wrote:

> Like Don, I am pleased you have a solution - although I would have also
> used a list box with multi select option set to true.
>
> I tend to use them a lot in Access on forms and almost never use subforms,
> which tend (for me at least) to be cumbersome in their behaviour.
>
> Nice work on your solution Bill.
>
> Cheers
> Darryl
>
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> Bill Benson
> Sent: 11 June 2015 02:14
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Force selection of multiple records on a datasheet
> subform
>
> I just love everything there is about a datasheet subform. The items you
> mentioned, like ordering, filtering,widening and reordering columns.
>
> While there is a drawback in not being able to select non-consecutive
> items, I handle that by toggling and adjusting filters. Also, I include a
> column in the underlying table (and thus also in the recordsource query for
> the subform) called "Selected", and update that through the Select
> Highlighted Items and UnSelect Highlighted Items command buttons each time
> the user has highlighted a group of records.
>
> Thanks for the kudos, it seems to be working out.
>
> On Wed, Jun 10, 2015 at 11:37 AM, McGillivray, Don <
> DMcGillivray at ctc.ca.gov>
> wrote:
>
> > Kudos on your success, but your original question raises another for me.
> >
> > I know there are advantages to using a datasheet sub-form (sortable,
> > variable column widths, built-in filtering, etc.) but might a
> > multi-select list box have been an option here?  I find it pretty
> > simple to loop through the selected list box items and perform my
> > magic - all while the selected items still appear selected in the
> > list.  I've even worked around some of the display limitations by
> > including sorting/filtering options using combo boxes for selecting
> > the target columns/values.  I suspect that the overhead of
> > populating/repopulating the list box would be comparable to or lower
> than that for walking the record set as in your example.
> >
> > Or maybe you have other reasons for using the sub-form?  Or other
> > drawbacks to the list box that I'm missing?
> >
> >
> >
> > -----Original Message-----
> > From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf
> > Of Bill Benson
> > Sent: Wednesday, June 10, 2015 7:53 AM
> > To: Access Developers discussion and problem solving
> > Subject: Re: [AccessD] Force selection of multiple records on a
> > datasheet subform
> >
> > I think my last post was too long for the list, here it is again just
> > in case.
> >
> > Subform code:
> >
> > Option Compare Database
> > Option Explicit
> > Private Sub Form_MouseMove(Button As Integer, Shift As Integer, X As
> > Single, Y As Single) EstablishSelections End Sub Private Sub
> > EstablishSelections() Dim iHeight As Long Dim i As Long Dim sIDString
> > As String Dim rS As DAO.Recordset iHeight = Me.SelHeight On Error
> > Resume Next If iHeight > 0 Then
> >     ReDim ArSubformSelections(1 To 4, 1 To iHeight)
> >     sIDString = ""
> >     Set rS = Me.RecordsetClone
> >     rS.MoveFirst
> >     rS.Move Me.SelTop - 1
> > Else
> >     Exit Sub
> > End If
> > For i = 1 To iHeight
> >     ArSubformSelections(1, i) = rS![ID]
> >     ArSubformSelections(2, i) = rS![EmployeeName]
> >     ArSubformSelections(3, i) = rS![EmployeeFirstName]
> >     ArSubformSelections(4, i) = rS![Email]
> >     sIDString = sIDString & "," & ArSubformSelections(1, i)
> >     rS.MoveNext
> > Next
> > If sIDString <> "" Then
> >     sIDString = "(" & Mid(sIDString, 2) & ")"
> > End If
> > End Sub
> >
> > Parent form Code:
> >
> > Private Sub cmdSendSelected_Click()
> > Dim iTest As Long
> > Dim i As Long, iCount As Long, iFirstSelection As Long, iLastSelection
> > As Long Dim rst As DAO.Recordset Dim FRM As Form Dim F As Form On
> > Error Resume Next iTest = UBound(ArSubformSelections, 2) On Error GoTo
> > 0 If iTest > 0 Then
> >     For iTest = 1 To UBound(ArSubformSelections, 2)
> >         If Err.Number = 0 Then
> >             If Len(CStr(ArSubformSelections(2, iTest))) > 0 Then
> >                 iCount = iCount + 1
> >                 If iCount = 1 Then
> >                     iFirstSelection = ArSubformSelections(1, iTest)
> >                 End If
> >                 iLastSelection = iTest
> >             End If
> >         End If
> >     Next
> >
> >     If iFirstSelection > 0 Then
> >         Set F = Me.rptFinishedReportsNotYetSent.Form
> >         Set rst = F.RecordsetClone
> >         rst.FindFirst "Id = " & iFirstSelection
> >         If Not rst.NoMatch Then
> >             F.Bookmark = rst.Bookmark
> >             F.SelHeight = iCount
> >         End If
> >
> >         If iLastSelection > iLastSelection Then
> >             If MsgBox("Send reports for IDs " & ArSubformSelections(1,
> > iFirstSelection) & " through " & ArSubformSelections(1,
> > iLastSelection) & "?", vbQuestion + vbYesNo + vbDefaultButton1) = vbYes
> Then
> >                 'Get details of items to send from the array
> >             End If
> >         Else
> >             If MsgBox("Send reports to " & ArSubformSelections(1,
> > iFirstSelection) & "?", vbQuestion + vbYesNo + vbDefaultButton1) =
> > vbYes Then
> >                 'Get details of items to send from the array
> >             End If
> >         End If
> >     End If
> > End If
> > End Sub
> >
> > Public variable declaration
> > Public ArSubformSelections()
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>