Command text was not set for the command object error And DoCmd.OpenQuery

[tina]

Hello,
I would really appreciate some help. I am still learning VBA and I
feel like my brain is fried already.
Below I am trying to run a query and do a loop.
I actually get the data set but then I get this error when I am trying
to execute "Command text was not set for the command object."
What is wrong? How can I make this work?

Thank you very much.

Function GetFullReportCCListServer(MyUnit_Name As String)
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmdSQL As ADODB.Command

On Error GoTo HandleErr

Set cnn = New ADODB.Connection
cnn.ConnectionString = CurrentProject.Connection
cnn.Mode = adModeShareDenyNone
cnn.Open

Set rs = New ADODB.Recordset

Set cmdSQL = New ADODB.Command
Set cmdSQL.ActiveConnection = cnn


DoCmd.OpenQuery "qryGetFullReportCCList"

rs.CursorType = adOpenStatic

Set rs = cmdSQL.Execute()

rs.MoveFirst

Do Until rs.EOF

GetFullReportCCListServer = GetFullReportCCListServer &
rs.Fields("LoginID") & ";"
rs.MoveNext

Loop

GetFullReportCCListServer = Left$(GetFullReportCCListServer,
Len(GetFullReportCCListServer) - 1)

ExitHere:

Set rs = Nothing
Set cnn = Nothing

Exit Function

Select Case Err.Number
Case 3021
MsgBox "No Distribution List was found for this Program
Unit"
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description,
vbCritical, "modLookUps.GetFullReportCCListServer"
'ErrorHandler:$$N=modLookUps.GetCurrentAuditor
End Select
GoTo ExitHere
End Function

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You're setting up the recordset incorrectly. Try this:

Set cnn = New ADODB.Connection
cnn.ConnectionString = CurrentProject.Connection
cnn.Mode = adModeShareDenyNone
cnn.Open

Set rs = cnn.Execute("qryGetFullReportCClist",,acCmdTable)

Queries are considered as tables.

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQBGEOIechKqOuFEgEQJQqQCgt3UoRIz/Hogd4DPgrXz8z/mf/hMAn3ws
xooACMZlprMvJ2MCjh4r0dtd
=gbTc
-----END PGP SIGNATURE-----