Return field values from a query using a Recordset in VBA

[bdt513]

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 "qselRosterEmai lList" 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.c om, pe*****@email.c om,
pe*****@email.c om"

Thanks for your help,
David

[bdt513]

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 =
qselRosterEmail List or something like that)

Thanks

[pietlinden]

bdt513 wrote:

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 "qselRosterEmai lList" 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.c om, pe*****@email.c om,
pe*****@email.c om"

Thanks for your help,
David

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(ByV al 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(st rQuery)
Set rs = qd.OpenRecordse t

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

AddressList = Right$(strList, Len(strList) - 2)

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

query:
SELECT tblPeople.First Name, tblPeople.LastN ame, Left$([Firstname],1) &
[LastName] & "@mail.net" AS Expr1
FROM tblPeople;
Oh, so convert it to ADO. Or use late binding.

[bdt513]

Here is the Recordset code I have now, and this produces an error at
the rs.Open line. "rosEmail" is the field I want and
"qselRosterEmai lList" is the query from which I want to read.

Private Sub cmdGenerateEmai lList_Click()

Dim temp, list As String
Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset
rs.Open "qselRosterEmai lList"

Do While Not rs.EOF
temp = rs!rosEmail
list = temp & ", "
rs.MoveNext
Loop

rs.Close

End Sub

[C.P.J. de Boer]

I think rs.Open "qselRosterEmai lList" needs a connection.
I often use the connection of the current project

rs.Open "qselRosterEmai lList", CurrentProject. Connection

And you might want to add further arguments after the conenction about the
way the recordset must be opened.

Kees de Boer

bdt513 wrote:

Here is the Recordset code I have now, and this produces an error at
the rs.Open line. "rosEmail" is the field I want and
"qselRosterEmai lList" is the query from which I want to read.

Private Sub cmdGenerateEmai lList_Click()

Dim temp, list As String
Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset
rs.Open "qselRosterEmai lList"

Do While Not rs.EOF
temp = rs!rosEmail
list = temp & ", "
rs.MoveNext
Loop

rs.Close

End Sub

[Bas Cost Budde]

C.P.J. de Boer wrote:

Did you work in The Hague in 1986?
--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

[bdt513]

I've modified the code, but I'm now getting a different error. For
some reason it does not like using a query as the Recordset on the
rs.Open command. When I use a table, I'm fine, but I need to pull this
data from a query. What do I need to do in order to allow me to use a
query to feed this Recordset? (I receive the error message, "No value
given for one or more parameters")

Here's the code as it stands now...

Sub EmailList()

Dim rs As ADODB.Recordset
Dim strEmails As String

Set rs = New ADODB.Recordset

rs.Open "Select * FROM qselRosterEmail List;", CurrentProject. Connection

While Not rs.EOF
strEmails = strEmails & ", " & rs.Fields(0).Va lue
rs.MoveNext
Wend
rs.Close
Set rs = Nothing

End Sub

[Darryl Kerkeslager]

> rs.Open "Select * FROM qselRosterEmail List", CurrentProject. Connection

As counterintuitiv e as it sounds, you cannot actually use
CurrentProject. Connection in this manner; you must decalre and use a
Connection object (and no, I don't know why):

Dim cnxn As ADODB.Connectio n
Dim rs As ADODB.Recordset
Set cnxn = CurrentProject. Connection
Set rs = New ADODB.Recordset

rs.Open "Select * FROM qselRosterEmail List", cnxn

Your code should run as above, however, I advise you to get in the habit of
specifying the parameters after the Connection, otherwise, Access uses the
defaults for the first two parameters, then figures the final one out on its
own (which slows down your code).

Filling in the default values, you have opened up this type of Recordset
/cursor:

rs.Open "Select * FROM qselRosterEmail List", cnxn, adOpenForwardOn ly,
adLockReadOnly

This is fine in your situation, but you will need other types of cursors
(Keyset, LockOptimistic)

Also, you should specify the final parameter, in order to speed up the code:
adCmdText for queries, adCmdTableDirec t for tables, or adCmdStoredProc for
Stored Procedures

rs.Open "Select * FROM qselRosterEmail List", cnxn, adOpenForwardOn ly,
adLockReadOnly, adCmdText
Darryl Kerkeslager
"bdt513" <BD****@gmail.c om> wrote:

I've modified the code, but I'm now getting a different error. For
some reason it does not like using a query as the Recordset on the
rs.Open command. When I use a table, I'm fine, but I need to pull this
data from a query. What do I need to do in order to allow me to use a
query to feed this Recordset? (I receive the error message, "No value
given for one or more parameters")

Here's the code as it stands now...

Sub EmailList()

Dim rs As ADODB.Recordset
Dim strEmails As String

Set rs = New ADODB.Recordset

rs.Open "Select * FROM qselRosterEmail List;", CurrentProject. Connection

While Not rs.EOF
strEmails = strEmails & ", " & rs.Fields(0).Va lue
rs.MoveNext
Wend
rs.Close
Set rs = Nothing

End Sub

[bdt513]

That was a very helpful post, and I appreciate the advice. Still,
however, the code does not work. I receive the same error on the
rs.Open line:
"No value given for one or more parameters"

When I run the same code but use a table instead of a query, the code
works. Short of using a make table, how can I use the query
(qselRosterEmai lList) to populate the Recordset? [The query does work
okay on its own. It opens properly and returns the right information]

Here's the code that's in my module:
-----------------------------
Sub EmailList()

Dim cnxn As ADODB.Connectio n
Dim rs As ADODB.Recordset
Set cnxn = CurrentProject. Connection
Set rs = New ADODB.Recordset

rs.Open "Select * FROM qselRosterEmail List", cnxn, adOpenForwardOn ly,
adLockOptimisti c, adCmdText

[...other stuff...]

End Sub
-----------------------------

When I use the follow line, which looks to a table instead of a query,
I have no problem:
rs.Open "Select * FROM tblRoster", cnxn, adOpenForwardOn ly,
adLockOptimisti c, adCmdText

Thanks again for you help,
David

[bdt513]

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 =
qselRosterEmail List or something like that)

Thanks