MultiSelect List Box to Filter Form

C

Confused

I added a MultiSelect List Box and now I am trying to make it work with the
other combo boxes on the form to filter records.

It apparently doesn't work the same way as filtering with the combos. I
have attached the code (adapted from AllenBrowne.com). What I added is the
part "Me.LstCategory". Could someone provide some insight into how to
adapt this to make it work to filter records in the LstCategory List Box
along with the other Combo boxes?


Private Sub cmdfilter_Click()
Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"


If Not IsNull(Me.cboEmployees) Then
strWhere = strWhere & _
" [CLECID] IN(SELECT [cLECID]" & _
" FROM tblemployeeAssignments" & _
" WHERE [employeeId] = " & Me.cboEmployees & ") AND "

End If



If Not IsNull(Me.Text58) Then
strWhere = strWhere & "([CLEC Name] Like ""*" & Me.Text58 & "*"") And "

End If

If Not IsNull(Me.LstCategory) Then
strWhere = strWhere & _
" [CLECID] In (Select [CLECID]" & _
" From [CLEC Systems3]" & _
"Where [system id] = " & Me.LstCategory & ") And "
End If


'See if the string has more than 5 characters (a trailng " AND ") to remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If

End Sub
 
C

Confused

Hi again,

It now only pulls up one record when I select one of the items in the
listbox. If I select multiple items in the listbox it might pull up two or
three records. Neither pertaining to the entire list. CLECID is a long
integer data type.

KenSheridan via AccessMonster.com said:
A multi-select list box has an ItemsSelected collection, which unlike most
collections, is not a collection of objects but of variants, each being an
integer index which refers to a selected row in the control. You process the
selected rows by looping through this collection in code and building a
string expression which can then be used as part of the Filter property.

So the code it would be something like this:

Dim ctrl as Control
Dim strWhere As String
Dim strCLECIDList As String
Dim strCriteria As String
Dim lngCLECID As Long
Dim varItem As Variant

set ctrl = Me.LstCategory

If Not IsNull(Me.cboEmployees) Then
strWhere = strWhere & _
"And CLECID In(SELECT CLECID" & _
" FROM tblemployeeAssignments" & _
" WHERE [employeeId] = " & Me.cboEmployees & ") "
End If

If Not IsNull(Me.Text58) Then
strWhere = strWhere & "And [CLEC Name] Like ""*" & Me.Text58 & "*"" "
End If

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
' look up CLECID value for selected system id
' and add to value list
strCriteria = "[system id] = " & ctrl.ItemData(varItem)
lngCLECID = DLookup("CLECID", "[CLEC Systems3]", strCriteria)
strCLECIDList = strCLECIDList & "," & lngCLECID
Next varItem

' remove leading comma from value list
strCLECIDList = Mid(strCLECIDList , 2)

strWhere = strWhere & "And CLECID In(" & strCLECIDList & ")"
End If

' remove leading "And "
strWhere = Mid(strWhere,5)

' set form's Filter property and apply filter
Me.Filter = strWhere
Me.FilterOn = True


I've assumed that CLECID is a long integer number data type.

Ken Sheridan
Stafford, England
I added a MultiSelect List Box and now I am trying to make it work with the
other combo boxes on the form to filter records.

It apparently doesn't work the same way as filtering with the combos. I
have attached the code (adapted from AllenBrowne.com). What I added is the
part "Me.LstCategory". Could someone provide some insight into how to
adapt this to make it work to filter records in the LstCategory List Box
along with the other Combo boxes?

Private Sub cmdfilter_Click()
Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"

If Not IsNull(Me.cboEmployees) Then
strWhere = strWhere & _
" [CLECID] IN(SELECT [cLECID]" & _
" FROM tblemployeeAssignments" & _
" WHERE [employeeId] = " & Me.cboEmployees & ") AND "

End If



If Not IsNull(Me.Text58) Then
strWhere = strWhere & "([CLEC Name] Like ""*" & Me.Text58 & "*"") And "

End If

If Not IsNull(Me.LstCategory) Then
strWhere = strWhere & _
" [CLECID] In (Select [CLECID]" & _
" From [CLEC Systems3]" & _
"Where [system id] = " & Me.LstCategory & ") And "
End If


'See if the string has more than 5 characters (a trailng " AND ") to remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If

End Sub
Click to expand...
Click to expand...
 
C

Confused

That fixed it! Incredible! Thank you! Have a nice weekend!

KenSheridan via AccessMonster.com said:
I'd assumed system id was a determinant of CLECID in CLEC Systems3. Maybe
that's not the case? It does help if you can give us some details of the
table definitions with posts like this. If system id is not a determinant of
CLECID in CLEC Systems3 then try this amendment which is closer to the logic
of your original code:

Dim ctrl as Control
Dim strWhere As String
Dim strSystemIDList As String
Dim lngCLECID As Long
Dim varItem As Variant

set ctrl = Me.LstCategory

