oli,
Well, I was once in a similar position and wrote the function below, ACFormRecordSetCloneWithFilterSort, whose code is at the end of
this message.
However, I would up realizing that what I was trying to do could benefit from Sylvain's earlier advice, "Try using bookmark instead
of AbsolutePosition.", and I think you can too....
What you need to do is somthing like this:
1) take note of which rows in your fsub are selected (take a copy of SelTop and SelHeight)
2) step through those rows one at a time by resetting the SelTop starting at origSelTop through orgSelTop
3)
Here's some useless code that shows the approach. It works in an ADP. It presents the user with a message box of the first field
in the underlying recordset for each selected row. Modify it to your needs. It expects to work with an active datasheet. To make
it work with subforms is possible, but there are a few tricks you'll have to figure out...
Public Function HackSelection() As Boolean
' NOTE: this must be called from macro or menu or autokeys - NOT from anything that changes the focus away form the selection
On Error GoTo HandleErr
Dim frm2Hack As Form
Set frm2Hack = Screen.ActiveDatasheet
If frm2Hack Is Nothing Then Err.Raise 513, , "There is no active datasheet to hack"
With frm2Hack
If (.SelHeight = 0) Then ' gratuitously select the entire row
.SelHeight = 1
End If
If (.SelHeight = 1 + .Recordset.RecordCount) Then ' an entire column is selected and it includes the 'new' row...
.SelHeight = .Recordset.RecordCount 'so, unselect the 'new' row (since it is not a real row).
End If
Dim HackTop As Long, HackHeight As Long, HackLeft As Long, HackWidth As Long
HackTop = .SelTop
HackLeft = .SelLeft
HackHeight = .SelHeight
HackWidth = .SelWidth
End With
Dim rsHackMe As ADODB.Recordset
Set rsHackMe = frm2Hack.Recordset.Clone
Dim RowToHack As Long
For RowToHack = HackTop To HackTop + HackHeight - 1
frm2Hack.SelTop = RowToHack
frm2Hack.SelHeight = 1
rsHackMe.Bookmark = frm2Hack.Recordset.Bookmark
'<YOUR HACK CODE GOES HERE INSTEAD OF MINE'
MsgBox rsHackMe.Fields(1).value
Next
With frm2Hack ' restore selection
.SelTop = HackTop
.SelHeight = HackHeight
.SelLeft = HackLeft
.SelWidth = HackWidth
End With
ExitHere:
Exit Function
' Error handling block added by VBA Code Commenter and Error Handler Add-In. DO NOT EDIT this block of code.
HandleErr:
Select Case Err.Number
Case 513
MsgBox Err.description
Case Else
'' put your error code here
End Select
Resume ExitHere
' End Error handling block.
End Function
I DON'T recommend trying to use the following proc. The above approach should get you where you want to go. Nonetheless, to show
you what might be possible....
Function ACFormRecordSetCloneWithFilterSort(f As Form) As ADODB.Recordset
'returns a recordset as a clone of the form's recordset after
'making sort and filter agree with the forms (client side) soft and filter.
'A TOTAL HACK!?!?!? Access does not otherwise make this possible.
Set ACFormRecordSetCloneWithFilterSort = f.Recordset.clone
Dim strNewFilter As String
Dim strNewSort As String
Static re As Object
If re Is Nothing Then
Set re = CreateObject("VBScript.RegExp")
With re
.Global = True
.IgnoreCase = True
.Multiline = True
.Pattern = "\w+\."
End With
End If
With f
If .FilterOn Then
strNewFilter = .Filter
With re
strNewFilter = .Replace(strNewFilter, "")
End With
strNewFilter = Replace(strNewFilter, " ALIKE ", " LIKE ")
strNewFilter = Replace(strNewFilter, "*", "%")
strNewFilter = Replace(strNewFilter, "?", "_")
strNewFilter = Replace(strNewFilter, Chr(34), "'")
ACFormRecordSetCloneWithFilterSort.Filter = strNewFilter
End If
If .OrderByOn Then
strNewSort = .OrderBy
With re
strNewSort = .Replace(strNewSort, "")
End With
ACFormRecordSetCloneWithFilterSort.Sort = strNewSort
End If
End With
'
'Bye_ACFormRecordSetCloneWithFilterSort:
' ' Return the result.
'
'Exit Function
'
'Err_ACFormRecordSetCloneWithFilterSort:
'
'Resume Bye_ACFormRecordSetCloneWithFilterSort
'
'End Function
Cheers,
