Solved

DAO Recordset query returns parameter error

Posted on 2009-07-10
4
298 Views
Last Modified: 2013-11-27
I have condensed the problem to the the tiny query shown in Code.  When I run the VBA code below on processing the Set rst statement I get a runtime error 3061 : Too few parameters. Expected 1. There is a temporary variable (CurLeadID)  that is setup in a macro that defines it using the settempvars command.  Ultimately it will be set in the code below as well and I have tried doing that directly and using various forms of "" and "' '" to no avail.

If I run the query from Access it runs fine.  If I use the VBA code :

DoCmd.OpenQuery ("Query1",acViewNormal,acReadOnly)

it will run fine as well. But when I try to obtain a recordset from it it fails.  If instead of using the [TempVars]!{CurLeadID] I use a number it works fine. I have used the debugger to make sure the variable is set up correctly before the Set rst statement and it looks fine.
Dim DB As DAO.Database
Dim rst DAO.Recordset
 
Set DB = CurrentDb
Set rst = CurrentDb.OpenRecordset("Query1")
 
Query1  SQL Statement is :
 
SELECT Leads.ID
FROM Leads
WHERE Leads.ID=[TempVars]![CurLeadID];

Open in new window

0
Comment
Question by:databasefun
  • 2
4 Comments
 
LVL 84
ID: 24827763
Perhaps this:

SELECT Leads.ID
FROM Leads
WHERE Leads.ID=Eval([TempVars]![CurLeadID]);
0
 

Author Comment

by:databasefun
ID: 24828395
Unfortunately it still yields the same error
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 24828487
create a function in a regular module

function getLeadID() as long
     getLeadID= Nz(TempVars![CurLeadID], 0)
end function

now change the sql of your query1

SELECT Leads.ID
FROM Leads
WHERE Leads.ID=getLeadID()

0
 

Author Closing Comment

by:databasefun
ID: 31602292
That did it!  Thanks - this was really getting frustrating.
What is the logic behind having to use this method?
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question