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

Darryl Collins darryl at whittleconsulting.com.au
Sun Jun 14 18:30:01 CDT 2015 

Yeah, the best solution really depends on your data and your outcome and your own personal tastes - I would never be so bold as to suggest there is a single best way to do <anything>.  I like using listboxes (often with a filter option to narrow down the initial options plus using cascading results as well - so as the users selects an option in the first list box, the 2nd list box populates with only valid options based on listbox 1 etc...) but they come with their pro and cons for sure.

Anyway, I hear you re Access and it's limits.  More and more I am using SQL Server for most things and just use Access as  FE if / when required.

Good luck.  Maybe Access 2016 is better.  I was testing it (Access 2016) over the last few weeks, but the latest update to W10 technical preview on the weekend fried my test PC and I have to reinstall it all again from scratch.  Too lazy yesterday (Sunday for me) to be bothered with all that, so will try later in the week.

Good luck Bill.
Cheers
Darryl


-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bill Benson
Sent: Friday, 12 June 2015 6:12 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Force selection of multiple records on a datasheet subform

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
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

More information about the AccessD mailing list