Solved

Opening a Query using OpenRecordset

Posted on 2003-11-12
19
498 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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
 
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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…

832 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