Enter Parameters for Qry Recordset

[maxsun08]

Hi All,

I have a query with a "Between [Sdate] and [Edate] a the parameter for a DateOpened field. I'm able to export the query recordset to Excel using a button On Click, however I can't get the parameter prompt to come up to enter the start and end dates.

This gives me "Too few parameters, expected 2." Error.

Set rst = CurrentDb.OpenRecordset("select * from qryIssueswithDateParameters", dbOpenSnapshot)

I then tried this, but get a RT error 2482 "cannot find the name 'Enter Start Date" you entered in the expression.

Set db = CurrentDb
Set qrydef = db.QueryDefs("qryIssueswithDateParameters")
For Each prm In qrydef.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rst = qrydef.OpenRecordset(dbOpenDynaset)

Ideally, when the action button is clicked I would like the two parameter prompts to pop up to enter the Start Date and End Date so that records between those dates are exported to Excel.

Thank you all in advance!

 

[CJ_London]

try using docmd.openquery "qryIssueswithDateParameters", acViewNormal, acReadOnly

Or to export to Excel use

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Query1", "C:\temp\query1.xls", SysHasFieldNames

Note the spreadsheet type is different for .xls and .xlsx

Both of these will cause the prompts to fire

 

[maxsun08]

CJ, thank you for the response. Unfortunately, I'm exporting to a new blank xlworkbook so the transferspreadsheet command won't work. Here's most of the code for a better idea:

Set xlApp = Excel.Application
xlApp.Visible = True
Set xlwb = xlApp.Workbooks.Add
Set xlWs = xlwb.Worksheets.Add
xlWs.Name = "Data"
    
'***Recordset

Set db = CurrentDb
Set qrydef = db.QueryDefs("qryIssueswithDateParameters")
For Each prm In qrydef.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rst = qrydef.OpenRecordset(dbOpenDynaset)


'Set rst = CurrentDb.OpenRecordset("select * from qryIssueswithDateParameters", dbOpenSnapshot)

xlApp.Visible = True
xlApp.ScreenUpdating = True
xlApp.DisplayAlerts = False

'******************** adding header to excel worksheet

    For Each fld In rst.Fields
    xlWs.Cells(1, i).Value = fld.Name
    xlWs.Cells(1, i).Font.Bold = True
    i = i + 1
    Next fld
    rst.MoveFirst

    xlWs.Range("A2").CopyFromRecordset rst

 

[maxsun08]

I finally got it working. The prompts don't pop up but parameter values now come from textboxes on the form.

Set db = CurrentDb
Set qrydef = db.QueryDefs("qryIssueswithDateParameters")

    qrydef.Parameters("Start Date") = Me.txtStartDate
    qrydef.Parameters("End Date") = Me.txtEndDate

Set rst = qrydef.OpenRecordset(dbOpenDynaset)

Regards,

 

[CJ_London]

well done! Was going to be my next suggestion.

 

[utzja1]

Useful dialog, helped me greatly with my project.