DAO Recordset query returns parameter error

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

databasefunAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Perhaps this:

SELECT Leads.ID
FROM Leads
WHERE Leads.ID=Eval([TempVars]![CurLeadID]);
0
databasefunAuthor Commented:
Unfortunately it still yields the same error
0
Rey Obrero (Capricorn1)Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
databasefunAuthor Commented:
That did it!  Thanks - this was really getting frustrating.
What is the logic behind having to use this method?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.