Error received "No value given for one or more required parameters

[JimmyM]

MS Excel/Access 2000 version
I am getting the error message when running a VBA macro inside of excel.
I borrowed the routine below from a diffrstRecordset.Open cmdcommanderent
discussion thread. It abends with the above error on the command line:

rstRecordset.Open cmdcommand.

The routine is taking a SSN (the field is defined numeric), and is in column
E and looking up in the table in column 1, and I want the value returned back
into column 16(P) in the spread sheet. But am stuck on the above command.


Sub Lookup()

Dim iLoopRow As Integer

Set cnnConn = New ADODB.Connection
With cnnConn
.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0"
.Open "C:\_MyAccess\ab_fedhist.mdb"
End With

Set cmdcommand = New ADODB.Command
Set cmdcommand.ActiveConnection = cnnConn

Set rstRecordset = New ADODB.Recordset
Set rstRecordset.ActiveConnection = cnnConn

With cmdcommand
.CommandType = adCmdText

'*** Get data for 100 rows
For iLoopRow = 1 To 100

.CommandText = "Select E from py_ab_fedhst where E = " &
Cells(iLoopRow, 1)

rstRecordset.Open cmdcommand

'*** Test to see if a lookup value was returned
If rstRecordset.EOF Then
Cells(iLoopRow, 16) = "Unknown"
Else
Cells(iLoopRow, 16) = rstRecordset("E")
End If '*** No record returned

rstRecordset.Close

Next iLoopRow
End With '*** cmdcommand


'*** Cleanup
Set cmdcommand = Nothing
Set rstRecordset = Nothing
cnnConn.Close
Set cnnConn = Nothing


End Sub

 

[JimmyM]

The 1st paragraph looks like I pasted something improperly. I have fixed the
commentary.