Home  |   French  |   About  |   Search  | mvps.org  

What's New
Table Of Contents
10 Commandments 

In Memoriam

Terms of Use

VB Petition

Forms: Determine selected records in datasheet view

Dev Ashish

(Q)    I want to perform some action based on which records the user has selected in the datasheet view (form/subform).  How can I determine which records are selected?

(A)    Starting with Access 95, SelTop, SelWidth, SelHeight, and SelLeft properties are at now available for this purpose.  To determine which records are selected, we can use the SelTop and SelHeight properties.

    Unfortunately the event that runs this code must either be present behind the form's OnTimer event or a separate custom toolbar.  This is due to the fact that the selection is valid only as long as the control focus is on the datasheet.   As soon as the focus moves to another control (for example when you click on a command button), the selection is invalidated and the aforementioned properties will not be of any use.

    As an example, here's some code to determine (using the OnTimer event) to determine which records have been selected in a subform and print those using a report.

Create a form level variable.
Dim mfPrint As Boolean

Put following code in Form's Open Event

Private Sub Form_Open(Cancel As Integer)
mfPrint = False
End Sub

Set the Form's Timer Property to 5000, and paste the following code in the Timer Event

'************* Code Start **************
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
' Code Courtesy of
' Dev Ashish
Private Sub Form_Timer()
Dim i As Long
Dim strSQL As String
Dim loqd As QueryDef

    If Me.SelHeight = 0 Or mfPrint Then Exit Sub

    strSQL = "Select * from [" & Me.RecordSource _
                & "] Where "

    With Me.RecordsetClone
        .Move Me.SelTop - 1

        For i = 1 To Me.SelHeight
            strSQL = strSQL & "ProductID = " & _
                            ![ProductID] & _
                            " or "
        Next i
        strSQL = Left$(strSQL, Len(strSQL) - 3)

        If MsgBox("Are you ready to print now?", _
                    vbQuestion + vbYesNo, _
                    "Please Confirm...") = vbYes Then

            Set loqd = CurrentDb.QueryDefs("qryProducts")
            loqd.SQL = strSQL
            'DoCmd.OpenReport "SomeReport", acViewPreview
            mfPrint = True
        End If
    End With
    Set loqd = Nothing
End Sub
'**************** Code End *****************

1998-2010, Dev Ashish & Arvin Meyer, All rights reserved. Optimized for Microsoft Internet Explorer