Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do I access the selected rows in Access?

I have a form which includes a data sheet. I would like to make it possible for a user to select multiple rows, click on a button and have some sql query run and perform some work on those rows.

Looking through my VBA code, I see how I can access the last selected record using the CurrentRecord property. Yet I don't see how I can know which rows were selected in a multiple selection. (I hope I'm clear...)

What's the standard way of doing this? Access VBA documentation is somewhat obscure on the net...

Thanks!

like image 720
koni Avatar asked Nov 03 '09 20:11

koni


People also ask

How do you select certain rows in Access?

To select a single record, click the record selector column (the leftmost column in the table). The record is highlighted. To select a block of records, click the record selector column next to the first record in the block, press and hold down the Shift key, and click the last record's selector column.

How do I Access fields in Access?

On the Home tab, in the Views group, click View, and then click Datasheet View. On the Fields tab, in the Add & Delete group, click More Fields. Select a field in the More Fields list to insert the new column. Access places the field to the right of the column where your cursor is currently located.


1 Answers

Here is the code to do it, but there is a catch.

Private Sub Command1_Click()
     Dim i As Long
     Dim RS As Recordset
     Dim F As Form

     Set F = Me.sf.Form
     Set RS = F.RecordsetClone

     If F.SelHeight = 0 Then Exit Sub

     ' Move to the first selected record.
     RS.Move F.SelTop - 1

     For i = 1 To F.SelHeight
       MsgBox RS![myfield]
       RS.MoveNext
     Next i

End Sub

Here's the catch: If the code is added to a button, as soon as the user clicks that button, the selection is lost in the grid (selheight will be zero). So you need to capture that info and save it to a module level variable either with a timer or other events on the form.

Here is an article describing how to work around the catch in some detail.
http://www.mvps.org/access/forms/frm0033.htm

Catch 2: This only works with contiguous selections. They can't select mutliple non-sequential rows in the grid.

Update:
There might be a better event to trap this, but here is a working implementation using the form.timerinterval property that i have tested (at least in Access 2k3, but 2k7 should work just fine)

This code goes in the SUBFORM, use the property to get the selheight value in the master form.

Public m_save_selheight As Integer

Public Property Get save_selheight() As Integer
    save_selheight = m_save_selheight
End Property

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

Private Sub Form_Timer()
    m_save_selheight = Me.selheight
End Sub
like image 99
JohnFx Avatar answered Sep 27 '22 21:09

JohnFx