vba 为表单选择特定记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1782560/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Choosing specific record for form
提问by Justin
On this form, I create a grid, almost looks like a bar chart with little "cells" stacked by month. Each of these "cells" are square sub forms, and I create the little square forms that I use as the sub forms.
在这个表单上，我创建了一个网格，它几乎看起来像一个条形图，上面有按月堆积的小“单元格”。这些“单元格”中的每一个都是方形子表单，我创建了用作子表单的小方形表单。
So what I wanted to know is, what is the code (VB) for running a select query, and only working with specific records within that query? For example, each one of these little "cell" like forms that become the sub-forms, represent a organization, and if I run a top Nth query I get the 10 Top store by whatever....so the idea is to use each little form as a representative of each record from that query. So for the first cell I want to run the SELECT query, get the results, and work only with the first record. Then on the second form, run the exact same query, and work only with the second record, and so on!
所以我想知道的是，运行选择查询的代码（VB）是什么，并且只处理该查询中的特定记录？例如，这些小“单元格”中的每一个都像成为子表单的表单，代表一个组织，如果我运行第 N 个查询，我会通过任何方式获得 10 Top 商店......所以这个想法是使用每个小表格代表该查询中的每条记录。所以对于第一个单元格，我想运行 SELECT 查询，获取结果，并且只处理第一条记录。然后在第二个表单上，运行完全相同的查询，并且只处理第二个记录，依此类推！
Its a little weird I suppose, but it will give them exactly what they want, and the only part I am not sure about, is the "defining exactly which record I want to use in the VBA"
我想这有点奇怪，但它会给他们他们想要的东西，我不确定的唯一部分是“准确定义我想在 VBA 中使用的记录”
回答by Fionnuala
You could change the SQL for each sub form to read:
您可以将每个子表单的 SQL 更改为：
SELECT TOP 1 ID, F1, F2 From Table
SELECT TOP 2 From Table WHERE ID NOT IN (SELECT TOP 1 ID From Table)
SELECT TOP 3 From Table WHERE ID NOT IN (SELECT TOP 2 ID From Table)
<...>
SELECT TOP 10 From Table WHERE ID NOT IN (SELECT TOP 9 ID From Table)
Or, seeing you already have a somewhat odd set-up, you can write each ID to one of 10 hidden textbox controls using a recordset, and use each of these textboxes as the link master field with ID as the link child field for the subforms.
或者，看到您已经有一些奇怪的设置，您可以使用记录集将每个 ID 写入 10 个隐藏的文本框控件之一，并将这些文本框中的每一个用作链接主字段，并将 ID 作为子表单的链接子字段.
Link Master Field and Link Child Field are properties of the subform control, not the form contained.
链接主字段和链接子字段是子窗体控件的属性，而不是包含的窗体。
回答by Adriaan Stander
If i understand your request correctly yuo can do something like this.
如果我正确理解你的要求，你可以做这样的事情。
Table Structure
表结构
ID Autonumber,
Col1 Text
VBCode
代码
Private Sub Command0_Click()
Dim rec As Recordset
Dim id As Integer
Set rec = CurrentDb.OpenRecordset("SELECT TOP 10 * FROM Table1")
While Not rec.EOF
id = rec.Fields("ID")
rec.MoveNext
Wend
End Sub
回答by Michael Dillon
You treat the SQL statement as the definition of a pseudoTable, i.e. a set of records (RecordSet) that you can manipulate as if it was a table.
您将 SQL 语句视为伪表的定义，即您可以像处理表一样操作的一组记录 (RecordSet)。
Dim cnn As ADODB.Connection
Dim pseudoTable As ADODB.Recordset
Dim strSQL As String
Set cnn = CurrentProject.Connection
Set pseudoTable = New ADODB.Recordset
strSQL = "SELECT Title FROM realTable where realID < 1000;"
pseudoTable.Open strSQL, cnn, adOpenStatic, adLockOptimistic
If Not pseudoTable.BOF And Not pseudoTable.EOF Then
pseudoTable.MoveFirst
Do Until pseudoTable.EOF
' do something with the table
pseudoTable.MoveNext
Loop
The above code should give you a good start.
上面的代码应该给你一个良好的开端。
