Get the selected records from an Access table

Microsoft Access: Get the selected records from an Access table

Platform:

Microsoft Access

Task:

Discussion:

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.

Example:

'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.MoveFirst
        rs.Move SelectionTop - 1
        For i = 1 To SelectionHeight
           MsgBox rs!DocumentID & " " & rs!Title 'or rs!YourColumnName
           rs.MoveNext
        Next i
    End If
End Sub