vba 如何在 Access 中搜索表中的字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12183879/
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
How to search a field in a table in Access
提问by Paolo Bernasconi
Using VBA, how can I search for a text string, for example "CHIR", in a table called "ServiceYES", in the field "Service".
使用 VBA，如何在名为"ServiceYES"的表中的"Service"字段中搜索文本字符串，例如"CHIR "。
After that, I would like to save the neighboring field for all the rows that "CHIR"exists in the table "ServicesYES". The "ServiceYES"table is below:
之后，我想为表"ServicesYES" 中存在"CHIR" 的所有行保存相邻字段。该“ServiceYES”表如下：
I basically, want to find all the "CHIR"in "Service" column and then save the names which are on the left of the CHIR, eg "FRANKL_L", "SANTIA_D"as an array.
我基本上想在“服务”列中找到所有“CHIR”，然后将 CHIR 左侧的名称保存为数组，例如“FRANKL_L”、“SANTIA_D”。
Thanks for all your help in advance.
提前感谢您的所有帮助。
回答by HansUp
Start by creating a SELECTquery.
首先创建一个SELECT查询。
SELECT Code_Perso
FROM ServicesYES
WHERE Service = 'CHIR';
Use SELECT DISTINCT Code_Persoif you want only the unique values.
使用SELECT DISTINCT Code_Perso，如果你只想要独特价值。
Add ORDER BY Code_Persoif you care to have them sorted alphabetically.
ORDER BY Code_Perso如果您想让它们按字母顺序排序，请添加。
Once you have a satisfactory query, open a DAO recordset based on that query, and loop through the Code_Persovalues it returns.
一旦有了满意的查询，打开基于该查询的 DAO 记录集，并循环遍历Code_Perso它返回的值。
You don't need to load them directly into your final array. It might be easier to add them to a comma-separated string. Afterward you can use the Split()function (assuming you have Access version >= 2000) to create your array.
您不需要将它们直接加载到最终数组中。将它们添加到逗号分隔的字符串中可能更容易。之后您可以使用该Split()函数（假设您的 Access 版本 >= 2000）来创建您的阵列。
Here's sample code to get you started. It's mostly standard boiler-plate, but it might actually work ... once you give it "yourquery".
这是帮助您入门的示例代码。它主要是标准的样板，但它实际上可能有效......一旦你给它“你的查询”。
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strItems As String
Dim varItems As Variant
Set db = CurrentDb
Set rs = db.OpenRecordset("yourquery", dbOpenSnapshot)
With rs
Do While Not .EOF
strItems = strItems & "," & !Code_Perso
.MoveNext
Loop
.Close
End With
If Len(strItems) > 0 Then
' discard leading comma '
strItems = Mid(strItems, 2)
varItems = Split(strItems, ",")
Else
MsgBox "Oops. No matching rows found."
End If
Set rs = Nothing
Set db = Nothing
回答by PowerUser
I tested this and it seems to work. This function will pull all records where ServiceYes='CHIR' and dump the Code_Person value into an array which it will return:
我对此进行了测试，它似乎有效。此函数将提取 ServiceYes='CHIR' 的所有记录并将 Code_Person 值转储到它将返回的数组中：
Function x() As String()
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset( _
"Select * from ServiceYES where Service='CHIR'")
Dim Arr() As String
Dim i As Integer
While rst.EOF = False
ReDim Preserve Arr(i)
Arr(i) = rst.Fields("Code_Person")
i = i + 1
rst.MoveNext
Wend
x = Arr
End Function
Sample Usage:
示例用法：
Debug.Print x()(0)
回答by Chris
Paolo,
保罗，
Here is something I threw together in a few minutes. You can add it to the VBA editor in a module. It uses a trick to get the RecordCount property to behave properly. As for returing the array, you can update the function and create a calling routine. If you need that bit of code, just post a comment.
这是我在几分钟内拼凑起来的东西。您可以将其添加到模块中的 VBA 编辑器中。它使用一种技巧来使 RecordCount 属性正常运行。至于返回数组，您可以更新函数并创建调用例程。如果您需要那部分代码，只需发表评论。
Thanks!
谢谢！
Option Compare Database
Function QueryServiceYES()
Dim db As Database
Dim saveItems() As String
Set db = CurrentDb
Dim rs As DAO.Recordset
Set rs = db.OpenRecordset("SELECT Code_Perso, Service, Favorites " & _
"FROM ServiceYES " & _
"WHERE Service = 'CHIR'")
'bug in recordset, MoveFirst, then MoveLast forces correct invalid "RecordCount"
rs.MoveLast
rs.MoveFirst
ReDim Preserve saveItems(rs.RecordCount) As String
For i = 0 To rs.RecordCount - 1
saveItems(i) = rs.Fields("Code_Perso")
rs.MoveNext
Next i
'print them out
For i = 0 To UBound(saveItems) - 1
Debug.Print saveItems(i)
Next i
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Function
