By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,500 Members | 830 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,500 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
-----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-----
tina wrote:
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


Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.