By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,483 Members | 1,464 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,483 IT Pros & Developers. It's quick & easy.

help...how can I pass parameter to Access Query using VB

P: 26
When I call this function Access gives me error msg "Too few parameters. Expected 3. [3061]" on the Set rstDAO = qryDef.OpenRecordset. Then I changed to
Set rstDAO = qryDef.OpenRecordset(dbOpenSnapshot, dbOpenSnapshot, dbOpenForwardOnly)

Now it gives me error "Invalid Arguments [3001]"...pls help...

here is my code:
Expand|Select|Wrap|Line Numbers
  1. Public Function GetTotalAmt(strAcct_Code As String) As Long
  2.    Dim currDB As DAO.Database
  3.    Dim rstDAO As DAO.Recordset
  4.    Dim qryDef As DAO.QueryDef
  5.    Dim strQry As String
  6.  
  7.    Set currDB = CurrentDb()
  8.    strQry = "Sum_Amt_CashierDirect"
  9.    Set qryDef = currDB.QueryDefs("Sum_Amt_CashierDirect")
  10.    qryDef.Parameters("acct_code") = "9ARTNC"
  11.    Set rstDAO = qryDef.OpenRecordset(dbOpenSnapshot, dbOpenSnapshot) '  ERROR MESSAGE EXPECTED 3 PARAMETERS
  12.  
  13.    MsgBox "[" & rstDAO![SumOfAMOUNT] & "]"
  14.  
  15.    GetTotalAmt = rstDAO![SumOfAMOUNT]
  16.  
  17.    'rstDAO.Close
  18.    'qryDef.Close
  19.  
  20. End Function
Aug 13 '08 #1
Share this Question
Share on Google+
9 Replies


Expert Mod 2.5K+
P: 2,545
Hi. Please don't duplicate part of your post (see thread linked here) - it wastes the time of the expert volunteers who staff this site.

You are using a querydef object to allow parameter passing. In more normal circumstances OpenRecordset applies to the database object, not a querydef object. You can use the CurrentDb qualifier which is intrinsic in Access to refer to the currently-active database object. With it, the recordset open should become

Set rstDAO = Currentdb.OpenRecordset(strQry)

but if you need the parameter passing then retry your querydef based solution with OpenRecordset(strQry), as you have not supplied the correct arguments to OpenRecordset in the example you quote.

-Stewart
Aug 13 '08 #2

P: 26
thanks Stewart....sorry for double posting because I'm too desperate and nobody can help me....
Aug 13 '08 #3

P: 26
hi,

if I use:

Set rstDAO = Currentdb.OpenRecordset(strQry)

what I know, I need to use:

Set qryDef = currDB.QueryDefs("Sum_Amt_CashierDirect")

to pass parameter (acct_code-> parameter of my query and supplied them with "9ARTNC" value )

qryDef.Parameters("acct_code") = "9ARTNC"

how can I pass the parameter from my access query? if I do this?

Set rstDAO = Currentdb.OpenRecordset(strQry)

pls correct me if I'm wrong....pls help...

thanks
Aug 14 '08 #4

P: 26
...help please...

can anybody give me the syntax or example how can I implement the OpenRecordSet() method using MS Access query?

i'm still having "Too few parameters. Expected 3 [3061]"
Aug 14 '08 #5

P: 26
still waiting who can give solution to my problem
Aug 14 '08 #6

Expert Mod 2.5K+
P: 2,545
Hi. As we have all already said, the error message does NOT relate to the syntax of the OpenRecordset method; it results from a failure to open the recordset, possibly related to references to form control values in the SQL of the underlying query.

Please post the SQL for the query you are trying to use OpenRecordset upon.

Thank you

-Stewart
Aug 14 '08 #7

P: 26
Hi Stewart,

Thanks for the response, here is the content of the query
Expand|Select|Wrap|Line Numbers
  1. SELECT dbo_SALFLDGDSN.ACCNT_CODE, 
  2.        dbo_SALFLDGDSN.PERIOD, 
  3.        dbo_SALFLDGDSN.JRNAL_NO, 
  4.        Sum(dbo_SALFLDGDSN.AMOUNT) AS SumOfAMOUNT, 
  5.        dbo_SALFLDGDSN.D_C
  6.   FROM dbo_qrySSRFADDDSN INNER JOIN dbo_SALFLDGDSN ON dbo_qrySSRFADDDSN.ADD_CODE = dbo_SALFLDGDSN.ACCNT_CODE
  7. GROUP BY dbo_SALFLDGDSN.ACCNT_CODE, 
  8.       dbo_SALFLDGDSN.PERIOD, 
  9.       dbo_SALFLDGDSN.JRNAL_NO, 
  10.       dbo_SALFLDGDSN.D_C
  11. HAVING ((  (dbo_SALFLDGDSN.PERIOD)  = Format(SunToSqlServerPeriod([Forms]![frmPayInvFileGen]![txtRepPeriod]),"General Number")) 
  12. AND ((dbo_SALFLDGDSN.JRNAL_NO)=[Forms]![frmPayInvFileGen]![txtJrnlNo]  ));
where:
SunToSqlServerPeriod() returns => "2008005"
[Forms]![frmPayInvFileGen]![txtJrnlNo] => 6735
Aug 15 '08 #8

Expert Mod 2.5K+
P: 2,545
Hi. There are two form field references in the HAVING clause (the equivalent of a WHERE clause in a grouped query). Access is inconsistent in the way it treats form field references in such clauses, and there are known issues with their use (including the parameter error message arising with OpenRecordset). It is these problems that Adezii was referring to in his response.

When I use such form field references I do so through the use of a simple global function in VBA to fetch the value of the form field from the relevant form. Access has no problems with including function calls in SQL statements.

Open any public code module (any module shown in the Modules tab of the database), or create a new one if you don't have any. You can give it any suitable name (the default is Module 1).

Paste the following code in to the module and save the changes:

Expand|Select|Wrap|Line Numbers
  1. Public Function FormFieldValue(FormName As String, FieldName As String)
  2.     FormFieldValue = Forms(FormName).Controls(FieldName)
  3. End Function
Then try the revised HAVING part of the SQL for your query below, where the form field references are retrieved using the new function in place of direct references:

Expand|Select|Wrap|Line Numbers
  1. HAVING ((  (dbo_SALFLDGDSN.PERIOD)  = Format(SunToSqlServerPeriod(FormfieldValue("frmPayInvFileGen", "txtRepPeriod")),"General Number")) 
  2. AND ((dbo_SALFLDGDSN.JRNAL_NO)=FormFieldValue("frmPayInvFileGen", "txtJrnlNo")  ));
-Stewart
Aug 15 '08 #9

P: 26
Hi Stewart,

Thanks for your response....I'll try your solution to my problem...Thanks again..

EVH316
Aug 18 '08 #10

Post your reply

Sign in to post your reply or Sign up for a free account.