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

[EVH316]

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:

Public Function GetTotalAmt(strAcct_Code As String) As Long
   Dim currDB As DAO.Database
   Dim rstDAO As DAO.Recordset
   Dim qryDef As DAO.QueryDef
   Dim strQry As String
 
   Set currDB = CurrentDb()
   strQry = "Sum_Amt_CashierDirect"
   Set qryDef = currDB.QueryDefs("Sum_Amt_CashierDirect")
   qryDef.Parameters("acct_code") = "9ARTNC"
   Set rstDAO = qryDef.OpenRecordset(dbOpenSnapshot, dbOpenSnapshot) '  ERROR MESSAGE EXPECTED 3 PARAMETERS
 
   MsgBox "[" & rstDAO![SumOfAMOUNT] & "]"
 
   GetTotalAmt = rstDAO![SumOfAMOUNT]
 
   'rstDAO.Close
   'qryDef.Close
 
End Function

[Stewart Ross]

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

[EVH316]

thanks Stewart....sorry for double posting because I'm too desperate and nobody can help me....

[EVH316]

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

[EVH316]

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

[EVH316]

still waiting who can give solution to my problem

[Stewart Ross]

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

[EVH316]

Hi Stewart,

Thanks for the response, here is the content of the query

SELECT dbo_SALFLDGDSN.ACCNT_CODE, 
       dbo_SALFLDGDSN.PERIOD, 
       dbo_SALFLDGDSN.JRNAL_NO, 
       Sum(dbo_SALFLDGDSN.AMOUNT) AS SumOfAMOUNT, 
       dbo_SALFLDGDSN.D_C
  FROM dbo_qrySSRFADDDSN INNER JOIN dbo_SALFLDGDSN ON dbo_qrySSRFADDDSN.ADD_CODE = dbo_SALFLDGDSN.ACCNT_CODE
GROUP BY dbo_SALFLDGDSN.ACCNT_CODE, 
      dbo_SALFLDGDSN.PERIOD, 
      dbo_SALFLDGDSN.JRNAL_NO, 
      dbo_SALFLDGDSN.D_C
HAVING ((  (dbo_SALFLDGDSN.PERIOD)  = Format(SunToSqlServerPeriod([Forms]![frmPayInvFileGen]![txtRepPeriod]),"General Number")) 
AND ((dbo_SALFLDGDSN.JRNAL_NO)=[Forms]![frmPayInvFileGen]![txtJrnlNo]  ));

where:
SunToSqlServerPeriod() returns => "2008005"
[Forms]![frmPayInvFileGen]![txtJrnlNo] => 6735

[Stewart Ross]

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:

Public Function FormFieldValue(FormName As String, FieldName As String)
    FormFieldValue = Forms(FormName).Controls(FieldName)
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:

HAVING ((  (dbo_SALFLDGDSN.PERIOD)  = Format(SunToSqlServerPeriod(FormfieldValue("frmPayInvFileGen", "txtRepPeriod")),"General Number")) 
AND ((dbo_SALFLDGDSN.JRNAL_NO)=FormFieldValue("frmPayInvFileGen", "txtJrnlNo")  ));

-Stewart

[EVH316]

Hi Stewart,

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

EVH316