How do I access the selected rows in Access?

Question

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!

Larry

Answer 1

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

Covey

amazing... that's why I kept getting selheight at 0... Thanks a lot I will try that right away and accept your answer if it worked out! – Larry

The trick is grabbing the value before it loses focus. In what event are you grabbing the selheight value? Did you try it in a timer in the subform? Try checking the value in the immediate window (ctrl-g) and see if the issue is the selection resetting after losing focus. – Covey

Is an event registered each time the user clicks an item to select? If so, you could use that event to capture everything the user has selected each time they click. It's repetitive, sure, but it would seem to guarantee that you always had the most recent list of selected items when the focus changes to the button control. – Neritic

I added some code to my answer that shows how to do this with the TimerInterval event. There might be a better event that is more closely linked to changing the selection, but I'm not sure offhand what it is. I tried SelectionChange (which seemed most appropriate, but it didn't work for me either). You might want to just experiment with moving the code to the events that look promising until you get a good result. Worst case, the timerinterval approach DOES work. – Covey

I couldn't get this to work either until I added this code. It just copies the value from the property where it's saved back to the forms .selheight property. F.SelHeight = save_selheight That way when F.SelHeight is checked it will equal the value the timer grabbed. – Afterbrain

You can use a label as a button, so the selected records will not lose focus when the label is clicked and correct SelHeight number is returned – Belgrade

Answer 2

I used the technique similar to JohnFx

To trap the Selection height before it disappears I used the Exit event of the subform control in the Main form.

So in the Main form:

Private Sub MySubForm_Exit(Cancel As Integer)

  With MySubForm.Form
    m_SelNumRecs = .SelHeight
    m_SelTopRec = .SelTop
    m_CurrentRec = .CurrentRecord
  End With

End Sub

Shum

This is much more elegant and efficient than the form Timer event! If there is necessary code to run on the SubForm, a public method of the SubForm can be called from the parent form in the same Exit event handler: MySubForm.Form.PublicMethod. The SelHeight and SelTop values will still be valid during the execution of that method. The benefit to this pattern is that the because the working code is in the SubForm module, it will be reusable if the SubForm is on multiple parent forms... so duplicated code on the parent forms is minimal. – Overcrop

.CurrentRecord is just a number. How do you get the actual field values of that record? – Emissive

Answer 3

I've tried doing something like that before, but I never had any success with using a method that required the user to select multiple rows in the same style as a Windows File Dialog box (pressing Ctrl, Shift, etc.).

One method I've used is to use two list boxes. The user can double click on an item in the left list box or click a button when an item is selected, and it will move to the right list box.

Another option is to use a local table that is populated with your source data plus boolean values represented as checkboxes in a subform. After the user selects which data they want by clicking on checkboxes, the user presses a button (or some other event), at which time you go directly to the underlying table of data and query only those rows that were checked. I think this option is the best, though it requires a little bit of code to work properly.

Even in Access, I find sometimes it's easier to work with the tables and queries directly rather than trying to use the built-in tools in Access forms. Sometimes the built-in tools don't do exactly what you want.

Neritic

+1 for your second option, though I'd rather not do that if possible... Hopefully Microsoft came up with an obscure way to access those selected rows...? – Larry

