vba 如何根据另一字段中的查找值返回一个字段中的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/505838/
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 return the value in one field based on lookup value in another field
提问by user51498
This is basic stuff, but I'm somewhat unfamiliar with VBA and the Word/Access object models.
这是基本的东西，但我对 VBA 和 Word/Access 对象模型有些陌生。
I have a two column database of about 117000 records. The columns are 'surname' and 'count'. I want a user to be able to type SMITH in a textbox and hit submit. I then want to run something like
我有一个包含大约 117000 条记录的两列数据库。列是“姓氏”和“计数”。我希望用户能够在文本框中键入 SMITH 并点击提交。然后我想运行类似的东西
SELECT table.count FROM table WHERE surname = string
SELECT table.count FROM table WHERE surname = string
and return the value of table.count in a string.
并以字符串形式返回 table.count 的值。
It feels like this should be five or six lines of code (which I have but won't post) but I'm obviously missing something!
感觉这应该是五六行代码（我有但不会发布）但我显然错过了一些东西！
Cheers
干杯
回答by Joel Spolsky
First of all, be careful naming the column 'count' -- this is a keyword in SQL and might cause problems. Similarly, don't call the table 'table'.
首先，命名列“count”时要小心——这是 SQL 中的关键字，可能会导致问题。同样，不要将表称为“表”。
Here is some sample code which shows one way of doing it:
这是一些示例代码，显示了一种方法：
' This example uses Microsoft ActiveX Data Objects 2.8,
' which you have to check in Tools | References
' Create the connection. This connection may be reused for other queries.
' Use connectionstrings.com to get the syntax to connect to your database:
Dim conn As New ADODB.Connection
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\tmp\Database1.accdb"
Dim cmd As New ADODB.Command
Set cmd.ActiveConnection = conn
' Replace anything which might change in the following SQL string with ?
cmd.CommandText = "select ct from tbl where surname = ?"
' Create one parameter for every ?
Dim param As ADODB.Parameter
Set param = cmd.CreateParameter("surname", adBSTR, adParamInput, , TextBox1.Text)
cmd.Parameters.Append param
Dim rs As ADODB.Recordset
Set rs = cmd.Execute
MsgBox rs("ct")
rs.Close
conn.Close
回答by Fionnuala
It is possible to use InsertDatabase:
可以使用 InsertDatabase：
Sub GetData()
ActiveDocument.Bookmarks("InsertHere").Select
Selection.Range.InsertDatabase Format:=0, Style:=0, LinkToSource:=False, _
Connection:="TABLE Members", SQLStatement:= _
"SELECT [Count] FROM [Members]" _
& " WHERE Surname='" _
& ActiveDocument.FormFields("Text1").Result & "'", _
DataSource:="C:\docs\ltd.mdb", From:=-1, To:= _
-1, IncludeFields:=True
End Sub
This is an edited macro recorded using the database toolbar.
这是使用数据库工具栏记录的编辑宏。
EDITEDWarning: this code, as shown, is subject to a SQL Injection attack.
已编辑警告：如图所示，此代码会受到 SQL 注入攻击。
