what causes an error that says: "Too few Parameters. Expected 1."

[Paul]

I'm trying to use the following code to test whether there are any records
in a recordset, but when VBA tries to run the line containing the SELECT
statement, it displays an error that says

Too few parameters. Expected 1.

Here's the code I'm using:

Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT tblProject.ProjectID,
tblContacts.osUserName FROM tblProject INNER JOIN (tblContacts INNER JOIN
tblProjectContact ON tblContacts.ContactID = tblProjectContact.ContactID) ON
tblProject.ProjectID = tblProjectContact.ProjectID WHERE
(((tblProject.ProjectID)=[forms]![frmProjects]![txtProjectID]) AND
((tblContacts.osUserName)=fosUserName()));", dbOpenDynaset)
If rs.RecordCount > 0 Then
etc.

One thing that puzzles me about this is that the SELECT statement does
produce the desired records in a query. Why am I getting this message when
I try to use it in VBA?

Thanks in advance,

Paul

 

[Allen Browne]

The parameter is the reference:
[forms]![frmProjects]![txtProjectID]

In a query, JET passes the reference to the Expression Service, which
evaluates the expression. DAO cannot do that.

So, concatenate the value into the string:
Dim strSql As String
strSql = "SELECT ... FROM ... WHERE (tblProject.ProjectID = " & _
Forms!frmProjects!txtProjectID & ") AND ... "
Set rs = db.OpenRecordset(strSql)

Note that if ProjectID is a Text field in tblProject, you need extra quotes
as explained here:
http://allenbrowne.com/casu-17.html
If it is a number field, you need to test if the text box is null. Otherwise
the string will be mal-formed.

 

[Bob Barrows]

The parameter is the reference:
[forms]![frmProjects]![txtProjectID]

In a query, JET passes the reference to the Expression Service, which
evaluates the expression ...

Actually, I think it's Access that does that before passing the query along
to Jet.

 

[Paul]

Ah! That's what I needed.

Thanks for showing me how to solve that problem, Allen.

Paul