Recordset - query with user-entered criteria

[kraymond]

I'm trying to automate Excel charts from an Access query. When I get to
defining my recordset in Access, it returns an error saying I have fewer than
expected parameters. After having looked at replies in this forum, I
realized this is because my query that I am trying to use as the recordset
has a [Enter Station Name] criteria for the [Station Name] field.

I must use this criteria, or something with a similar function, because I am
trying to graph all water levels for all devices over time at a particular
station.

Does anyone have a suggestion for a way to let the user define this criteria?

Here is the problem part of my code:
Set db = CurrentDb()
Set rst = db.OpenRecordset("qrysitedevicewaterlevel", dbOpenSnapshot)


Thanks.



The rest of the code to this point is:
Sub CreateXLChart()
'Excel object variables
Dim objApp As Excel.Application
Dim objBook As Excel.Workbook
Dim objSheet As Excel.Worksheet
Dim objChart As Excel.Chart
'Access/Jet object variables
Dim db As Database
Dim rst As Recordset
'Normal variables
Dim cSheet As Integer
Dim lngRows As Long
'Create XL Application object
Set objApp = New Excel.Application
'Make it visible so we can watch it work
objApp.Visible = True
'Create a new workbook
Set objBook = objApp.Workbooks.Add
'Get rid of all but one worksheet
objApp.DisplayAlerts = False
For cSheet = objBook.Worksheets.Count To 2 Step -1
objBook.Worksheets(cSheet).Delete
Next
objApp.DisplayAlerts = True
'Capture reference to first worksheet
Set objSheet = objBook.Worksheets(1)
'Change the worksheet name
objSheet.Name = "Water level Over time"

 

[Guest]

hi,
you can play a trick on it.
make the query a make table query and dump the contents of
the query in the temp table. the use the temp table as the
recordset record source
Set rst = db.OpenRecordset("temptable", dbOpenSnapshot)
you can add code to delete the table just prior to
creating again each time you run this routine. or after
the xl routine to save disk space.