Which version of Access are you using? I use Access 2002 at work. Interestingly, I just flipped through "Access 2002 Developer's Handbook" (Considered the Access Developer's Bible) and they have a class they created called MultiPik. This class does allow for selecting multiple items at a time, but they say it's slow when you have tons of items (they say 300 items is slow). If you're interested, I can look into the legality of posting the code for this class on Stack Overflow. Let me know. – Neritic

well i'm using 2007 and my table has way more than 10k entries so that probably wouldn't work well.. – Larry

Seeing how Catch 2 from JohnFx's answer may prevent your solution from working, do you want me to show you how do to option 2 from my solution above? It would take me a little while to type out the code, so I'll gladly do it if you'd like to see it. – Neritic

Given the inability of selections in a subform control to be non-contiguous, I think a multiselect listbox or a boolean checkbox in a temp table is preferable. I always use the latter, myself. It's really easy to implement and the users understand how it works (they aren't so comfortable with multiselect listboxes, even the "simple" version, which is a pain for selecting multiple items (i.e., the SHIFT key doesn't do anything). – Kibitzer

Answer 4

A workaround to the selection loss when the sub form loses the focus is to save the selection in the Exit event (as already mentioned by others).

A nice addition is to restore it immediately, using timer, so that the user is still able to see the selection he made.

Note: If you want to use the selection in a button handler, the selection may not be restored already when it executes. Make sure to use the saved values from the variables or add a DoEvents at the beginning of the button handler to let the timer handler execute first.

Dim m_iOperSelLeft As Integer
Dim m_iSelTop As Integer
Dim m_iSelWidth As Integer
Dim m_iSelHeight As Integer

Private Sub MySubForm_Exit(Cancel As Integer)

    m_iSelLeft = MySubForm.Form.SelLeft
    m_iSelTop = MySubForm.Form.SelTop
    m_iSelWidth = MySubForm.Form.SelWidth
    m_iSelHeight = MySubForm.Form.SelHeight

    TimerInterval = 1

End Sub

Private Sub Form_Timer()

    TimerInterval = 0

    MySubForm.Form.SelLeft = m_iSelLeft - 1
    MySubForm.Form.SelTop = m_iSelTop
    MySubForm.Form.SelWidth = m_iSelWidth
    MySubForm.Form.SelHeight = m_iSelHeight

End Sub

Distefano

Answer 5

There is another solution.

The code below will show the number of selected rows as soon as you release the mouse button. Saving this value will do the trick.

Private Sub Form_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)

        MsgBox Me.SelHeight

End Sub

Rival

Hey, you might want to fix the formatting of your code, the beginning and end are not indented. – Granulocyte

This works well but is limited to only mouse operations. The keyboard can also be used to select multiple columns and rows, but this won't capture those. Regrettably, the KeyPress and KeyUp events don't seem to capture the control and arrow keys when navigating around a DataSheet. The other posted solutions involving the parent Exit event are more general and aren't dependent upon the selection method. – Overcrop

Answer 6

Use a Global variable in the form, then refer to that in the button code.

Dim g_numSelectedRecords as long

Private Sub Form_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
   g_numSelectedRecords = Me.SelHeight
End Sub


Dim formRecords As DAO.Recordset
Dim i As Long

Set formRecords = Me.RecordsetClone

' Move to the first record in the recordset.
formRecords.MoveFirst

' Move to the first selected record.
formRecords.Move Me.SelTop - 1

For i = 1 To numSelectedRecords
    formRecords.Edit
    formRecords.Fields("Archived") = True
    formRecords.Update
    formRecords.MoveNext
Next i

Glee

Answer 7

Why not use an array or recordset and then every time the user clicks on a row (either contiguous or not, save that row or some identifier into the recordset. Then when they click the button on the parent form, simply iterate the recordset that was saved to do what you want. Just don't forget to clear the array or recordset after the button is clicked.?

Vivyanne

Answer 8

Another workaround to keeping the selection while attempting to execute a procedure - Instead of leaving the datasheet to activate a button, just use the OnKeyDown event and define a specific keycode and shift combination to execute your code.

Want

Answer 9

If you are trying to capture selheight and seltop on the current form you could try capturing them in the MouseUp and KeyUp event and save them in a form level variable.

Winebaum

Hasn't this been said as well as coded here before? – Phelan

Answer 10

The code provided by JohnFx works well. I implemented it without a timer this way (MS-Access 2003):
1- Set the Form's Key Preview to Yes
2- put the code in a function
3- set the event OnKeyUp and OnMouseUp to call the function.

Option Compare Database
Option Explicit

Dim rowSelected() As String

Private Sub Form_Load()
'initialize array
ReDim rowSelected(0, 2)
End Sub

Private Sub Form_Current()
' if cursor place on a different record after a selection was made 
' the selection is no longer valid
If "" <> rowSelected(0, 2) Then
  If Me.Recordset.AbsolutePosition <> rowSelected(0, 2) Then
    rowSelected(0, 0) = ""
    rowSelected(0, 1) = ""
    rowSelected(0, 2) = ""
  End If
End If
End Sub

Private Sub Form_KeyUp(KeyCode As Integer, Shift As Integer)
rowsSelected
If KeyCode = vbKeyDelete And Me.SelHeight > 0 Then
    removeRows
End If
End Sub

Private Sub Form_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
rowsSelected
End Sub

Sub rowsSelected()
Dim i As Long, rs As DAO.Recordset, selH As Long, selT As Long
selH = Me.SelHeight
selT = Me.SelTop - 1
If selH = 0 Then
    ReDim rowSelected(0, 2)
    Exit Sub
Else
    ReDim rowSelected(selH, 2)
    rowSelected(0, 0) = selT
    rowSelected(0, 1) = selH
    rowSelected(0, 2) = Me.Recordset.AbsolutePosition ' for repositioning 
    Set rs = Me.RecordsetClone
    rs.MoveFirst ' other key touched caused the pointer to shift
    rs.Move selT
    For i = 1 To selH
        rowSelected(i, 0) = rs!PositionNumber
        rowSelected(i, 1) = Nz(rs!CurrentMbr)
        rowSelected(i, 2) = Nz(rs!FutureMbr)
        rs.MoveNext
    Next
    Set rs = Nothing
    Debug.Print selH & " rows selected starting at " & selT 
End If
End Sub

Sub removeRows()
' remove rows in underlying table using collected criteria in rowSelected()
    Me.Requery
' reposition cursor
End Sub

Private Sub cmdRemRows_Click()
If Val(rowSelected(0, 1)) > 0 Then
    removeRows
Else
    MsgBox "To remove row(s) select one or more sequential records using the record selector on the left side."
End If
End Sub

Sapir

Forgot to mention: my scenario involves selecting records in a form where the source query is not updatable and users need to delete the record; not a subform. – Sapir