Check Box to exclude from search (1 Viewer)

mickmullen

Registered User.
Local time
Today, 16:16
Joined
Oct 30, 2018
Messages
19
I have a multi select list with VBA code feeding a query. Currently, if I select one or many of the items in the list, I get a query that is filtered by those selections.


I'm realizing that it will be necessary to have the option to exclude the selected items from the list. The form needs a check Box (I think) that tells the query to include everything but the selected items.



I'm new to this stuff, just got this Code working today. Any help on how this gets written would be much appreciated.


Heres the current Code


Code:
Private Sub Command204_Click()
' Declare variables
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim varItem As Variant
    Dim varItem2 As Variant
    Dim varItem3 As Variant
    Dim varItem4 As Variant
    Dim strCriteria As String
    Dim strCriteria2 As String
    Dim strCriteria3 As String
    Dim strCriteria4 As String
    Dim strSql As String
' Get the database and stored query
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("WorkOrdersQuery")
' Loop through the selected items in the list box and build a text string
    If Me!ListRmNum.ItemsSelected.Count > 0 Then
        For Each varItem In Me!ListRmNum.ItemsSelected
            strCriteria = strCriteria & "[Estimate Data.room Number] = " & Chr(34) _
                          & Me!ListRmNum.ItemData(varItem) & Chr(34) & "OR "
        Next varItem
        strCriteria = Left(strCriteria, Len(strCriteria) - 3)
    Else
        strCriteria = "[Estimate Data.room number] Like '*'"
    End If
' Loop through the selected items in the list box and build a text string
    If Me!ListMemo.ItemsSelected.Count > 0 Then
        For Each varItem2 In Me!ListMemo.ItemsSelected
            strCriteria2 = strCriteria2 & "[Estimate Data.Memo] = " & Chr(34) _
                          & Me!ListMemo.ItemData(varItem2) & Chr(34) & "OR "
        Next varItem2
        strCriteria2 = Left(strCriteria2, Len(strCriteria2) - 3)
    Else
        strCriteria2 = "[Estimate Data.Memo] Like '*'"
    End If
' Build the new SQL statement incorporating the string
    strSql = "SELECT [Estimate Data].Qty, [Estimate Data].[U/M], [Estimate Data].Rate, [Estimate Data].Amount, [Estimate Data].[Room Number], [Estimate Data].[Project Number], [Estimate Data].Class, [Estimate Data].Memo " & vbCrLf & _
"FROM [Estimate Data] " & vbCrLf & _
"WHERE ((" & strCriteria & ") AND(" & strCriteria2 & ") AND (([Estimate Data].[Project Number])=[Forms]![WorkOrders1]![Project Number]) AND (([Estimate Data].Class)=[Forms]![WorkOrders1]![cboClass]));"
' Apply the new SQL statement to the query
    qdf.SQL = strSql
' Open the query
    DoCmd.OpenQuery "WorkOrdersQuery"
    DoCmd.Close acQuery, "WorkOrdersQuery"
'Fill In the Blanks
    ' Loop through the selected items in the list box and build a text string
    If Me!ListMemo.ItemsSelected.Count > 0 Then
        For Each varItem3 In Me!ListMemo.ItemsSelected
            strCriteria3 = strCriteria3 & Me!ListMemo.ItemData(varItem3) & ", "
        Next varItem3
        strCriteria3 = Left(strCriteria3, Len(strCriteria3) - 1)
    End If
    Text208 = strCriteria3
    ' Loop through the selected items in the list box and build a text string
    If Me!ListRmNum.ItemsSelected.Count > 0 Then
        For Each varItem4 In Me!ListRmNum.ItemsSelected
            strCriteria4 = strCriteria4 & Me!ListRmNum.ItemData(varItem4) & ", "
        Next varItem4
        strCriteria4 = Left(strCriteria4, Len(strCriteria4) - 1)
    End If
    Text206 = strCriteria4
' Empty the memory
    Set db = Nothing
    Set qdf = Nothing
End Sub
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:16
Joined
May 7, 2009
Messages
19,169
let's say that your Checkbox name on the form is chkExcludeRoom (the label
something like "Excluded Rooms", and chkExcludeMemo ("Exclude Memos")
there are two checkboxes because of the two listbox.

if the chkExclude is checked, then the selected items are excluded
from the query, otherwise included
Code:
Private Sub Command204_Click()
' Declare variables
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim varItem As Variant
    Dim varItem2 As Variant
    Dim varItem3 As Variant
    Dim varItem4 As Variant
    Dim strCriteria As String
    Dim strCriteria2 As String
    Dim strCriteria3 As String
    Dim strCriteria4 As String
    Dim strSql As String
