?
Solved

openrecordset queryname -- too few parameters

Posted on 2011-03-25
2
Medium Priority
?
518 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 58

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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

650 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