[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 308
  • Last Modified:

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

0
databasefun
Asked:
databasefun
  • 2
1 Solution
 
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
 
databasefunAuthor Commented:
That did it!  Thanks - this was really getting frustrating.
What is the logic behind having to use this method?
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now