[AccessD] Parameter Queries via Code

Hi Mark

Those textboxes probably contain dates:

3.        qdfMetrics.Parameters(0) = "#" & Forms!frmDatePicker!Text14 &
"#"
4.        qdfMetrics.Parameters(1) = "#" & Forms!frmDatePicker!Text16 &
"#"

You may even need to format these to US date format:

3.        qdfMetrics.Parameters(0) = "#" &
Format(Forms!frmDatePicker!Text14, "m\/d\/yyyy") & "#"
4.        qdfMetrics.Parameters(1) = "#" &
Format(Forms!frmDatePicker!Text16, "m\/d\/yyyy") & "#"

/gustav


>>> dba.email at gmail.com 04/29 7:02 pm >>>
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?