Query Run From Form Using VBA to set criteria from List Box

[alansidman]

I am running a query with criteria established in a search form. Additionally, the client wishes to select mulitple locations. I have employed a list box (with 66 locations).



The following code when started with a command button does nothing. No error message no query opened. Nothing. It does not seem to pass the strCriteria from the list box to the SQL statement.

The SQL statement when employed in a query and the Div criteria manually entered runs without issue.

What am I missing?

Option Compare Database


Private Sub cmdAllProduct_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"
    End If
    Exit Sub
    
    'Establish variables
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("qFI71NoProdFilter")
    
    '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,"
    strSQL = strSQL & "tblSource.TRANSACTION, tblTranXCode.[Transaction Description], tblTranXCode.GLAccount, tblSource.ClassCode,"
    strSQL = strSQL & "[Class Table].[Class Name], tblSource.[TRANS DT], tblSource.[PROD NBR], tblSource.DESCRIPTON, tblSource.STS, tblSource.[SLS UOM],"
    strSQL = strSQL & "tblSource.CASE, tblSource.Each, tblSource.QUANTITY, tblSource.[UNIT COST], tblSource.[EXTND COST]"
    strSQL = strSQL & "FROM ((tblSource LEFT JOIN [Class Table] ON tblSource.ClassCode = [Class Table].[Class Code]) LEFT JOIN t_DIV ON tblSource.Div = t_DIV.BRNCH_ID) LEFT JOIN tblTranXCode ON tblSource.TRANSACTION = tblTranXCode.TRANSACTION"
    strSQL = strSQL & "WHERE (((tblSource.[TRANS DT]) Between [Forms]![frmSearch].[txtStart] And [Forms]![frmSearch].[txtEnd]) AND tblSource.Div IN(" & strCriteria & "));"
    
    qdf.SQL = strSQL
    
    'Run Query
    DoCmd.OpenQuery "qFI71NoProdFilter"
    
    'Clear Memory
    Set db = Nothing
    Set qdf = Nothing
    
    
End Sub

[ItsMe]

The following jumped out at me.

    If IsNull(Me.txtStart) Or IsNull(Me.txtEnd) Then
    MsgBox "You need to enter valid Start and End Dates"
    End If
    Exit Sub

Perhaps an end if or else is in order. Is there a need for further analysis?

[alansidman]

I see what you mean. Need to put the Exit Sub before the End if.

Thanks much. Whew! This has been killing me as it was working before I put the test for null strings in yesterday and then not working today. Good eyes!!

[ItsMe]

A fresh set of eyes are nice when yours are burning from the screen radiation!