Hi Bdavis,
Here is how I do it, for a QBF (Query by Form) search form that displays the
search results in a subform, and allows the capability to export the results
to Excel. I'm happy to send you a copy of this sample, if you send me a
private e-mail message with a valid reply-to address. My e-mail address can
be found at the bottom of the contributors page indicated in my signature.
Public Function RequerySubform()
On Error GoTo ProcError
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim intRecordCount As Integer
Set db = CurrentDb()
Set qdf = db.QueryDefs("qryMovieSelections")
<snip -- code to build strFullSQL goes here. This string variable serves as
the recordsource for the subform.>
qdf.SQL = strFullSQL
Set rs = db.OpenRecordset("qryMovieSelections")
intRecordCount = rs.RecordCount
If intRecordCount = 0 Then
cmdExportToExcel.Enabled = False
Else
cmdExportToExcel.Enabled = True
End If
<snipped a bit more code here>
ExitProc: 'Clean up
On Error Resume Next
qdf.Close: Set qdf = Nothing
rs.Close: Set rs = Nothing: db.Close: Set db = Nothing
Exit Function
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, , _
"Error in RequerySubform event procedure..."
Resume ExitProc
End Function
Private Sub cmdExportToExcel_Click()
On Error GoTo ProcError
Dim strPath As String
strPath = CurrentProject.Path
DoCmd.OutputTo acOutputQuery, "qryMovieSelections", acFormatXLS, _
strPath & "\MovieSelections.xls" ', AutoStart:=-1
MsgBox "The selected movies have been exported to the file
MovieSelections.xls" & vbCrLf _
& "in the folder:" & vbCrLf & strPath, vbInformation, "Export
Complete..."
ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, , _
"Error in cmdExportToExcel_Click event procedure..."
Resume ExitProc
End Sub
My QBF form is an unbound form, so you'd have to do a slight modification in
order to incorporate ParentID=" & txtParentID, but that should be rather easy.
Tom
http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
:
Tom,
I think the problem here will be that the query output is filted at the form
level. It's the filted data I want to export, not the entire query result.
__________________________________________
:
Steve,
Why the need to first append the data to an export table? This will just
contribute to unnecessary database bloat.
Export the query to your spreadsheet
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"qryName", "c:\myspreadsheet.xls"
where "qryName" is the name of your query. Make the appropriate substitution.
Tom
http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
:
The data in the subform can be derived from a query.
Suppose that the recordsource is qryZYX and your ParentID is in a field
called txtparentID
Append the data to an export table:
currentdb.execute "INSERT INTO tblExportSubForm SELECT * from qryZYX WHERE
ParentID=" & txtParentID
Export the table to your spreadsheet
docmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"tblExportSubForm", "c:\myspreadsheet.xls"
--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips:
http://www.fmsinc.com/free/tips.html
__________________________________________
I have a datasheet subform. On the mainform, I'd like to put a command
button that exports the data shown in the subform (data is filtered on the
mainform) to Excel.
And then, if possible, I'd like it to open in excel and execute an excel
macro on the exported data.
The first part is the most important. Thanks in advance!
