Query Recordset Too Few Parameters

[dftjsn]

I'm trying to open an ADO Recordset with the following:

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
Dim ProjectORGID As String
Dim ProjectISNUMBER As Long
ProjectORGID = "ZZZZDEMO"
ProjectISNUMBER = 1
rst.Open "qrySelectedProjectCitations", CurrentProject.Connection, adOpenStatic, adLockReadOnly, adCmdTable

The SQL from qrySelectedProjectCitations is:

PARAMETERS ProjectISNUMBER Long, ProjectORGID Text ( 255 );
SELECT tblCitationProjectAssignment.LocProj_IS_NUMBER, tblCitationProjectAssignment.LocProj_ORG_ID, tblCitationProjectAssignment.LocCITN_IS_NUMBER, tblCitationProjectAssignment.LocCITN_ORG_ID, tblCitationProjectAssignment.TSMPROJ_IS_NUMBER, tblCitationProjectAssignment.TSMPROJ_ORG_ID, tblCitationProjectAssignment.TSRCITN_IS_NUMBER, tblCitationProjectAssignment.TSRCITN_ORG_ID, tblCitationProjectAssignment.D_USERID_CODE, tblCitationProjectAssignment.D_LAST_UPDATE_TS
FROM tblCitationProjectAssignment
WHERE (((tblCitationProjectAssignment.LocProj_IS_NUMBER)=[ProjectISNUMBER]) AND ((tblCitationProjectAssignment.LocProj_ORG_ID)=[ProjectORGID]));

I get an error message -2147217904 about having too few parameters. I'm passing in ProjectISNUMBER and ProjectORGID, but it doesn't seem to take them.

Can anyone shed any light on this?

Thanks!

dftjsn

 

[rac2]

I think that you should not put quote marks around

qrySelectedProjectCitations.

If that is a string variable that contains the SQL then it should not be enclosed in quotes.

 

[dftjsn]

Rac2,

Thanks for your response. qrySelectedProjectCitations is actually the name of the query. It is not a string variable so it should be enclosed in quotes. The error message I get is -2147217904 "No value given for one or more required parameters". Evidently the values for ProjectISNUMBER and ProjectORGID aren't getting passed into the query. Does anyone have any ideas?

Thanks!

dftjsn

 

[dftjsn]

The solution was to use an ADO command object and create parameters like this:

Dim p1 As ADODB.Parameter
Dim p2 As ADODB.Parameter
Dim qryCmd As ADODB.Command
Dim rst As ADODB.Recordset
Set qryCmd = New ADODB.Command
With qryCmd
.ActiveConnection = CurrentProject.Connection
.CommandText = "qrySelectedProjectCitations"
.CommandType = adCmdTable
Set p1 = .CreateParameter("ProjectISNUMBER", adInteger, adParamInput)
.Parameters.Append p1
p1.Value = 1
Set p2 = .CreateParameter("ProjectORGID", adVarChar, adParamInput, 8)
.Parameters.Append p2
p2.Value = "ZZZZDEMO"
.Execute
End With
Set rst = New ADODB.Recordset
rst.Open qryCmd, , adOpenStatic, adLockReadOnly
rst.Close
Set rst = Nothing

 

[rac2]

My mistake. I did not understand that "qrySelectedProjectCitations" could be the name of something. Perhaps similar to the name of a VIEW or TABLE.

Thanks for posting your solution.