Results 1 to 4 of 4
  1. #1
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529

    Receiving Error Message when running Query second time

    I have a query that is driven by a search form with several criteria. The query works fine the first time it is run. If the user wishes to run the query a second time with different criteria, the following error message appears.

    Click image for larger version. 

Name:	Capture.PNG 
Views:	13 
Size:	47.3 KB 
ID:	15833

    The criteria are cleared prior to running the query a second time. All the criteria fields are set to Null and me.requery is the last line in the code.



    The line of code highlighted when debug is selected is as follows:

    Code:
    DoCmd.OpenQuery "qAggregate"
    Here is the code to run the query.

    Code:
    Private Sub cmdSummary_Click()
        'Define variables
        Dim db As DAO.Database
        Dim qdf As DAO.QueryDef
        Dim varItem As Variant
        Dim strCriteria As String
        Dim strSQL As String
        
        If IsNull(Me.txtStart) Or IsNull(Me.txtEnd) Then
        MsgBox "You need to enter valid Start and End Dates"
        Exit Sub
        End If
        
        'Establish variables
        Set db = CurrentDb()
        Set qdf = db.QueryDefs("qAggregate")
        
        'Determine Criteria from list box selections
        For Each varItem In Me!List0.ItemsSelected
            strCriteria = strCriteria & "," & Me!List0.ItemData(varItem)
        Next varItem
        
        'Test if selections have been made.  If none, exit routine
        If Len(strCriteria) = 0 Then
            MsgBox "You did not select anything from the list" _
                , vbExclamation, "Nothing to find!"
            Exit Sub
        End If
        
        'Remove the leading comma from the criteria set above
        strCriteria = Right(strCriteria, Len(strCriteria) - 1)
              
        'Create Query using SQL
        strSQL = "SELECT tblSource.[Rpt Name], t_DIV.BRNCH_CD, tblSource.Div, t_DIV.BRNCH_NM, t_DIV.RGN_NM, tblSource.TRANSACTION, tblSource.[Transaction Desc], tblTranXCode.GLAccount, [Class Table].[Class Code], [Class Table].[Class Name], tblSource.[TRANS DT], tblSource.[PROD NBR], tblSource.DESCRIPTON, tblSource.STS, tblSource.[SLS UOM], tblSource.[UNIT COST], Sum(tblSource.CASE) AS SumOfCASE, tblSource.Each, Sum(tblSource.QUANTITY) AS SumOfQUANTITY, Sum(tblSource.Each) AS SumOfEach, Sum(tblSource.[EXTND COST]) AS [SumOfEXTND COST] " & vbCrLf & _
        "FROM ((tblSource LEFT JOIN tblTranXCode ON tblSource.TRANSACTION = tblTranXCode.TRANSACTION) LEFT JOIN t_DIV ON tblSource.Div = t_DIV.BRNCH_ID) LEFT JOIN [Class Table] ON tblSource.[Class Code] = [Class Table].[Class Code] " & vbCrLf & _
        "GROUP BY tblSource.[Rpt Name], t_DIV.BRNCH_CD, tblSource.Div, t_DIV.BRNCH_NM, t_DIV.RGN_NM, tblSource.TRANSACTION, tblSource.[Transaction Desc], tblTranXCode.GLAccount, [Class Table].[Class Code], [Class Table].[Class Name], tblSource.[TRANS DT], tblSource.[PROD NBR], tblSource.DESCRIPTON, tblSource.STS, tblSource.[SLS UOM], tblSource.[UNIT COST], tblSource.Each " & vbCrLf & _
        "HAVING (((tblSource.Div) In (" & strCriteria & ")) AND ((tblSource.[TRANS DT]) Between [Forms]![frmSearch].[txtStart] And [Forms]![frmSearch].[txtEnd]) AND ((tblSource.[PROD NBR])=[Forms]![frmSearch].[txtProd1] Or (tblSource.[PROD NBR])=[Forms]![frmSearch].[txtProd2] Or (tblSource.[PROD NBR])=[Forms]![frmSearch].[txtProd3] Or (tblSource.[PROD NBR])=[Forms]![frmSearch].[txtProd4] Or (tblSource.[PROD NBR])=[Forms]![frmSearch].[txtProd5]));"
        qdf.SQL = strSQL
        
        'Run Query
        DoCmd.OpenQuery "qAggregate"
        
        'Clear Memory
        Set db = Nothing
        Set qdf = Nothing
            
        MsgBox "Completed"
       
    
    
    End Sub
    If I close the form and reopen and then run the query, it works fine. I am thinking that I may need a refresh on the form. Your thoughts on this?



    Alan

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Are you creating a new query object named "qAggregate"? In other words, do you have an existing query named "qAggregate" that you are only trying to change the SQl?

    If this is a temp object then delete it after you run the SQL.

    db.QueryDefs.Delete ("qAggregate")

    If this is not a temp query object then just be sure to close it.

    So something like this maybe......

    Code:
    'Clear Memory
    qdf.Close
    Set qdf = Nothing
    Set db = Nothing 'I usually reverse order from the order of instantiation
    
    MsgBox "Completed"

  3. #3
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    Thanks again for your help. Reversing the lines and closing the qdf worked.

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Cool......

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 15
    Last Post: 11-01-2013, 03:24 PM
  2. Replies: 6
    Last Post: 06-27-2013, 12:38 PM
  3. Replies: 11
    Last Post: 08-27-2012, 11:26 AM
  4. Replies: 1
    Last Post: 12-02-2010, 03:04 PM
  5. Receiving error on contact database
    By tcd2004 in forum Access
    Replies: 6
    Last Post: 03-12-2010, 06:43 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums