[AccessD] Parameter Queries via Code

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