Solved

Opening a Query using OpenRecordset

Posted on 2003-11-12
19
492 Views
Last Modified: 2008-02-01
Hi There,

I am presently trying to open a query using the DAO OpenRecordset method which is generating a debug error saying "Too few parameters expected 2".

the code is provided:

Public Function Develop_missing_form_table()
    Dim datab As DAO.Database
    Dim rsit As DAO.Recordset
   
    Dim cntr As Long
    Dim cntr2 As Long
    Dim var_str1 As String
    Dim res_str1 As String
       
    Set datab = CurrentDb ' set to the current database
    res_str1 = ""
               
    For cntr = 1 To 31
        var_str1 = "qry_SUPERVISOR_day" & cntr
        Set rsit = datab.OpenRecordset(var_str1, dbOpenForwardOnly)
        res_str1 = rsit.Fields("Field2").Value
        If IsNull(res_str1) = True Then
            Debug.Print (res_str1)
        End If
        rsit.Close  'free up the memory
        Next cntr
       
       
    datab.Close 'free up the memory
   
End Function


Does anybody know how to fix this, I have referenced queries using this method before yet suddenly now it doesn't work.  Is there another way to do this?

Regards,

-n
0
Comment
Question by:ndedich
  • 6
  • 5
  • 4
  • +2
19 Comments
 
LVL 19

Accepted Solution

by:
Dexstar earned 500 total points
ID: 9736338
ndedich:

> I am presently trying to open a query using the DAO OpenRecordset method which
> is generating a debug error saying "Too few parameters expected 2".

Here is a similar problem, and a solution:
http://www.experts-exchange.com/Databases/MS_Access/Q_20734201.html

Hope that helps,
Dex*
0
 
LVL 2

Expert Comment

by:incongruent
ID: 9736426
If   the name of your queries are qry_SUPERVISOR_day1...qry_SUPERVISOR_day2....qry_SUPERVISOR_day2... etc... then try the following...  

var_str1 = "[qry_SUPERVISOR_day" & cntr &"]"
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 9736472
The problem looks like is criteria you have in:

qry_SUPERVISOR_day" & cntr

In design view of one of the queries, from menu, select View/SQL and paste its content here.

Mike
0
 

Author Comment

by:ndedich
ID: 9736494
Hi incongruent,

I made the change as you suggested, it now is saying
Run-time error 3078
Cannot find the 'input table or query [qry_supervisor_day1]

Regards,
-n
0
 

Author Comment

by:ndedich
ID: 9736527
ok pasting the qry_SUPERVISOR_day1 contents, it is a query which references another query

SELECT [qry Dbase checking day 1].Field2, [qry Dbase checking day 1].Month, [qry Dbase checking day 1].Year
FROM [qry Dbase checking day 1]
WHERE ((([qry Dbase checking day 1].Month)=[Forms]![Gemini User ID lookup]![Combo27]) AND (([qry Dbase checking day 1].Year)=[Forms]![Gemini User ID lookup]![Combo29]));

Regards,
-n
0
 

Author Comment

by:ndedich
ID: 9736601
I think the problem is arising because the query is calling another query.  When I pasted the referenced query in it seemed to work.  I am sure something is wrong because the queries work by themselves in the query tab of the database window but the OpenRecordset method can't seem to handle the query calling another subquery or parameter queries.  If you guys have this maybe you could contact Microsoft and let them know to write a soln.  Maybe you could test this?

Regards,
-n
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 9736644
Your SQL less extra things is:

SELECT Field2, Month, Year FROM [qry Dbase checking day 1] WHERE Month=[Forms]![Gemini User ID lookup]![Combo27] AND Year=[Forms]![Gemini User ID lookup]![Combo29]

Q1: Is this query returning correct values, I should have asked in my last post?
Q2: Is there some other criteria in [qry Dbase checking day 1]?  Ignore Q2 if Q1 is yes.

If the answer to Q1 is NO, to trouble shoot above SQL, start a new query, add a table (any table), select View/SQL and paste following SQL to see whether it is able to return criteria values from your form:

SELECT Field2, Month, Year, [Forms]![Gemini User ID lookup]![Combo27] As Combo27Value, [Forms]![Gemini User ID lookup]![Combo29] Combo29Value FROM [qry Dbase checking day 1]

