[AccessD] Parameter Queries via Code

Admin Sparky dba.email at gmail.com
Fri Apr 29 12:07:01 CDT 2005


Thanks for the suggestion Dan.  In this case however, it is an
existing spreadsheet with existing charts that have been developed. 
I'm just trying to automate the updating of the chart's data range
with the latest data from the query on demand.


Mark Mitsules


On 4/29/05, Dan Waters <dwaters at usinternet.com> wrote:
> 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
> 
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>



More information about the AccessD mailing list