Multiple Record Selection on a Continuous Form (1 Viewer)

darthcalis said:

Paul, it works brilliant! Finally, I can use the record selectors for something useful! It's a lot nicer than having to build a separate list using a control.

I must thank you again though as your link provided me with the bit of SQL that I was missing. I didn't know the syntax for looking for multiple items in the WHERE clause, which you conveniently provided in your code, so thanks for that.

I manage to pull it off thanks to a bit of code I found on the web a few weeks back. I'd already been knocking the idea around in my head and was wondering how I'd do it, and I came across the original version of the following:

Code:Copy to clipboard

'---------------------------------------------------------------
'read which records have been selected and return the recordset
'---------------------------------------------------------------
Public Function stdLib_ReturnSelectedRecordset(frm As Access.Form, ByVal strPKName As String) As DAO.Recordset
    On Error GoTo ErrorHandler
 
    Dim intBottom As Integer
    Dim intTop As Integer
    Dim intI As Integer
    Dim strFilter As String
    Dim rst As DAO.Recordset
 
    intTop = global_lngLastSelected
    intBottom = global_lngSelectedCount
    
    Set rst = frm.RecordsetClone
    With rst
        If intTop > intBottom Then
            strFilter = "1=0"
        Else
            For intI = intTop To intBottom
                .AbsolutePosition = intI - 1
                strFilter = strFilter & "," & .Fields(strPKName).Value
            Next
            strFilter = strPKName & " In (" & Mid(strFilter, 2) & ")"
        End If
        .Filter = strFilter
        Set stdLib_ReturnSelectedRecordset = .OpenRecordset
    End With
    
    GoTo EndPoint
    
ErrorHandler:
    Select Case stdLib_ErrorLogging(Err.Number, Err.Description, "stdLib_returnSelectedRecordset", True)
        Case 1: Resume Next
        Case 2: Resume
        Case Else
    End Select
    
EndPoint:
    'cleanup
    Set rst = Nothing
End Function

I modified it a little for my own use, and you can see how I'm using the global variables. The OnMouseUp event on my sub form triggers this bit of code:

Code:Copy to clipboard

'-----------------------------------------------------------------------
'stores which records have been selected on a form in global 2 variables
'-----------------------------------------------------------------------
Public Function stdLib_StoreSelectedRecords(frm As Form)
    On Error GoTo ErrorHandler
 
    With frm
        global_lngLastSelected = .SelTop
        global_lngSelectedCount = .SelHeight + .SelTop - 1
    End With
    
    GoTo EndPoint
    
ErrorHandler:
    Select Case stdLib_ErrorLogging(Err.Number, Err.Description, "stdLib_StoreSelectedRecords", True)
        Case 1: Resume Next
        Case 2: Resume
        Case Else
    End Select
    
EndPoint:
End Function

Finally, when I click my print button, I run this:

Code:Copy to clipboard

Private Sub btnPrint_Click()
    On Error GoTo ErrorHandler
    
    'declare local objects and vars
    Dim rst As DAO.Recordset
    Dim strIDList As String
    Dim strWhereText As String
    
    'init vars
    strIDList = ""
    
    If global_lngLastSelected = global_lngSelectedCount Then
        'if only a single record then just print the selected record
        DoCmd.OpenReport const_rptContractReport, acViewNormal
    Else
        Set rst = stdLib_ReturnSelectedRecordset(Me.subRegSub.Form, const_fldContractIDField)
        
        With rst
            If Not .BOF Then
                .MoveFirst
                While Not .EOF
                    strIDList = strIDList & .Fields(const_fldContractIDField) & ", "
                    .MoveNext
                Wend
            End If
        End With
        
        'close recordset
        rst.Close
        
        'if ID list found, remove final delimiter
        If strIDList <> "" Then
            strIDList = Left(strIDList, Len(strIDList) - 2)
        End If
        
        strWhereText = const_fldContractIDField & " IN (" & strIDList  & ")"
        stdLib_WriteDebugLog "btnPrint_Click: WHERE clause text: " & strWhereText
        DoCmd.OpenReport const_rptMultipleContractReport, acViewNormal, , strWhereText
    End If
    
    'reset selected records variables
    stdLib_StoreSelectedRecords Me.subRegSub.Form
        
    GoTo EndPoint
    
ErrorHandler:
    Select Case stdLib_ErrorLogging(Err.Number, Err.Description, "btnPrint_Click", True)
        Case 1: Resume Next
        Case 2: Resume
        Case Else
    End Select
    
EndPoint:
    'cleanup
    Set rst = Nothing
End Sub

A couple of things worth mentioning though. I ended up having to use 2 reports, one for single printing, where I used the filtering tip you previously gave me, and a second one that doesn't filter itself. I ended up having to do that because no matter what I tried, I couldn't get a single report to work consistently well. I'd get the single report occasionally printing the wrong record regardless of whether I tried launching with a where clause or using a filter. In the end my brain couldn't take it any more and I resorted to 2 reports, which is now working perfectly. The single report is filtered and the multiple report uses the WHERE clause, as you can see in the code.

The other thing is that I had to make sure that there was a space after the comma separating my ID values in the 'IN' part of the clause, otherwise I would randomly get not all the records I wanted printing. Since I changed to include the space, it works fine every time. Very odd.

Anyway, this works!

Cheers
Emilio

Click to expand...