vba 如何访问 Access 中的选定行？

How do I access the selected rows in Access?

提问by karlipoppins

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.

我有一个包含数据表的表格。我想让用户可以选择多行，单击一个按钮并运行一些 sql 查询并对这些行执行一些工作。

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...)

查看我的 VBA 代码，我看到了如何使用 CurrentRecord 属性访问最后选择的记录。但是我不知道如何知道在多项选择中选择了哪些行。（我希望我很清楚...）

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

这样做的标准方法是什么？Access VBA 文档在网上有点晦涩...

Thanks!

谢谢！

采纳答案by JohnFx

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.

这里有一个问题：如果代码被添加到一个按钮上，一旦用户单击该按钮，网格中的选择就会丢失（selheight 将为零）。因此，您需要捕获该信息并将其保存到模块级变量中，或者使用计时器或表单上的其他事件。

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

这是一篇文章，详细描述了如何解决问题。
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.

Catch 2：这仅适用于连续选择。他们不能在网格中选择多个非连续行。

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)

更新：
可能有更好的事件来捕获这个，但这里是一个使用 form.timerinterval 属性的工作实现，我已经测试过（至少在 Access 2k3 中，但 2k7 应该可以正常工作）

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

此代码位于 SUBFORM 中，使用该属性获取主窗体中的 selheight 值。

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

回答by DJ.

I used the technique similar to JohnFx

我使用了类似于 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

回答by Ben McCormack

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.).

我以前尝试过做类似的事情，但我从未成功使用要求用户选择与 Windows 文件对话框样式相同的多行（按 Ctrl、Shift 等）的方法。

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.

即使在 Access 中，我发现有时直接使用表和查询比尝试使用 Access 表单中的内置工具更容易。有时，内置工具并不能完全按照您的意愿行事。

回答by prossel

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.

注意：如果您想在按钮处理程序中使用选择，则该选择在执行时可能尚未恢复。确保使用变量中保存的值或在按钮处理程序的开头添加 DoEvents 以让计时器处理程序首先执行。

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

回答by user2777544

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

回答by larryP

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

回答by baldguy70

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.?

为什么不使用数组或记录集，然后每次用户单击一行（无论是否连续，将该行或某个标识符保存到记录集中。然后当他们单击父窗体上的按钮时，只需迭代记录集保存做你想做的事。点击按钮后不要忘记清除数组或记录集。？

回答by Bill Rigley

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.

在尝试执行过程时保持选择的另一种解决方法 - 无需离开数据表来激活按钮，只需使用 OnKeyDown 事件并定义特定的键码和移位组合来执行您的代码。

回答by Jean-Marc

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.

JohnFx 提供的代码运行良好。我以这种方式在没有计时器的情况下实现了它（MS-Access 2003）：
1- 将表单的键预览设置为是
2- 将代码放入函数中
3- 设置事件 OnKeyUp 和 OnMouseUp 以调用该函数。

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