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.
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