VBA Access 获取 RowSource 以查找查找值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18367441/
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
VBA Access getting RowSource to find lookup values
提问by MichaelTaylor3D
VBA noob here (as of this mourning),
VBA 菜鸟在这里（截至本次哀悼），
In MS Access I wrote a test function to find the value of a record base on some criteria you pass in.
在 MS Access 中，我编写了一个测试函数来根据您传入的某些条件查找记录的值。
The function seems to work fine except in cases where there is a lookup in the column that I am searching.
该函数似乎工作正常，除非在我正在搜索的列中有查找的情况。
Basically it might return "19" and 19 corresponds to some other table value.
基本上它可能会返回“19”，而 19 对应于其他一些表值。
It seems that the RowSource of the column is what Im after so I can do a second query to find the true value.
似乎该列的 RowSource 是 Im 之后的内容，因此我可以进行第二次查询以找到真正的值。
Can someone point me in the right direction on finding the RowSource assuming I know the column name and then utilizing it to find the value Im after?
假设我知道列名，然后利用它来查找我之后的值，有人可以指出我找到 RowSource 的正确方向吗？
Edit: It seems that Im not explaining myself clearly, Here is a picture of what I trying to get programatically
编辑：似乎我没有清楚地解释自己，这是我试图以编程方式获得的内容的图片
回答by ron tornambe
If I understand your question correctly, I think using a parameter query will solve your problem. Using parameters is good practice since they will perform implicit data type casts and also prevent injection attacks.
如果我正确理解您的问题，我认为使用参数查询将解决您的问题。使用参数是一种很好的做法，因为它们将执行隐式数据类型转换并防止注入攻击。
Notice in the following function, I changed the lookupValue to a Variant type, which allows you to pass any type of value to the function.
请注意，在以下函数中，我将 lookupValue 更改为 Variant 类型，这允许您将任何类型的值传递给函数。
Public Function lookUpColumnValue( _
database As DAO.database, _
column As String, _
table As String, _
lookUpColumn As String, _
lookUpValue As Variant) As String
Dim sql As String
Dim recordSet As DAO.recordSet
Dim result As String
Dim qd As QueryDef
Set qd = database.CreateQueryDef("")
sql = "SELECT [" + table + "].[" + column + "] FROM [" + table + "] " & _
"WHERE [" + table + "].[" + lookUpColumn + "] = [parm1];"
qd.sql = sql
qd.Parameters![parm1] = lookUpValue
Set recordSet = qd.OpenRecordset()
result = recordSet(column)
EDIT
编辑
lookUpColumnValue = DLookup("Space Use Description", "Space Use Codes", result)
End Function
回答by transistor1
Try this -- I think I finally understand why you are looking for the RowSource -- sorry I didn't "get" it at first. The field you're trying to pull is a foreign key into a description table.
试试这个——我想我终于明白你为什么要寻找 RowSource——对不起，我一开始没有“得到”它。您尝试提取的字段是描述表中的外键。
This function should work as a general solution for all such fields (assuming the RowSource always has the primary key first, and the description second). If there is no RowSource, it will just pull the value of the field.
此函数应作为所有此类字段的通用解决方案（假设 RowSource 始终首先具有主键，其次才是描述）。如果没有 RowSource，它只会拉取字段的值。
It's based on your original code, rather than the changes proposed by @ron, but it should set you in the right direction. You should fix it to make it parameterized, and allow for variant data types, as ron suggests (+1 ron)
它基于您的原始代码，而不是@ron 提出的更改，但它应该使您朝着正确的方向前进。您应该修复它以使其参数化，并允许可变数据类型，正如 ron 建议的那样（+1 ron）
As an aside, use the ampersand (&) to join strings together in VBA to avoid things like this: abc = "1" + 1, where abc is now equal to 2 instead of "11" as you would expect if both items were intended to be strings.
顺便说一句&，在 VBA 中使用和号 ( ) 将字符串连接在一起以避免这样的事情：abc = "1" + 1，其中 abc 现在等于 2 而不是“11”，如果两个项目都打算是字符串，则您会期望。
Public Function lookUpColumnValue(Database As Database, column As String, table As String, lookUpColumn As String, lookUpValue As String) As String
Dim sql As String
Dim recordSet As DAO.recordSet
Dim result As String
lookUpColumnValue = "" 'Return a blank string if no result
On Error Resume Next
sql = "SELECT [" & table & "].[" & column & "] FROM [" & table & "] WHERE [" & table & "].[" & lookUpColumn & "] = '" & lookUpValue & "'"
Set recordSet = Database.OpenRecordset(sql)
If Not recordSet.EOF Then
Dim td As DAO.TableDef
'this gives your number - say, 19
result = recordSet(column)
Set td = Database.TableDefs(table)
'Get the rowsource
Dim p As DAO.Property
For Each p In td.Fields(column).Properties
If p.Name = "RowSource" Then
RowSource = Replace(td.Fields(column).Properties("RowSource"), ";", "")
Exit For
End If
Next
If Not RowSource = "" Then
Dim rs2 As DAO.recordSet
Dim qd As DAO.QueryDef
Set qd = Database.CreateQueryDef("", RowSource)
Set rs2 = Database.OpenRecordset(RowSource)
If rs2.EOF Then Exit Function
PKField = rs2.Fields(0).Name
rs2.Close
qd.Close
sql = "SELECT * FROM (" & RowSource & ") WHERE [" & PKField & "]=[KeyField?]"
Set qd = Database.CreateQueryDef("", sql)
qd.Parameters("KeyField?").Value = result
Set rs2 = qd.OpenRecordset()
If Not rs2.EOF Then
'NOTE: This assumes your RowSource *always* has ID first, description 2nd. This should usually be the case.
lookUpColumnValue = rs2.Fields(1)
End If
Else
'Return the field value if there is no RowSource
lookUpColumnValue = recordSet.Fields(column)
End If
End If
End Function
