[AccessD] Parameter Queries via Code

Thank you Charlotte, that worked, but I'm still not getting past line
5...I'm now getting "Type mismatch (Error 13)".

1.        Set db = CurrentDb
2.        Set qdfMetrics = db.QueryDefs("qryMyQuery")
3.        qdfMetrics.Parameters(0) = Forms!frmDatePicker!Text14
4.        qdfMetrics.Parameters(1) = Forms!frmDatePicker!Text16
5.        Set rstMetrics = qdfMetrics.OpenRecordset(dbReadOnly)
6.        varResults = rstMetrics.GetRows(rstMetrics.RecordCount)
7.        Set objXLBook = GetObject(cXLPath & cXLFile)
8.        Set objXLApp = objXLBook.Parent
9.        Set objQuerySheet = objXLBook.Worksheets("ProductMetrics")
10.      objXLApp.Visible = True
11.      objXLBook.Windows(1).Visible = True
12.      Set objXLRange = objQuerySheet.Range("B1:G4")
13.      objXLRange.FormulaArray = varResults

Mark Mitsules



On 4/29/05, Charlotte Foust <cfoust at infostatsystems.com> wrote:
> Wen you handle a querydef like that in code, you have to pass the
> parameter values in the code.  You can't rely on a form, even if it is
> open.  So in your code, pass the value of Text14 and the value of Text16
> into the parameters like this:
> 
> qdfMetrics.Parameters(0) = Forms!frmDatePicker!Text14
> qdfMetrics.Parameters(1) = Forms!frmDatePicker!Text16
> 
> Then open your recordset.
> 
> Charlotte Foust
> 
> 
> -----Original Message-----
> From: Admin Sparky [mailto:dba.email at gmail.com]
> Sent: Friday, April 29, 2005 8:35 AM
> To: accessd at databaseadvisors.com
> Subject: [AccessD] Parameter Queries via Code
> 
> Group,
> 
> I have a stored parameter query where the parameters are taken from 2
> controls on a form...this part works great.  The query field is a date
> field and the form controls contain the "Start Date" and "End Date" with
> the parameter for that field listed as:
> 
> Between [Forms]![frmDatePicker]![Text14] And
> [Forms]![frmDatePicker]![Text16]
> 
> I have a report based on this query which I can run directly from this
> form...this part also works great.
> 
> Now the question.  From that same form I want to press a button which
> will take the results of that parameter query and put it into an Excel
> spreadsheet.  Can someone point me in the right direction to fill in the
> missing syntax at or around line 4?
> 
> 1.        Set db = CurrentDb
> 2.        Set qdfMetrics = db.QueryDefs("qryMyQuery")
> 3. ==> ?
> 4.        Set rstMetrics = qdfMetrics.OpenRecordset(dbReadOnly)
> 5. ==> ?
> 6.        varResults = rstMetrics.GetRows(rstMetrics.RecordCount)
> 7.        Set objXLBook = GetObject(cXLPath & cXLFile)
> 8.        Set objXLApp = objXLBook.Parent
> 9.        Set objQuerySheet = objXLBook.Worksheets("ProductMetrics")
> 10.        objXLApp.Visible = True
> 11.      objXLBook.Windows(1).Visible = True
> 12.      Set objXLRange = objQuerySheet.Range("B1:G4")
> 13.      objXLRange.FormulaArray = varResults
> 
> At the moment I am getting error 3061 on line 4: "Too few parameters.
> Expected 2."  Could this be a problem with scope?  The code above is in
> a separate module and is called by the Form code...is this a problem?
> 
> Thank you for any suggestions,
> 
> Mark Mitsules
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>