Solved

openrecordset queryname -- too few parameters

Posted on 2011-03-25
2
499 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 400 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

749 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