If Not IsNull(Me.cboEmployees) Then
strWhere = strWhere & _
"And CLECID In(SELECT CLECID" & _
" FROM tblemployeeAssignments" & _
" WHERE [employeeId] = " & Me.cboEmployees & ") "
End If

If Not IsNull(Me.Text58) Then
strWhere = strWhere & "And [CLEC Name] Like ""*" & Me.Text58 & "*"" "
End If

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
' add system ID to value list
strSystemIDList = strSystemIDList & "," & ctrl.ItemData(varItem)
Next varItem

' remove leading comma from value list
strSystemIDList = Mid(strSystemIDList, 2)

strWhere = strWhere & "And CLECID In(SELECT CLECID " & _
"FROM [CLEC Systems3] WHERE [system ID] IN(" & _
strSystemIDList & "))"
End If

' remove leading "And "
strWhere = Mid(strWhere,5)

' set form's Filter property and apply filter
Me.Filter = strWhere
Me.FilterOn = True

I note that you are using Boolean AND operations on the three parameters, so
if more than one is applied all must be met for a row be returned, e.g. if a
pattern for a CLEC Name is entered in the text box, and a set of system IDs
are selected in the list box than a row will only be returned for those
values of CLECID which not only match the pattern but are present in those
rows in CLEC Systems3 containing one of the selected system ID values and
where the CLECID corresponds to one of the CLEC Name values which meet the
pattern entered in the text box. If an employee is also selected in the
first combo box then not only must the forgoing criteria be met, but the
CLECID in a row must also be one of those from the tblemployeeAssignments
table where the employeeID matches the value selected in the combo box. Just
so we can be sure we are not heading up a gum tree, is that the correct
logical premise for the rows to which you want the form to be filtered?

Ken Sheridan
Stafford, England
Hi again,

It now only pulls up one record when I select one of the items in the
listbox. If I select multiple items in the listbox it might pull up two or
three records. Neither pertaining to the entire list. CLECID is a long
integer data type.
A multi-select list box has an ItemsSelected collection, which unlike most
collections, is not a collection of objects but of variants, each being an
Click to expand...
[quoted text clipped - 105 lines]
End Sub
Click to expand...
Click to expand...
Click to expand...
 
C

Confused

Hopefully quick question:

In general how would I add a multiselect LstREgion to the mix, following the
same logic? I wasn't certain about how to reference the LstREgion with the
set ctl and of course with the code.

Table ClecREgions has fields REgionID, CLECID.

KenSheridan via AccessMonster.com said:
I'd assumed system id was a determinant of CLECID in CLEC Systems3. Maybe
that's not the case? It does help if you can give us some details of the
table definitions with posts like this. If system id is not a determinant of
CLECID in CLEC Systems3 then try this amendment which is closer to the logic
of your original code:

Dim ctrl as Control
Dim strWhere As String
Dim strSystemIDList As String
Dim lngCLECID As Long
Dim varItem As Variant

set ctrl = Me.LstCategory

If Not IsNull(Me.cboEmployees) Then
strWhere = strWhere & _
"And CLECID In(SELECT CLECID" & _
" FROM tblemployeeAssignments" & _
" WHERE [employeeId] = " & Me.cboEmployees & ") "
End If

If Not IsNull(Me.Text58) Then
strWhere = strWhere & "And [CLEC Name] Like ""*" & Me.Text58 & "*"" "
End If

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
' add system ID to value list
strSystemIDList = strSystemIDList & "," & ctrl.ItemData(varItem)
Next varItem

' remove leading comma from value list
strSystemIDList = Mid(strSystemIDList, 2)

strWhere = strWhere & "And CLECID In(SELECT CLECID " & _
"FROM [CLEC Systems3] WHERE [system ID] IN(" & _
strSystemIDList & "))"
End If

' remove leading "And "
strWhere = Mid(strWhere,5)

' set form's Filter property and apply filter
Me.Filter = strWhere
Me.FilterOn = True

I note that you are using Boolean AND operations on the three parameters, so
if more than one is applied all must be met for a row be returned, e.g. if a
pattern for a CLEC Name is entered in the text box, and a set of system IDs
are selected in the list box than a row will only be returned for those
values of CLECID which not only match the pattern but are present in those
rows in CLEC Systems3 containing one of the selected system ID values and
where the CLECID corresponds to one of the CLEC Name values which meet the
pattern entered in the text box. If an employee is also selected in the
first combo box then not only must the forgoing criteria be met, but the
CLECID in a row must also be one of those from the tblemployeeAssignments
table where the employeeID matches the value selected in the combo box. Just
so we can be sure we are not heading up a gum tree, is that the correct
logical premise for the rows to which you want the form to be filtered?

Ken Sheridan
Stafford, England
Hi again,

It now only pulls up one record when I select one of the items in the
listbox. If I select multiple items in the listbox it might pull up two or
three records. Neither pertaining to the entire list. CLECID is a long
integer data type.
A multi-select list box has an ItemsSelected collection, which unlike most
collections, is not a collection of objects but of variants, each being an
Click to expand...
[quoted text clipped - 105 lines]
End Sub
Click to expand...
Click to expand...
Click to expand...
 
