Hi Tod,
You create the Command object and all the required Parameter objects
before you go into the loop. Then you just modify the value of the
parameter(s) inside the loop like so:
cm.ActiveConnection = cn
cm.CommandText = "qryAccessQuery"
cm.CommandType = adCmdStoredProc
Set pm1 = cm.CreateParameter("NameField", adVarChar, adParamInput, 5)
cm.Parameters.Append pm1
For MyNames = 0 To UBound(SampleArray)
pm1.Value = SampleArray(MyNames)
rs.Open cm
'.......code to use recordset
rs.Close
Next MyNames
--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/
* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *
"Tod" wrote in message
...
Is it possible to use ADO Parameter objects in a loop? If
so, what am I doing wrong.
Here's some sample code:
SampleArray("Brian","David","Mark")
For MyNames = 0 To UBound(SampleArray)
With cm
.ActiveConnection = cn
.CommandText = "qryAccessQuery"
.CommandType = adCmdStoredProc
Set pm1 = cm.CreateParameter("NameField",
adVarChar, adParamInput, 5)
.Parameters.Append pm1
pm1.Value = SampleArray(MyNames)
End With
rs.Open cm
'.......code to use recordset
rs.close
next MyNames
I can get through the loop once okay. But I need to
somehow clear the parameter so it can accept a new value.
All I've managed to do is continue appending parameters.
How's this done?
tod