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

[Bill Benson]

PS.   I doubt, Darryl, with several hundred records and many fields which
the user needs to see in entirety, should they choose, as well as certain
details that they require an ability to copy out of the list (one example
is file path) that you would have been satisfied with a listbox. Had I gone
that route I would have had to add many textboxes to the form to display
full values of any single item clicked on. Also, to allow selection of a
range of values I probably would have had to use a multiselect extended
list box and added several controls to allow the user reformat and filter
and/or sort the columns in the listbox. Another option would be to let the
user select from the list box then use opentable or open another datasheet
form to display and operate on the selected items from the listbox, which
method I have used in other projects.
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
>