Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

openrecordset queryname -- too few parameters

Posted on 2011-03-25
2
Medium Priority
?
522 Views
Last Modified: 2012-06-22
Hi,
I'm using MS Access 2000.

I'm getting a too few parameters error on this openrecordset line:
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("Q_tags_aisle4", dbOpenDynaset)

Q_tags_aisle4 looks like this:
SELECT DISTINCT Q_tags_aisle1.BRAND, Q_tags_aisle1.Aisle
FROM Q_tags_aisle1;

Q_tags_aisle1 looks like this:
SELECT Left([tbl_line_setup].[RESRV],4) AS Aisle, Left([tbl_line_setup].[BCKUP],4) & " " & Right([tbl_line_setup].[BCKUP],5) AS BCKUP, tbl_line_setup.BRAND, tbl_line_setup.PROGRAM
FROM tbl_line_setup
WHERE (((Left([tbl_line_setup].[RESRV],4))<>"") AND ((tbl_line_setup.PROGRAM)=[Forms]![Main].[txtProgramPass]))
ORDER BY Left([tbl_line_setup].[RESRV],4);

I put a textbox on the form that I set equal to the Program value that the query uses in its criteria.
txtProgramPass.Value = sProgram

The sProgram variable is passing the value correctly and I see it display in the textbox when I step through.

The query runs fine if I just run it myself, but the openrecordset is not working.  If I hardcode the criteria into the query then the openrecordset does work, but I obviously can't leave it like that.

What can I try here?

Thanks,
Dave


0
Comment
Question by:coperations07
2 Comments
 
LVL 59

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 1600 total points
ID: 35215450
When you open a query in code, Access will not resolve reference for you, you must do it.  Since these are just form references, you can do it like this:

Dim db As Database
Dim qdef As QueryDef
Dim prm as Parameter
Dim rs As Recordset


Set db = CurrentDb()
Set qdef = db.QueryDefs("Q_tags_aisle4")
For Each prm in qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rs = qdef.OpenRecordset()  

JimD.
0
 

Author Closing Comment

by:coperations07
ID: 35215658
Thank you sir! One day I will know everything...I hope. :)
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

569 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