?
Solved

CurrentDb.QueryDefs.Delete is Permanently Deleting my Query

Posted on 2007-10-19
8
Medium Priority
?
1,151 Views
Last Modified: 2013-12-20
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:
CurrentDb.QueryDefs.Delete "qryAvgCostMileXLS"

Private Sub cmdAvgCostMileXLS_Click()
    Dim qd As DAO.QueryDef
    Set qd = Nothing
        CurrentDb.QueryDefs.Delete "qryAvgCostMileXLS"
     On Error Resume Next 'to ignore the error
CurrentDb.QueryDefs.Delete "qryAvgCostMileXLS"
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
Exit Sub
Err_Handler:
    If Err.Number <> 2501 Then  'Ignore "Report cancelled" error.
        MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdAvgCostMileXLS_Click"
    End If
End Sub
0
Comment
Question by:acarpen271
  • 4
  • 2
7 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 20108365
acarpen271 said:
>>but if a date is selected it works fine and nothing is deleted.

That's not at all what's happening: the query is being deleted and then recreated.

Let's take a step back: please describe in a couple of sentences what you are trying to accomplish.
0
 

Author Comment

by:acarpen271
ID: 20108381
Okay, Correction, what's happening is that when I don't make a selection from the list box, the query gets deleted but if I select something it does not. The date range text boxes have no affect, I guess I'm not completely awake!

What I'm trying to accomplish is for the user to make selections from a multi-select list box, choose a date range which is picked up by the query, and then output to an excel file upon hitting the command button.
0
 

Author Comment

by:acarpen271
ID: 20108401
Something else I just realized, that if I select a date, but select nothing in the multi-select list box, the query is not deleted and the output includes everything. The query is only deleted when there is no selection in the date range text boxes or the list box.  If I select nothing, I get Error 3145, syntax error in WHERE clause. Then the query, qryAvgCostMileXLS which is nothing more than Select * FROM qryAvgCostMile; is deleted.
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 20108512
acarpen271,

You need to post the code for BuildWhereClause as well.

Regards,

Patrick
0
 

Author Comment

by:acarpen271
ID: 20108522
Okay, here's the code for that portion, thanks again:

Private Function BuildWhereClause() As String
'On Error Goto Err_Handler

    Dim strField As String      'Name of your date field.
    Dim strWhere1 As String     'Where condition for OpenReport.
    Dim varItem As Variant      'Selected items
    Dim strWhere2 As String     'String to use as WhereCondition
    Dim strWhere3 As String
    Dim strDelim As String      'Delimiter for this field type.
    Const conDateFormat = "\#mm\/dd\/yyyy\#"

    strField = "CLOSE_OUT_DATE"
    If IsNull(Me.txtStartDate) Then
        If Not IsNull(Me.txtEndDate) Then   'End date, but no start.
            strWhere1 = strField & " <= " & _
                Format(Me.txtEndDate, conDateFormat)
        End If
    Else
        If IsNull(Me.txtEndDate) Then       'Start date, but no End.
            strWhere1 = strField & " >= " & _
                Format(Me.txtStartDate, conDateFormat)
        Else                                'Both start and end dates.
            strWhere1 = strField & " Between " & _
                Format(Me.txtStartDate, conDateFormat) & _
                " And " & Format(Me.txtEndDate, conDateFormat)
        End If
    End If

    strDelim = """"
    'Loop through the ItemsSelected in the list box.
    With Me.lstCustName
        For Each varItem In .ItemsSelected
            If Not IsNull(varItem) Then
                strWhere2 = strWhere2 & strDelim & _
                    .ItemData(varItem) & strDelim & ","
            End If
        Next
    End With
    'Remove trailing comma. Add field name, IN operator, and brackets.
    lngLen = Len(strWhere2) - 1
    If lngLen > 0 Then
        strWhere2 = "[CUSTOMER_NAME] IN (" & Left$(strWhere2, lngLen) & ")"
    End If

    If strWhere1 = "" Then
        strWhere3 = strWhere2
    ElseIf strWhere2 = "" Then
        strWhere3 = strWhere1
    Else
        strWhere3 = strWhere1 & " AND " & strWhere2
End If
BuildWhereClause = strWhere3
Exit Function

Err_Handler:
     MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdAvgCostMileXLS_Click"
End Function
0
 

Author Comment

by:acarpen271
ID: 20109574
Problem Solved (I think). I had to change the code to first create the temporary query, then delete it upon exiting rather than the other way around. Everything seems to work fine now. Only thing that concerns me is I also had to bypass the Error Handler as well. Here's the code if anyone has any suggestions that would improve this:

Private Sub cmdAvgCostMileXLS_Click()
    Dim qd As DAO.QueryDef
    Set qd = Nothing
             On Error Resume Next 'to ignore the 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
CurrentDb.QueryDefs.Delete "qryAvgCostMileXLS"
Exit Sub
Err_Handler:
    If Err.Number <> 2501 Then  'Ignore "Report cancelled" error.
        MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdAvgCostMileXLS_Click"
    End If
End Sub
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 21638891
PAQed with points refunded (250)

Computer101
EE Admin
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question