Is it possible to use the record selectors on a continuous form to do anything useful? I have a continuous form as a subform which displays a list of customer contracts and some related data and it would be nice if I could select multiple records and print them.
However, I have noticed that although you can select multiple records, as soon as you press a command button the selection changes to a single record.
Is this fixed behaviour, or does anyone know if it can be altered?
I worked a way round it. I read the selected records on the form's MouseUp event. The last thing to happen on the form in the event of a mouse click will either be the record selection, in which case the selected records are identified, or something else will get clicked, in which case the selection will revert to a single record anyway.
So in the event of multiple records being selected I can store the SelTop and SelHeight values in global variables and read them when my print button is pressed.
That works? In answer to your original question "Is it possible to use the record selectors on a continuous form to do anything useful", I always turn that off. Clicking on them would trigger the current event, which you could use to grab the ID of the selected record. I can't see offhand how you could use the SelTop and SelHeight values to identify records. I would probably have done this:
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:
'---------------------------------------------------------------
'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:
'-----------------------------------------------------------------------
'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:
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.
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:
'---------------------------------------------------------------
'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:
'-----------------------------------------------------------------------
'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:
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.