One option would be to generate the appropriate SQL, save it to a temporary
query and export the temporary query.
Since you're currently using a query, you could try something like:
Private Sub Command84_Click()
On Error GoTo Err_Command84_Click
Dim dbCurr As DAO.Database
Dim qdfTemp As DAO.QueryDef
Dim lngOrderBy As Long
Dim strQueryName As String
Dim strSQL As String
' You only need to go to this effort if there's a filter
If Len(Me.Filter) > 0 Then
Set dbCurr = CurrentDb
' Get the SQL for the existing query
strSQL = dbCurr.QueryDefs("QryAdageVolumeSpend").SQL
' Check whether there's an ORDER BY clause in the SQL.
' If there is, we need to put the WHERE clause in front of it.
lngOrderBy = InStr(strSQL, "ORDER BY")
If lngOrderBy > 0 Then
strSQL = Left(strSQL, lngOrderBy - 1) & _
" WHERE " & Me.Filter & " " & _
Mid(strSQL, lngOrderBy)
Else
' There's no ORDER BY in the SQL.
' Remove the semi-colon from the end, then append the WHERE clause
strSQL = Left(strSQL, Len(strSQL) - 1) & _
" WHERE " & Me.Filter
End If
' By using the current date and time, hopefully that means
' a query by that name won't already exist
strQueryName = "qryTemp" & Format(Now, "yyyymmddhhnnss")
' Create the temporary query
Set qdfTemp = dbCurr.CreateQueryDef(strQueryName, strSQL)
' Export the temporary query
DoCmd.TransferSpreadsheet transfertype:=acExport, _
spreadsheettype:=acSpreadsheetTypeExcel9, _
tableName:=strQueryName, FileName:= _
"C:\Documents and Settings\rfitz03\My Documents\AdageData.xls", _
hasfieldnames:=True
' Delete the temporary query
dbCurr.QueryDefs.Delete strQueryName
Else
DoCmd.TransferSpreadsheet transfertype:=acExport, _
spreadsheettype:=acSpreadsheetTypeExcel9, _
tableName::="QryAdageVolumeSpend", FileName:= _
"C:\Documents and Settings\rfitz03\My Documents\AdageData.xls", _
hasfieldnames:=True
End If
Exit_Command84_Click:
Set dbCurr = Nothing
Exit Sub
Err_Command84_Click:
MsgBox Err.Description
Resume Exit_Command84_Click
End Sub
Note that I've only handled simple queries there: a SELECT with no WHERE
clause but (possibly) an ORDER BY clause. You know what QryAdageVolumeSpend
looks like: you may have to modify the code. Of course, you also have the
option of simply putting the SQL there, as opposed to retrieving the SQL
from QryAdageVolumeSpend and working with it.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
OK I got the export vba I think. this code exports the query but the
whole thing and not the filtered selection on the form.
Private Sub Command84_Click()
On Error GoTo Err_Command84_Click
DoCmd.TransferSpreadsheet transfertype:=acExport, _
spreadsheettype:=acSpreadsheetTypeExcel9, _
tableName:="QryAdageVolumeSpend", FileName:= _
"C:\Documents and Settings\rfitz03\My Documents\AdageData.xls", _
hasfieldnames:=True
Exit_Command84_Click:
Exit Sub
Err_Command84_Click:
MsgBox Err.Description
Resume Exit_Command84_Click
End Sub
I changed the form source from table to query. I made a query of the
table and made the form pull that instead. How do I link the form to
the query? When I linked up the combo boxes to the query and tried to
select an item it gave me an error. Error: You cannot add new clients
to this search form. Permission denied. What’s this mean? How do I get
the query to have the same results as the form, so when I export with
the code above it’ll give me the filtered list and not all 70k
records?
