[AccessD] Parameter Queries via Code

Mark,

I use this function to create the spreadsheet.

    DoCmd.TransferSpreadsheet acExport, , "qryMyQuery", _
        stgFolderName & stgSpreadsheetName


I use this code to create a folder on the user's PC to put the spreadsheet
into:

    Set fso = CreateObject("Scripting.FileSystemObject")
    stgFolderName = "C:\" & SystemAcronym & "\"
    If fso.FolderExists(stgFolderName) = False Then
        fso.CreateFolder (stgFolderName)
    End If
    stgFolderName = "C:\" & SystemAcronym & "\Spreadsheets\"
    If fso.FolderExists(stgFolderName) = False Then
        fso.CreateFolder (stgFolderName)
    End If
    Set fso = Nothing


Hope this helps!

Dan Waters


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Admin Sparky
Sent: Friday, April 29, 2005 10: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