C

Confused

I added the section as shown below. This is the full code. I added it
exactly like the other one. For some reason I keep getting Syntax error
(missing operator) in query expression RegionID IN (). This section is
identical to the first list box ( I copied and pasted and just changed the
field names). [CLEC ID] in CustomerRegions is CLEC ID with a space. Would
that make a difference? But I used the space in the code...
All of the other boxes work fine unless I select a region.

This is what I have:

Dim ctrl As Control
Dim strWhere As String
Dim strSystemIDList As String
Dim strRegionIDList As String
Dim lngCLECID As Long
Dim varItem As Variant

Set ctrl = Me.LstCategory

If Not IsNull(Me.cboEmployees) Then
strWhere = strWhere & _
"And CLECID In(SELECT CLECID" & _
" FROM tblemployeeAssignments" & _
" WHERE [employeeId] = " & Me.cboEmployees & ") "
End If


If Not IsNull(Me.CboSystems) Then
strWhere = strWhere & _
"And CLECID In(SELECT CLECID" & _
" FROM [CLEC Systems3]" & _
" WHERE [system id] <> " & Me.CboSystems & ") "
End If



If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
' add system ID to value list
strSystemIDList = strSystemIDList & "," & ctrl.ItemData(varItem)
Next varItem

' remove leading comma from value list
strSystemIDList = Mid(strSystemIDList, 2)

strWhere = strWhere & "And CLECID In(SELECT CLECID " & _
"FROM [CLEC Systems3] WHERE [system ID] IN(" & _
strSystemIDList & "))"
End If


Set ctrl = Me.lstREgion
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
'add RegionID to value list
strRegiondIDList = strRegionIDList & "," & ctrl.ItemData(varItem)
Next varItem
'remove leading comma from value list
strRegionIDList = Mid(strRegionIDList, 2)

strWhere = strWhere & "And [CLECID] In(SELECT [CLEC ID] " & _
"FROM [CustomerRegions] WHERE [REgionID] IN(" & _
strRegionIDList & "))"

End If





' remove leading "And "
strWhere = Mid(strWhere, 5)




DoCmd.OpenForm "EmailQuery", acNormal, , strWhere



Me.Filter = strWhere

Me.FilterOn = True

KenSheridan via AccessMonster.com said:
Firstly you'd declare another string variable to hold the value list of
RegionID values. You can then either declare separate object variables for
each control or you can reinitialise the same object variable to the relevant
control within each relevant block of code. Taking the latter route for
instance:

Dim ctrl as Control
Dim strWhere As String
Dim strSystemIDList As String
Dim strRegionIDList As String
Dim lngCLECID As Long
Dim varItem As Variant


If Not IsNull(Me.cboEmployees) Then
strWhere = strWhere & _
"And CLECID In(SELECT CLECID" & _
" FROM tblemployeeAssignments" & _
" WHERE [employeeId] = " & Me.cboEmployees & ") "
End If

If Not IsNull(Me.Text58) Then
strWhere = strWhere & "And [CLEC Name] Like ""*" & Me.Text58 & "*"" "
End If

Set ctrl = Me.LstCategory
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
' add system ID to value list
strSystemIDList = strSystemIDList & "," & ctrl.ItemData(varItem)
Next varItem

' remove leading comma from value list
strSystemIDList = Mid(strSystemIDList, 2)

strWhere = strWhere & "And CLECID In(SELECT CLECID " & _
"FROM [CLEC Systems3] WHERE [system ID] IN(" & _
strSystemIDList & "))"
End If

Set ctrl = Me.LstREgion
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
' add RegionID to value list
strRegionIDList = strRegionIDList & "," & ctrl.ItemData(varItem)
Next varItem

' remove leading comma from value list
strRegionIDList = Mid(strRegionIDList, 2)

strWhere = strWhere & "And CLECID In(SELECT CLECID " & _
"FROM [ClecREgions] WHERE [RegionID] IN(" & _
strRegionIDList & "))"
End If

' remove leading "And "
strWhere = Mid(strWhere,5)

' set form's Filter property and apply filter
Me.Filter = strWhere
Me.FilterOn = True


Ken Sheridan
Stafford, England
Hopefully quick question:

In general how would I add a multiselect LstREgion to the mix, following the
same logic? I wasn't certain about how to reference the LstREgion with the
set ctl and of course with the code.

Table ClecREgions has fields REgionID, CLECID.
I'd assumed system id was a determinant of CLECID in CLEC Systems3. Maybe
that's not the case? It does help if you can give us some details of the
Click to expand...
[quoted text clipped - 71 lines]
End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

type mismatch error 13 1
searching using a combo box 1
ADP list box filter 3
StrWhere with multi search results forms 8
filter by multiple combo boxes 2
Filters not responding 2
Multiple Criteria Search Form 3
Multiselect List Box 4

Top