mickmullen
Registered User.
- Local time
- Yesterday, 21:50
- 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
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