This query will return all records with the same criteria values specified in the combo box for all records.  If it does, we need to look elsewhere for the problem.

Mike
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 9736667
Another good test is VarType using:

SELECT Field2, Month, VarType(Month) As MonthVarType, Year, VarType(Year) As YearVarTypeVarType([Forms]![Gemini User ID lookup]![Combo27]) As Combo27ValueVarType, VarType([Forms]![Gemini User ID lookup]![Combo29]) Combo29ValueVarType FROM [qry Dbase checking day 1]

in this test, MonthVarType should match Combo27ValueVarType for all records and YearVarTypeVarType should match Combo29ValueVarType for all records.

Mike
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 9736741
Using user-defined function this should work.  Try:

SELECT Field2, Month, Year FROM [qry Dbase checking day 1] WHERE Month=iif(fnMonth() ="",[Month], fnMonth()) And Month=iif(fnYear() ="",[Year], fnYear())
-------------
In a standard module, under module tab, paste:

Public Function fnMonth() As String
    fnMonth=Nz([Forms]![Gemini User ID lookup]![Combo27],"")
End Function

Public Function fnYear() As String
    fnYear=Nz([Forms]![Gemini User ID lookup]![Combo29],"")
End Function

Another advantage with above criteria setting is if you leave say Combo29 blank, it will return all (will ignor year criteria not produce error because you left it blank).

Mike
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 9736749
oops... correction

SELECT Field2, Month, Year FROM [qry Dbase checking day 1] WHERE Month=iif(fnMonth() ="",[Month], fnMonth()) And Year=iif(fnYear() ="",[Year], fnYear())
0
 

Author Comment

by:ndedich
ID: 9737098
Ok guys will try very slowly and let you know my results.
0
 
LVL 2

Expert Comment

by:incongruent
ID: 9742309
instead  of :  
    Set rsit = datab.OpenRecordset(var_str1, dbOpenForwardOnly)

Try:
    Set rsit = datab.OpenRecordset(var_str1, dbOpenDynaset)
or:
    Set rsit = datab.OpenRecordset(var_str1, dbOpenSnapshot)



Plus, shouldn't there be a rsit.moveFirst()?
0
 

Author Comment

by:ndedich
ID: 9835056
I think the solution is what I used was to write to some identical tables.  I use 1 table for each day of the month.  I just opened the table instead of the Query and it worked, I'll probably keep this question open because it's a limitation of Access 97 it seems.
0
 
LVL 2

Expert Comment

by:incongruent
ID: 9838976
I wouldn't say its a limitation.

You have  to think of Access as two separate functionalities in one.  It is a database, and it is a software tool.  The two are completely seperate funtions and are isolated from each other.    Trying to access a software query from Access would be like trying to access your java SQL resultset from access.  They just dont' communicate with each other.  Thats one of the benefits of using a database in the first place.  Its not application specific.

my two cents.

-Allan
0
 
LVL 2

Expert Comment

by:incongruent
ID: 9839007
uhh... wish i could delete that...     I thought i was responding to a different question... nevermind that comment.
0
 

Author Comment

by:ndedich
ID: 9925307
Hi Guys this answers my question:

The query you are using to produce the recordset is a parameter query and access is failing to see the argument. Try supplying the query by an SQL statement and concatenate the string to supply the argument!

Ex:

Error
“SELECT *FROM tblMyTableName WHERE fldMyName. = frmMyFormsName!fldMyFieldName;”

Non error
“SELECT *FROM tblMyTableName WHERE fldMyName ' = “ &frmMyFormsName!fldMyFieldName & “';”
'if the value is string
Private Sub changeprices()
Dim rstprices As DAO.Recordset,
Dim db As Database
Dim strSQL as String

Set db = CurrentDb
strSQL = ““SELECT *FROM tblMyTableName WHERE fldMyName. = “ & frmMyFormsName!fldMyFieldName & “;”

Set rstprices = db.OpenRecordset(strSQL)

End Sub

Note:
The form has to be open for this code to work!

HTH

Bill
0
 
LVL 39

Expert Comment

by:stevbe
ID: 10025458
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

Accept: Dexstar {http:#9736338}

Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

stevbe
EE Cleanup Volunteer
0
 
LVL 39

Expert Comment

by:stevbe
ID: 10025465
although the wording is exact, this solution was provided in the link the Dexstart posted.

Steve
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now