How to access selected rows in Access?

Question

I have a form that includes a data sheet. I would like the user to be able to select several rows, click on the button and run the SQL query and do some work on these rows.

Looking through my VBA code, I see how I can access the last selected record using the CurrentRecord property. However, I do not see how I can find out which rows were selected in multiple selections. (I hope I understand ...)

What is the standard way to do this? Access to VBA documentation is somewhat obscure on the net ...

Thanks!

Answer 1

Here is the code to do this, 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 the button as soon as the user clicks on that button, the selection is lost in the grid (selheight will be zero). Thus, you need to fix this information and save it at the module level using a timer or other events in the form.

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

Catch 2: This only works with related choices. They cannot select multiple disjoint lines in the grid.

Update:
Perhaps the best event for this, but here is a working implementation using the form.timerinterval property that I tested (at least in Access 2k3, but 2k7 should work fine)

This code goes to SUBFORM, use the property to get the selheight value in the main 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 

Answer 2

I used a technique similar to JohnFx

To catch the height of the selection 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 

Answer 3

I already tried to do something similar before, but I never had success using a method that required the user to select multiple lines in the same style as the Windows File dialog box (pressing Ctrl, Shift, etc. .) ..

One of the methods I used is to use two lists. The user can double-click on an item in the left list or click a button when the item is selected and it moves to the right list.

Another option is to use a local table filled with your source data, plus logical values ​​presented as flags in the subform. After the user selects what data they want by clicking on the checkboxes, the user clicks a button (or some other event), after which you go directly to the base data table and request only those rows that have been marked. I think this option is the best, although it requires a bit of code to work properly.

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

Answer 4

The workaround for losing choice when the subform loses focus is to keep the selection in the Exit event (as others have already mentioned). A.

A good addition is its immediate recovery using a timer so that the user can still see their choice.

Note. If you want to use the selection in the button handler, the selection cannot be restored already when it is executed. Be sure to use the stored values ​​from the variables or add DoEvents at the beginning of the button handler to allow the timer handler to execute the 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 

Answer 5

There is another solution.

The code below indicates the number of rows selected 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 

Answer 6

Use a global variable on the form, and then refer to it 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 

Answer 7

Why not use an array or recordset, and then every time the user clicks on a row (adjacent or not), save that row or some kind of identifier in the recordset. Then, when they click the button in the parent form, just iterate over which was saved to do what you want. Just remember to clear the array or recordset after clicking the button.?

Answer 8

Another solution to save your choice when trying to execute a procedure. Instead of leaving data to activate the button, simply use the OnKeyDown event and define a specific code and combination of offsets to execute your code.

Answer 9

The code provided by JohnFx works well. I implemented it without a timer this way (MS-Access 2003):
1- Set the form preview to “Yes” 2- put the code in the function
3- set the OnKeyUp and OnMouseUp events 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