(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
(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
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
Set the Form's Timer Property to 5000, and paste the following code in the Timer Event
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 "
.Move Me.SelTop - 1
For i = 1 To Me.SelHeight
strSQL = strSQL & "ProductID = " & _
![ProductID] & _
" or "
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
mfPrint = True
Set loqd = Nothing