使用VBA中的Recordset从查询返回字段值 [英] Return field values from a query using a Recordset in VBA

问题描述

我正在尝试使用VBA从查询中提取值。

具体来说，我想从查询中连接rosEmail

字段的所有值" qselRosterEmailList"成一个字符串（strEmails）。我可以通过使用DLookup功能获得一条记录的结果，当然，

但是我希望获得每条记录的价值。要做到这一点，我相信我需要

来构建一个记录集，但我不知道如何。


我读过有关使用DAO做的事情在Access 97，但我使用Access

2002.此外，我不想注册任何额外的库，因为我将

共享Access数据库其他人并且不希望那些用户

也要注册库。


最终结果应该是这样的：

strEmails =" pe ***** @ email.com， pe*****@email.com ， pe*****@email.com ，
pe ***** @ email.com "


感谢您的帮助，

David

I am trying to extract the values from a query using VBA.
Specifically, I want to concatenate all the values of the "rosEmail"
field from query "qselRosterEmailList" into one string (strEmails). I
can get one record''s result by using the DLookup fuction, of course,
but I want to get every record''s value. To do this, I believe I need
to build a recordset, but I do not know how.

I''ve read about doing it using DAO in Access 97, but I am using Access
2002. Also, I do not want to register any extra libraries, since I''ll
be sharing the Access database with others and do not want those users
to have to register libraries as well.

The final result should be something like:
strEmails = "pe*****@email.com, pe*****@email.com, pe*****@email.com,
pe*****@email.com"

Thanks for your help,
David

推荐答案

不幸的是，这段代码不起作用。我收到错误说

用户定义的类型未定义并且它不会比

第一行代码更远。虽然我不确定，但我认为这是因为它是DAO而不是ADO。你写了哦，所以把它转换成ADO。或者使用较晚的

绑定 - 你能详细说明怎么做吗？我不知道怎么回事
返回ADO中的Recordset。


另外，我在哪里定义提供这些数据的查询？ （strQuery =

qselRosterEmailList或类似的东西）


谢谢

Unfortunately, this code does not work. I get an error saying
"User-defined type not defined" and it doesn''t go farther than the
first line of code. Although I''m not sure, I think this is because
it''s DAO and not ADO. You wrote "Oh, so convert it to ADO. Or use late
binding" - can you elaborate on how to do this? I don''t know how to
return the Recordset in ADO.

Also, where do I define the query that feeds this data? (strQuery =
qselRosterEmailList or something like that)

Thanks

bdt513写道：

bdt513 wrote:

我试图使用VBA从查询中提取值。
具体来说，我想连接rosEmail的所有值。来自查询qselRosterEmailList的字段成一个字符串（strEmails）。
我可以通过使用DLookup功能得到一条记录的结果，当然，
但我希望获得每条记录的价值。要做到这一点，我相信我需要构建一个记录集，但我不知道如何。

我读过在Access 97中使用DAO这样做，但我正在使用
Access 2002.另外，我不想注册任何额外的库，因为
我将与其他人共享Access数据库，并且不希望那些
用户必须注册库最后的结果应该是这样的：
strEmails =" pe ***** @ email.com， pe ***** @ email.com ， pe ***** @ email。 com ，
pe*****@email.com "
<感谢您的帮助，
大卫

I am trying to extract the values from a query using VBA.
Specifically, I want to concatenate all the values of the "rosEmail"
field from query "qselRosterEmailList" into one string (strEmails). I can get one record''s result by using the DLookup fuction, of course,
but I want to get every record''s value. To do this, I believe I need
to build a recordset, but I do not know how.

I''ve read about doing it using DAO in Access 97, but I am using Access 2002. Also, I do not want to register any extra libraries, since I''ll be sharing the Access database with others and do not want those users to have to register libraries as well.

The final result should be something like:
strEmails = "pe*****@email.com, pe*****@email.com, pe*****@email.com,
pe*****@email.com"

Thanks for your help,
David

嗯...代码在哪里？

你基本上声明一个字符串变量然后在循环中通过

记录，你将每个值附加到字符串。


这是废话，但它有效......假设您从查询中获取信息...

选项比较数据库


公共函数AddressList（ByVal strQuery As String）As String

Dim db As DAO.Database

Dim qd As DAO.QueryDef

Dim rs As DAO.Recordset

Dim strList As String


设置db = DBEngine（ 0）（0）

设置qd = db.QueryDefs（strQuery）

设置rs = qd.OpenRecordset


Do Until rs.EOF

strList = strList& ， &安培; rs.Fields（2）.Value

rs.MoveNext

循环


AddressList = Right

Umm... where''s the code?
You basically declare a string variable and then inside looping through
the records, you append each value to the string.

This is crap, but it works... assumes you get your info from a query...
Option Compare Database

Public Function AddressList(ByVal strQuery As String) As String
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strList As String

Set db = DBEngine(0)(0)
Set qd = db.QueryDefs(strQuery)
Set rs = qd.OpenRecordset

Do Until rs.EOF
strList = strList & ", " & rs.Fields(2).Value
rs.MoveNext
Loop

AddressList = Right

（strList，Len（strList） - 2）


rs.Close

设置rs = Nothing

Set qd = Nothing

设置db = Nothing

结束功能


查询：

SELECT tblPeople。 FirstName，tblPeople.LastName，Left

(strList, Len(strList) - 2)

rs.Close
Set rs = Nothing
Set qd = Nothing
Set db = Nothing
End Function

query:
SELECT tblPeople.FirstName, tblPeople.LastName, Left

这篇关于使用VBA中的Recordset从查询返回字段值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！