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

Bill Benson bensonforums at gmail.com
Wed Jun 10 09:51:03 CDT 2015


Dragon Slain!

I did it with:

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

Subform code:

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

Standard Module - public variable declaration

dim ArSubformSelections()

On Wed, Jun 10, 2015 at 10:14 AM, Bill Benson <bensonforums at gmail.com>
wrote:

> I *thought* once back in the day when I was a mentally fit Access
> programmer, that I had slain this dragon. But maybe it was all a dream. I
> certainly cannot think of how to do it now.
>
> What I have is a list of names and a variety of other details per record,
> in a data sheet subform.
>
> I have conquered the problem of how to record what records are selected
> when the user leaves the subform to click on an action button elsewhere on
> the main form (the problem I had there was that when the subform lost
> focus, so did the knowledge of what records the user had selected - as I
> say, I solved that be storing the selected items in an array in the Form's
> MouseMove event).
>
> What I now would like to do is ... so as not to confuse the user visually
> - in the click event of that command button, reselect the records on the
> subform. Because right now, when the form loses focus, it only seems to
> show one item selected - assumedly, the last record that was the current
> record.
>
> Any ideas on whether it is possible to force selection of records in the
> subform? I know I can navigate the recordsetclone set a bookmark and return
> to a particular record - but I cannot think of a way to force selection of
> a group of consecutive records.
>
>
>


More information about the AccessD mailing list