Microsoft Access: Get the selected records from an Access table

Platform:Microsoft Access
Task:Get the selected records from an Access table
You need to get the selected records in an Access table. Normally this would be easy, but as usual Microsoft messed it all up. Each table has a .SelTop and .SelHeight property which delineates selected rows in the table. You can make a recordsetclone and loop through the selected records using those properties. The problem comes when you try to get .SelTop and .SelHeight from a button. Once you click the button the needed properties are set to zero. To get at them you need to make a module level variable and pop the .SelHeight into the variable until you can use it. You need to set the module level variable via the Form_Timer event. I gotta be honest, I'm not sure why it works, but it does.
'in the subform
Public mSelectionHeight As Integer

Public Property Get SelectionHeight() As Integer
    SelectionHeight = mSelectionHeight
End Property

Private Sub Form_Open(Cancel As Integer)
    Me.TimerInterval = 500
End Sub

Private Sub Form_Timer()
    mSelectionHeight = Me.SelHeight
End Sub

'in the main form
Private Sub cmdGetSelectedRows_Click()
    Dim i As Integer
    'make a clone of the table's recordset
    Dim rs As ADODB.Recordset
    Set rs = Me.frmDocumentsChild.Form.RecordsetClone
    Dim SelectionTop As Integer
    SelectionTop = Me.frmDocumentsChild.Form.SelTop
    Dim SelectionHeight As Integer
    SelectionHeight = Me.frmDocumentsChild.Form.SelectionHeight
    'if there are selected records loop through them
    If SelectionHeight > 0 Then
        rs.Move SelectionTop - 1
        For i = 1 To SelectionHeight
           MsgBox rs!DocumentID & " " & rs!Title 'or rs!YourColumnName
        Next i
    End If
End Sub
Back to Index