Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 516
  • Last Modified:

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
0
ndedich
Asked:
ndedich
  • 6
  • 5
  • 4
  • +2
1 Solution
 
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
 
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 6
  • 5
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now