如何根据另一个字段中的查找值返回一个字段中的值 [英] How to return the value in one field based on lookup value in another field

问题描述

这是基本的东西，但我对VBA和Word / Access对象模型有些陌生。

This is basic stuff, but I'm somewhat unfamiliar with VBA and the Word/Access object models.

我有一个两列数据库，大约117000条记录。列是'surname'和'count'。我想让用户能够在文本框中键入SMITH并点击submit。然后我要运行像

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

SELECT table.count FROM table WHERE surname = string

并返回一个字符串中的table.count的值。

and return the value of table.count in a string.

感觉这应该是五行或六行代码

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!

干杯

推荐答案

首先，小心命名列count - 这是SQL中的关键字，可能会导致问题。同样，不要调用表'table'。

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

这里是一些示例代码，显示了一种方法：

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

这篇关于如何根据另一个字段中的查找值返回一个字段中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！