473,233 Members | 1,471 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,233 software developers and data experts.

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

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
1 11827
-----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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: jj | last post by:
I've got a form button that fires off 3 queries but if the first query returns an error, I don't want the other two queries to happen. Example: first query runs an insert from a linked table but...
1
by: gbb0330 | last post by:
hi all here is my situation, any help will be appreciated i have a form with two command buttons on it, listing update and append arc Private Sub listing_update_Click() On Error GoTo...
2
by: gbb0330 | last post by:
hi all first i would like to thank PC Datasheet, i got so far because of him/her i need some help again i have this command button the on-click event procedure is
1
by: xtra | last post by:
Hi Folk I have written a module that allows you to type a bunch of commands in the immediate window, for quick access to information when you are creating VB code. Here it is, it may be helpful...
4
by: jstaggs39 | last post by:
I have a form that requires a start date and an end date as input for the parameters then runs the form which open queries which are designed to populate certain tables. As it stands now, i can...
1
by: Gilz | last post by:
Hi I was wondering if anyone could help i have picked this database up from a designer who has now left the company. The code is a couple of years old. On click of a button it open a...
2
by: HydroPnik | last post by:
Hello all. Access 2003/Windows XP. I have set up a command button where three queries are run. The first and last queries are select and the second is an update. I only want to display the...
3
by: Kosmos | last post by:
Hey ya'll...I can't seem to figure out why I'm getting this error message, but it all started when I added the new line of code with the recSet5.AddNew --- when I ran the first line, the logic worked...
8
by: pelicanstuff | last post by:
Hi - Was wondering if anybody could tell me why this rather crappy code is giving me an 'Else without If' error on compile? All the Elses and Ifs look ok to me but there's a few. Private Sub...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.