T
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
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