By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,792 Members | 1,603 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,792 IT Pros & Developers. It's quick & easy. 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
  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
  13.    MsgBox "[" & rstDAO![SumOfAMOUNT] & "]"
  15.    GetTotalAmt = rstDAO![SumOfAMOUNT]
  17.    'rstDAO.Close
  18.    'qryDef.Close
  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.

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

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

Aug 14 '08 #4

P: 26 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

Aug 14 '08 #7

P: 26
Hi Stewart,

Thanks for the response, here is the content of the query
Expand|Select|Wrap|Line Numbers
  2.        dbo_SALFLDGDSN.PERIOD, 
  3.        dbo_SALFLDGDSN.JRNAL_NO, 
  4.        Sum(dbo_SALFLDGDSN.AMOUNT) AS SumOfAMOUNT, 
  5.        dbo_SALFLDGDSN.D_C
  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]  ));
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")  ));
Aug 15 '08 #9

P: 26
Hi Stewart,

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

Aug 18 '08 #10

Post your reply

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