' Get the database and stored query
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("WorkOrdersQuery")
' Loop through the selected items in the list box and build a text string
    If Me!ListRmNum.ItemsSelected.Count > 0 Then
        For Each varItem In Me!ListRmNum.ItemsSelected
            'add the chkExclude checkbox
            If (Me!chkExcludeRoom = False) Then
                strCriteria = strCriteria & "[Estimate Data.room Number] = " & Chr(34) _
                          & Me!ListRmNum.ItemData(varItem) & Chr(34) & " OR "
            Else
                strCriteria = strCriteria & "[Estimate Data.room Number] <> " & Chr(34) _
                          & Me!ListRmNum.ItemData(varItem) & Chr(34) & " AND "
            End If
        Next varItem
        If (Me!chkExcludeRoom = False) Then
            strCriteria = Left(strCriteria, Len(strCriteria) - 4)
        Else
            strCriteria = Left(strCriteria, Len(strCriteria) - 5)
        End If
    Else
        strCriteria = "[Estimate Data.room number] Like '*'"
    End If
' Loop through the selected items in the list box and build a text string
    If Me!ListMemo.ItemsSelected.Count > 0 Then
        For Each varItem2 In Me!ListMemo.ItemsSelected
            If (Me!chkExcludeMemo = False) Then
                strCriteria2 = strCriteria2 & "[Estimate Data.Memo] = " & Chr(34) _
                          & Me!ListMemo.ItemData(varItem2) & Chr(34) & " OR "
            Else
                strCriteria2 = strCriteria2 & "[Estimate Data.Memo] <> " & Chr(34) _
                          & Me!ListMemo.ItemData(varItem2) & Chr(34) & " AND "
            End If
        Next varItem2
        If (Me!chkExcludeMemo = False) Then
            strCriteria2 = Left(strCriteria2, Len(strCriteria2) - 4)
        Else
            strCriteria2 = Left(strCriteria2, Len(strCriteria2) - 5)
        End If
    Else
        strCriteria2 = "[Estimate Data.Memo] Like '*'"
    End If
' Build the new SQL statement incorporating the string
    strSql = "SELECT [Estimate Data].Qty, [Estimate Data].[U/M], [Estimate Data].Rate, [Estimate Data].Amount, [Estimate Data].[Room Number], [Estimate Data].[Project Number], [Estimate Data].Class, [Estimate Data].Memo " & vbCrLf & _
"FROM [Estimate Data] " & vbCrLf & _
"WHERE ((" & strCriteria & ") AND(" & strCriteria2 & ") AND (([Estimate Data].[Project Number])=[Forms]![WorkOrders1]![Project Number]) AND (([Estimate Data].Class)=[Forms]![WorkOrders1]![cboClass]));"
' Apply the new SQL statement to the query
    qdf.SQL = strSql
' Open the query
    DoCmd.OpenQuery "WorkOrdersQuery"
    DoCmd.Close acQuery, "WorkOrdersQuery"
'Fill In the Blanks
    ' Loop through the selected items in the list box and build a text string
    If Me!ListMemo.ItemsSelected.Count > 0 Then
        For Each varItem3 In Me!ListMemo.ItemsSelected
            strCriteria3 = strCriteria3 & Me!ListMemo.ItemData(varItem3) & ", "
        Next varItem3
        strCriteria3 = IIf(Me.chkExcludeRoom = False, "Included: ", "Excluded: ") & Left(strCriteria3, Len(strCriteria3) - 1)
    End If
    Text208 = strCriteria3
    ' Loop through the selected items in the list box and build a text string
    If Me!ListRmNum.ItemsSelected.Count > 0 Then
        For Each varItem4 In Me!ListRmNum.ItemsSelected
            strCriteria4 = strCriteria4 & Me!ListRmNum.ItemData(varItem4) & ", "
        Next varItem4
        strCriteria4 = IIf(Me.chkExcludeMemo = False, "Included: ", "Excluded: ") & Left(strCriteria4, Len(strCriteria4) - 1)
    End If
    Text206 = strCriteria4
' Empty the memory
    Set db = Nothing
    Set qdf = Nothing
End Sub
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 16:16
Joined
Apr 27, 2015
Messages
6,286
..or you could use Arnel’s contribution! Sorry Ariel, you must have posted while I was replying
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:16
Joined
May 7, 2009
Messages
19,169
you are right there friend, using In () / Not In(). it makes the strCriteria short.
 

June7

AWF VIP
Local time
Today, 12:16
Joined
Mar 9, 2014
Messages
5,423
Is this a multi-user db with simultaneous users? Users will conflict with each other if you use a check field that users can change as selection criteria.
 

mickmullen

Registered User.
Local time
Today, 16:16
Joined
Oct 30, 2018
Messages
19
June7- Yes there will be 2-3 users, potentially simultaneous, but highly unlikely they will be in the same Record at the same time. That said, I don't think that I understand the problem with the check field...
 

June7

AWF VIP
Local time
Today, 12:16
Joined
Mar 9, 2014
Messages
5,423
If checkbox is bound to field then checking/unchecking changes value in record. If simultaneous users are checking and unchecking records for selection, they will conflict.

If these are actually UNBOUND checkboxes, then there is no conflict.

And now that I re-read the posts and code, I have better idea of how the checkboxes are used. I was misled by the use of 'check field' in the narrative.
 
Last edited:

Users who are viewing this thread

Top Bottom