CurrentDb.QueryDefs.Delete is Permanently Deleting my Query
Posted on 2007-10-19
Hello, I have a string that takes selections from a multi-select list box, and passes it to a query which is taking a date range from 2 text boxes, and then outputs to an Excel file. The problem is, if I run the query and leave the 2 date text boxes blank, my query is deleted, but if a date is selected it works fine and nothing is deleted. I'm not that familiar with VB so I'm not sure how to fix. Here's the individual line deleting the query, and the entire code if anyone knows how to keep this from occurring:
Private Sub cmdAvgCostMileXLS_Click()
Dim qd As DAO.QueryDef
Set qd = Nothing
On Error Resume Next 'to ignore the error
On Error GoTo Err_Handler 'to break if there is an error
Set qd = CurrentDb.CreateQueryDef("qryAvgCostMileXLS", _
"Select * from qryAvgCostMile where " & BuildWhereClause())
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"qryAvgCostMileXLS", "C:\" & "AvgCostMile" & ".xls"
MsgBox "Your file has been saved as " & "C:\" & "AvgCostMile" & ".xls", vbOKOnly
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdAvgCostMileXLS_Click"