Have an unbound form (frmBuildCustomReport) with combo boxes whose row sources pull from a couple of tables. The form's command button then feeds the user's selection(s) into a query (qryBuildCustomReport) that ties it all together.
I've added two check boxes to this form. One check box outputs the query to a report. That works fine. The second checkbox, if selected, I would like it to copy the recordset of the qryBuildCustomReport and paste it into a new Excel workbook - an extra option if the user needs the info in Excel instead of the Access report.
Problem is something in my VBA...it creates a new Book1, correctly names the tab, but there's no data in it when it opens. It also creates Microsoft Visual Basic Run-time error 3061: "Too few poarameters. Expected 5."
There are 5 combo boxes on the form, so I'm assuming that's why it says that. However I have the query set up already to return all results for the combo boxes where nothing is selected.
Can anyone help me with this coding?
I've added two check boxes to this form. One check box outputs the query to a report. That works fine. The second checkbox, if selected, I would like it to copy the recordset of the qryBuildCustomReport and paste it into a new Excel workbook - an extra option if the user needs the info in Excel instead of the Access report.
Code:
Private Sub cmdOK_Click()
'If View/Print Report box is checked, send it to an Access report'
If Me!CheckViewPrintReport.Value = True Then
DoCmd.OpenReport "rptCustomBuiltSheet", acViewPreview
DoCmd.Close acForm, "frmBuildCustomReport"
Exit Sub
'If Export to Excel box is checked, export to a new Excel spreadsheet'
ElseIf Me!CheckExportExcel.Value = True Then
Dim dbs As DAO.Database
Dim rstGetRecordSet As Recordset
Dim objXL As Object
Dim objCreateWkb As Object
Dim objActiveWkb As Object
Set dbs = CurrentDb
Set objXL = CreateObject("Excel.Application")
Set objCreateWkb = objXL.Workbooks.Add
Set objActiveWkb = objXL.Application.ActiveWorkBook
objXL.Visible = True
objActiveWkb.Sheets.Add
objActiveWkb.Worksheets(1).Name = "Penetrations"
Set rstGetRecordSet = dbs.OpenRecordset("qryBuildCustomReport")
objActiveWkb.Worksheets("Penetrations").Cells(1, 1).CopyFromRecordset rstGetRecordSet
Set objActiveWkb = Nothing
Set objCreateWkb = Nothing
Set objXL = Nothing
rstGetRecordSet.Close
dbs.Close
Set rstGetRecordSet = Nothing
Set dbs = Nothing
Exit Sub
'If neither box is checked, prompt user with message box'
ElseIf Me!CheckViewPrintReport.Value = False And Me!CheckExportExcel.Value = False Then
MsgBox "Please check either View/Print Standard Report or Export to Excel", vbOKOnly
Me!CheckViewPrintReport.SetFocus
Exit Sub
Else
End If
End Sub
Problem is something in my VBA...it creates a new Book1, correctly names the tab, but there's no data in it when it opens. It also creates Microsoft Visual Basic Run-time error 3061: "Too few poarameters. Expected 5."
There are 5 combo boxes on the form, so I'm assuming that's why it says that. However I have the query set up already to return all results for the combo boxes where nothing is selected.
Can anyone help me with this coding?