Opening a Query using OpenRecordset

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
ndedichAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
incongruentCommented:
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
Mike EghtebasDatabase and Application DeveloperCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ndedichAuthor Commented:
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
ndedichAuthor Commented:
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
ndedichAuthor Commented:
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
Mike EghtebasDatabase and Application DeveloperCommented:
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
Mike EghtebasDatabase and Application DeveloperCommented:
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
Mike EghtebasDatabase and Application DeveloperCommented:
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
Mike EghtebasDatabase and Application DeveloperCommented:
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
ndedichAuthor Commented:
Ok guys will try very slowly and let you know my results.
0
incongruentCommented:
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
ndedichAuthor Commented:
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
incongruentCommented:
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
incongruentCommented:
uhh... wish i could delete that...     I thought i was responding to a different question... nevermind that comment.
0
ndedichAuthor Commented:
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
stevbeCommented:
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
stevbeCommented:
although the wording is exact, this solution was provided in the link the Dexstart posted.

Steve
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.