Exporting query data to EXCEL

[utzja1]

I have a query-based report with a macro button that exports selected items from the source query to an EXCEL template. The query previously took two explicit parameter entries; now, I have the data in unbound text boxes on the form where the report is created. I did this so I could validate the input before passing it to the query, otherwise Access generated a blank report for an item that wasn't in the database.



Below is an excerpt of the OLD code.


Set Db = CurrentDb
Set qrydef = Db.QueryDefs("QueryName")
qrydef.Parameters("Param1") = Me.UnitCost
qrydef.Parameters("Param2") = Me.ItemNum

Set rs = qrydef.OpenRecordset(dbOpenDynaset)
oWksheet.Select
With oWksheet
.Range("C3").Value = rs!QueryField1
.Range("C4").Value = rs!QueryField2

.Range("C5").Value = rs!QueryField3
End With

rs.Close
Set rs = Nothing



Before I changed the query set-up, this code passed values to Excel without any problems. So I tried the following code to pull in the data from the form.



Set Db = CurrentDb

Set rs = qrydef.OpenRecordset("QueryName", dbOpenDynaset)
qrydef.Parameters = Forms!BridgeRptsF!ItemNum
qrydef.Parameters = Forms!BridgeRptsF!UnitCost



My question is what changes I need to make in order to get the values from the form to the query so that the query will execute. This code only produces a blank template.



Many thanks for looking at this.

 

[utzja1]

Private Sub ExportExcel_Click()

Dim oExcel As Excel.Application
Dim oWkBk As Excel.Workbook
Dim oWksheet As Excel.Worksheet
Dim strFile As String
Dim qSource As String
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

strFile = "Filepath.extension"
Set oExcel = Excel.Application
Set oWkBk = oExcel.Workbooks.Open(strFile)
Set oWksheet = oWkBk.Sheets(1)
oExcel.Visible = True

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("QueryName")
qdf.Parameters("Forms!FormName!FieldID") = Forms!FormName!FieldID
qdf.Parameters("Forms!BridgeRptsF!UnitCost") = Forms!BridgeRptsF!UnitCost
Set rst = qdf.OpenRecordset()


oWksheet.Select
With oWksheet
.Range("C3").Value = rst!QueryField1
.Range("C4").Value = rst!QueryField2
.Range("C5").Value = rst!QueryField3
End With

rst.Close

Set rst = Nothing
Set oWksheet = Nothing

End Sub