I am having trouble with a datacombo and ADO code command object:
I can successfully populate this datacombo using a dataenvironment and a parameterized recordset. However, I want to do the same thing using ADO code. I can populate a datacombo using ADO code if I don’t use a command object and parameters, but I can’t do it when I use the command object at run-time. I can’t figure out why I can’t do it at run-time!!
Here’s the code,
Dim cn as adodb.connection
Dim rs2 as adodb.recordset
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
Set rs2 = New ADODB.Recordset
‘The boundtext of this datacombo is the EmployeeID
Zid = dtcEmpName.BoundText
‘Connection establishes a connection to SQL Server
Connection
cmd.ActiveConnection = cn
‘***This SQL statement executes fine if I use the dataenvironment and pass a parameter
cmd.CommandText = “SELECT tblEmployee.EmpName, tblJobCodeDsc.JobTitle, tblSupervisory.CostCtr FROM tblJobCodeDsc INNER JOIN tblSupervisory INNER JOIN tblEmployee ON tblEmployee.EmpID = tblSupervisory.AppraiseeID ON tblSupervisory.JobCode = tblJobCodeDsc.JobCode WHERE tblSupervisory.AppraiseeID = ? AND (tblSupervisory.AppraiserID = (SELECT tblEmployee.EmpID From tblEmployee WHERE NTUserName = SYSTEM_USER) OR tblSupervisory.ReviewerID = (SELECT tblEmployee.EmpID From tblEmployee WHERE NTUserName = SYSTEM_USER))”
cmd.CommandType = adCmdText
Set rs2 = cmd.Execute(Parameters:=Zid)
‘***I get the desired results
Do Until rs2.EOF
Debug.Print rs2(“JobTitle”)
rs2.MoveNext
Loop
‘***However, if I set the rowsource of the datacombo = rs2, I don’t get any values in the datacombo
Set dtcJobTitle.RowSource = rs2
dtcJobTitle.ListField = “JobTitle”
dtcJobTitle.BoundColumn = “CostCtr”
‘***I have successfully populated a datacombo using ADO code with a non-parameterized recordset – in other words, not using a command object in ADO code.