complete query before transferspreadsheet; only header row displayed

[nanoenterprises]

I have a form combo box that filters a query. However when it exports
the results of the query to excel, only the header is shown. I have a
feeling that the export function doesn't wait for the query to finish
before exporting. I've been reading all over and found similar issues,
but can't seem to resolve this problem. Any assistance is greatly
appreciated.


Event procedure:

Private Sub ACPSelect_Change()
DoCmd.OpenQuery "qry for Assistant CP"
Me.ACPSelect = ""
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry for
Assistant CP", "Z:\Region1\Rates for Assistant CP " & Format(Date,"mm-
dd-yy") & ".xls", True, , True
DoCmd.Close acQuery, "qry Rates for Assistant CP", acSaveNo
End Sub

 

[Allen Browne]

What I normally do is to modify the SQL property of the QueryDef so that it
contains the right criteria.

This example assumes you created a query for this purpose, and named it
(say) Query4Export. You can copy the SQL statement from SQL View of the
query, and paste it into your code. Then break it the string where the
parameter goes, and concatenate the correct value in.

This kind of thing:
Dim strFile As String
Dim strSql As String
Const strcQuery = "Query4Export"

strFile = "Z:\Region1\Rates for Assistant CP " & Format(Date,"mm-dd-yy")
& ".xls"
strSql = "SELECT * FROM Table1 WHERE SomeField = " & _
Forms![ACP by Region]!ACPSelect & " ORDER BY SomeField;"
CurrentDb.QueryDefs(strcQuery).SQL = strSql
'Debug.Print strSql
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strcQuery, strFile, True, , True

 

[Dirk Goldgar]

I have a form combo box that filters a query. However when it exports
the results of the query to excel, only the header is shown. I have a
feeling that the export function doesn't wait for the query to finish
before exporting. I've been reading all over and found similar issues,
but can't seem to resolve this problem. Any assistance is greatly
appreciated.


Event procedure:

Private Sub ACPSelect_Change()
DoCmd.OpenQuery "qry for Assistant CP"
Me.ACPSelect = ""
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry for
Assistant CP", "Z:\Region1\Rates for Assistant CP " & Format(Date,"mm-
dd-yy") & ".xls", True, , True
DoCmd.Close acQuery, "qry Rates for Assistant CP", acSaveNo
End Sub

You don't need to open and close the query, because TransferSpreadsheet is
using the query as stored, not as open on the screen. The problem you're
seeing, I think, is that you're clearing the control that the query is using
for a criterion, before you perform the export. Try doing just this:

'----- start of code -----
Private Sub ACPSelect_AfterUpdate()

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"qry for Assistant CP", _
"Z:\Region1\Rates for Assistant CP " & _
Format(Date,"mm-dd-yy") & ".xls", _
True, , True

DoEvents

Me.ACPSelect = ""

End Sub
'----- end of code -----

Note: I changed the event from the Change event to the AfterUpdate event.
I'm not sure what type of control ACPSelect is, but for most purposes the
AfterUpdate event is better to use than the Change event, because you can be
sure that the user is finished typing in the control.

 

[Nanobi]

Wow...Thanks to the both of you for the assistance. I ended up using
Dirk's method because of my limited VB & SQL knowledge. It worked
perfectly. I probably should have posted a while ago.