Multiple MultiSelect ListBoxes = Migraine

G

Guest

I have 1 ListBox and 5 MultiSelect ListBoxes on an unbound form. I have
written the code to generate the WHERE statement for a DoCmd.OpenForm
command. The problem is that the data being pulled seems to build on each
subsequent part of the where statement instead of filtering each field with
the values from its MSLB.

Private Sub Okay_Click()
Dim MySel As String, frm As Form, ctl As Control, varItem As Variant
Set frm = Forms!frmMain
*****Starting off setting the value for the ListBox*********
MySel = "[tblDetail.COMPANY] = '" & Me.COMPANY & "'"
*****Start in on the MultiSelect ListBoxes**************
Set ctl = frm!Site
b = ctl.ItemsSelected.Count
If b > 0 Then
MySel = MySel & " AND [tblDetail.Site] = '"
For Each varItem In ctl.ItemsSelected
MySel = MySel & ctl.ItemData(varItem) & "' OR [tblDetail.Site]= '"
Next varItem
MySel = Left(MySel, Len(MySel) - 23)
End If
b = 0
Set ctl = frm!Vendor
b = ctl.ItemsSelected.Count
If b > 0 Then
MySel = MySel & " AND [tblDetail.Vendor] = '"
For Each varItem In ctl.ItemsSelected
MySel = MySel & ctl.ItemData(varItem) & "' OR
[tblDetail.Vendor]= '"
Next varItem
MySel = Left(MySel, Len(MySel) - 25)
End If
b = 0
Set ctl = frm!Customer
b = ctl.ItemsSelected.Count
If b > 0 Then
MySel = MySel & " AND [tblDetail.Custkey] = '"
For Each varItem In ctl.ItemsSelected
MySel = MySel & ctl.ItemData(varItem) & "' OR
[tblDetail.Custkey]= '"
Next varItem
MySel = Left(MySel, Len(MySel) - 27)
End If
b = 0
Set ctl = frm!IREF
b = ctl.ItemsSelected.Count
If b > 0 Then
MySel = MySel & " AND [tblDetail.partnbr] = '"
For Each varItem In ctl.ItemsSelected
MySel = MySel & ctl.ItemData(varItem) & "' OR
[tblDetail.partnbr]= '"
Next varItem
MySel = Left(MySel, Len(MySel) - 27)
End If
b = 0
Debug.Print MySel
DoCmd.OpenForm "frmDetail", acFormDS, , MySel, acFormEdit, acWindowNormal
End Sub

When I select a couple Sites and a couple Vendors I generate the statement:

[tblDetail.COMPANY] = 'CB' AND [tblDetail.Site] = '01' OR [tblDetail.Site]=
'12' AND [tblDetail.Vendor] = '0105' OR [tblDetail.Vendor] = '0052' OR
[tblDetail.Vendor]= '0061'

What I end-up getting with my

DoCmd.OpenForm "frmDetail", acFormDS, , MySel, acFormEdit, acWindowNormal

statement is all records for Company CB - Site 01, and only records in the
selected Vendors for Company CB - Site 12

I know it all those ANDs and ORs in there... but I have no idea how to get
what I want.... which is all the records I would get if each field was subset
sequentially.
Can anyone help? Is it even possible to do this in the manner I am
attempting? Is there a better way?

Best regards, patrick
 
G

Guest

I didn't look at this too long but does the initial MySel need to be cleared?
MySel = "[tblDetail.COMPANY] = '" & Me.COMPANY & "'"
*****Start in on the MultiSelect ListBoxes**************
Set ctl = frm!Site
b = ctl.ItemsSelected.Count
Click to expand...

MYSEL = ""
If b > 0 Then
MySel = MySel & " AND [tblDetail.Site] = '"
Click to expand...


Patrick said:
I have 1 ListBox and 5 MultiSelect ListBoxes on an unbound form. I have
written the code to generate the WHERE statement for a DoCmd.OpenForm
command. The problem is that the data being pulled seems to build on each
subsequent part of the where statement instead of filtering each field with
the values from its MSLB.

Private Sub Okay_Click()
Dim MySel As String, frm As Form, ctl As Control, varItem As Variant
Set frm = Forms!frmMain
*****Starting off setting the value for the ListBox*********
MySel = "[tblDetail.COMPANY] = '" & Me.COMPANY & "'"
*****Start in on the MultiSelect ListBoxes**************
Set ctl = frm!Site
b = ctl.ItemsSelected.Count
If b > 0 Then
MySel = MySel & " AND [tblDetail.Site] = '"
For Each varItem In ctl.ItemsSelected
MySel = MySel & ctl.ItemData(varItem) & "' OR [tblDetail.Site]= '"
Next varItem
MySel = Left(MySel, Len(MySel) - 23)
End If
b = 0
Set ctl = frm!Vendor
b = ctl.ItemsSelected.Count
If b > 0 Then
MySel = MySel & " AND [tblDetail.Vendor] = '"
For Each varItem In ctl.ItemsSelected
MySel = MySel & ctl.ItemData(varItem) & "' OR
[tblDetail.Vendor]= '"
Next varItem
MySel = Left(MySel, Len(MySel) - 25)
End If
b = 0
Set ctl = frm!Customer
b = ctl.ItemsSelected.Count
If b > 0 Then
MySel = MySel & " AND [tblDetail.Custkey] = '"
For Each varItem In ctl.ItemsSelected
MySel = MySel & ctl.ItemData(varItem) & "' OR
[tblDetail.Custkey]= '"
Next varItem
MySel = Left(MySel, Len(MySel) - 27)
End If
b = 0
Set ctl = frm!IREF
b = ctl.ItemsSelected.Count
If b > 0 Then
MySel = MySel & " AND [tblDetail.partnbr] = '"
For Each varItem In ctl.ItemsSelected
MySel = MySel & ctl.ItemData(varItem) & "' OR
[tblDetail.partnbr]= '"
Next varItem
MySel = Left(MySel, Len(MySel) - 27)
End If
b = 0
Debug.Print MySel
DoCmd.OpenForm "frmDetail", acFormDS, , MySel, acFormEdit, acWindowNormal
End Sub

When I select a couple Sites and a couple Vendors I generate the statement:

[tblDetail.COMPANY] = 'CB' AND [tblDetail.Site] = '01' OR [tblDetail.Site]=
'12' AND [tblDetail.Vendor] = '0105' OR [tblDetail.Vendor] = '0052' OR
[tblDetail.Vendor]= '0061'

What I end-up getting with my

DoCmd.OpenForm "frmDetail", acFormDS, , MySel, acFormEdit, acWindowNormal

statement is all records for Company CB - Site 01, and only records in the
selected Vendors for Company CB - Site 12

I know it all those ANDs and ORs in there... but I have no idea how to get
what I want.... which is all the records I would get if each field was subset
sequentially.
Can anyone help? Is it even possible to do this in the manner I am
attempting? Is there a better way?

Best regards, patrick
Click to expand...
 
G

Guest

I am trying to create one big where statement consisting of all the
selections made by the user... so, no, I do not think it should be cleared.
The problem is that each additional AND and OR is further refining the subset
of the prior condition.

Access101 said:
I didn't look at this too long but does the initial MySel need to be cleared?
MySel = "[tblDetail.COMPANY] = '" & Me.COMPANY & "'"
*****Start in on the MultiSelect ListBoxes**************
Set ctl = frm!Site
b = ctl.ItemsSelected.Count
Click to expand...

MYSEL = ""
If b > 0 Then
MySel = MySel & " AND [tblDetail.Site] = '"
Click to expand...


Patrick said:
I have 1 ListBox and 5 MultiSelect ListBoxes on an unbound form. I have
written the code to generate the WHERE statement for a DoCmd.OpenForm
command. The problem is that the data being pulled seems to build on each
subsequent part of the where statement instead of filtering each field with
the values from its MSLB.

Private Sub Okay_Click()
Dim MySel As String, frm As Form, ctl As Control, varItem As Variant
Set frm = Forms!frmMain
*****Starting off setting the value for the ListBox*********
MySel = "[tblDetail.COMPANY] = '" & Me.COMPANY & "'"
*****Start in on the MultiSelect ListBoxes**************
Set ctl = frm!Site
b = ctl.ItemsSelected.Count
If b > 0 Then
MySel = MySel & " AND [tblDetail.Site] = '"
For Each varItem In ctl.ItemsSelected
MySel = MySel & ctl.ItemData(varItem) & "' OR [tblDetail.Site]= '"
Next varItem
MySel = Left(MySel, Len(MySel) - 23)
End If
b = 0
Set ctl = frm!Vendor
b = ctl.ItemsSelected.Count
If b > 0 Then
MySel = MySel & " AND [tblDetail.Vendor] = '"
For Each varItem In ctl.ItemsSelected
MySel = MySel & ctl.ItemData(varItem) & "' OR
[tblDetail.Vendor]= '"
Next varItem
MySel = Left(MySel, Len(MySel) - 25)
End If
b = 0
Set ctl = frm!Customer
b = ctl.ItemsSelected.Count
If b > 0 Then
MySel = MySel & " AND [tblDetail.Custkey] = '"
For Each varItem In ctl.ItemsSelected
MySel = MySel & ctl.ItemData(varItem) & "' OR
[tblDetail.Custkey]= '"
Next varItem
MySel = Left(MySel, Len(MySel) - 27)
End If
b = 0
Set ctl = frm!IREF
b = ctl.ItemsSelected.Count
If b > 0 Then
MySel = MySel & " AND [tblDetail.partnbr] = '"
For Each varItem In ctl.ItemsSelected
MySel = MySel & ctl.ItemData(varItem) & "' OR
[tblDetail.partnbr]= '"
Next varItem
MySel = Left(MySel, Len(MySel) - 27)
End If
b = 0
Debug.Print MySel
DoCmd.OpenForm "frmDetail", acFormDS, , MySel, acFormEdit, acWindowNormal
End Sub

When I select a couple Sites and a couple Vendors I generate the statement:

[tblDetail.COMPANY] = 'CB' AND [tblDetail.Site] = '01' OR [tblDetail.Site]=
'12' AND [tblDetail.Vendor] = '0105' OR [tblDetail.Vendor] = '0052' OR
[tblDetail.Vendor]= '0061'

What I end-up getting with my

DoCmd.OpenForm "frmDetail", acFormDS, , MySel, acFormEdit, acWindowNormal

statement is all records for Company CB - Site 01, and only records in the
selected Vendors for Company CB - Site 12

I know it all those ANDs and ORs in there... but I have no idea how to get
what I want.... which is all the records I would get if each field was subset
sequentially.
Can anyone help? Is it even possible to do this in the manner I am
attempting? Is there a better way?

Best regards, patrick
Click to expand...
Click to expand...
 
G

Guest

This aint easy. I have a form that filters for a report using 7 cascading
multi select list boxes. Here are the two procedures that do the guts of the
work. You will see a variable named lngSelector. That is passed from the
list box so the code knows which list box to work with. I know it is not
exactly what you are doing, but hopefully it will give you some ideas:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function
Private Function FindWhere(lngSelector As Long) As String
Dim strWhere As String
Dim strWhereNext As String

strWhere = Choose(Me.opgSubTask, "", "RRNR = 'RR'", "RRNR = 'NR'")

If lngSelector = 1 Then
strWhereNext = BuildWhereCondition("lstPool")
If Len(strWhereNext) > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "Pool2 " & strWhereNext
End If
End If

If lngSelector <= 2 Then
strWhereNext = BuildWhereCondition("lstBillNetwork")
If Len(strWhereNext) > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "BillNetwork " & strWhereNext
End If
End If

If lngSelector <= 3 Then
strWhereNext = BuildWhereCondition("lstActivity")
If Len(strWhereNext) > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "Activity " & strWhereNext
End If
End If

If lngSelector <= 4 Then
strWhereNext = BuildWhereCondition("lstMActivity")
If Len(strWhereNext) > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "MActivity " & strWhereNext
End If
End If

If lngSelector <= 5 Then
strWhereNext = BuildWhereCondition("lstITM")
If Len(strWhereNext) > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "ITM " & strWhereNext
End If
End If

If lngSelector <= 6 Then
strWhereNext = BuildWhereCondition("lstBillProdOffering")
If Len(strWhereNext) > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "ProjectID " & strWhereNext
End If
End If

FindWhere = strWhere

End Function


Patrick said:
I have 1 ListBox and 5 MultiSelect ListBoxes on an unbound form. I have
written the code to generate the WHERE statement for a DoCmd.OpenForm
command. The problem is that the data being pulled seems to build on each
subsequent part of the where statement instead of filtering each field with
the values from its MSLB.

Private Sub Okay_Click()
Dim MySel As String, frm As Form, ctl As Control, varItem As Variant
Set frm = Forms!frmMain
*****Starting off setting the value for the ListBox*********
MySel = "[tblDetail.COMPANY] = '" & Me.COMPANY & "'"
*****Start in on the MultiSelect ListBoxes**************
Set ctl = frm!Site
b = ctl.ItemsSelected.Count
If b > 0 Then
MySel = MySel & " AND [tblDetail.Site] = '"
For Each varItem In ctl.ItemsSelected
MySel = MySel & ctl.ItemData(varItem) & "' OR [tblDetail.Site]= '"
Next varItem
MySel = Left(MySel, Len(MySel) - 23)
End If
b = 0
Set ctl = frm!Vendor
b = ctl.ItemsSelected.Count
If b > 0 Then
MySel = MySel & " AND [tblDetail.Vendor] = '"
For Each varItem In ctl.ItemsSelected
MySel = MySel & ctl.ItemData(varItem) & "' OR
[tblDetail.Vendor]= '"
Next varItem
MySel = Left(MySel, Len(MySel) - 25)
End If
b = 0
Set ctl = frm!Customer
b = ctl.ItemsSelected.Count
If b > 0 Then
MySel = MySel & " AND [tblDetail.Custkey] = '"
For Each varItem In ctl.ItemsSelected
MySel = MySel & ctl.ItemData(varItem) & "' OR
[tblDetail.Custkey]= '"
Next varItem
MySel = Left(MySel, Len(MySel) - 27)
End If
b = 0
Set ctl = frm!IREF
b = ctl.ItemsSelected.Count
If b > 0 Then
MySel = MySel & " AND [tblDetail.partnbr] = '"
For Each varItem In ctl.ItemsSelected
MySel = MySel & ctl.ItemData(varItem) & "' OR
[tblDetail.partnbr]= '"
Next varItem
MySel = Left(MySel, Len(MySel) - 27)
End If
b = 0
Debug.Print MySel
DoCmd.OpenForm "frmDetail", acFormDS, , MySel, acFormEdit, acWindowNormal
End Sub

When I select a couple Sites and a couple Vendors I generate the statement:

[tblDetail.COMPANY] = 'CB' AND [tblDetail.Site] = '01' OR [tblDetail.Site]=
'12' AND [tblDetail.Vendor] = '0105' OR [tblDetail.Vendor] = '0052' OR
[tblDetail.Vendor]= '0061'

What I end-up getting with my

DoCmd.OpenForm "frmDetail", acFormDS, , MySel, acFormEdit, acWindowNormal

statement is all records for Company CB - Site 01, and only records in the
selected Vendors for Company CB - Site 12

I know it all those ANDs and ORs in there... but I have no idea how to get
what I want.... which is all the records I would get if each field was subset
sequentially.
Can anyone help? Is it even possible to do this in the manner I am
attempting? Is there a better way?

Best regards, patrick
Click to expand...
 
G

Guest

I get it!!! I did not know you could is IN('').... The real trick is making
sure you are properly formatting the statement depending on the number of
selections that were made in each ListBox. I don't know exactly what your
code is doing without seeing the app... but, as you hoped, I think I have
enough to go on.
Thanks, Klatuu!!

Klatuu said:
This aint easy. I have a form that filters for a report using 7 cascading
multi select list boxes. Here are the two procedures that do the guts of the
work. You will see a variable named lngSelector. That is passed from the
list box so the code knows which list box to work with. I know it is not
exactly what you are doing, but hopefully it will give you some ideas:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function
Private Function FindWhere(lngSelector As Long) As String
Dim strWhere As String
Dim strWhereNext As String

strWhere = Choose(Me.opgSubTask, "", "RRNR = 'RR'", "RRNR = 'NR'")

If lngSelector = 1 Then
strWhereNext = BuildWhereCondition("lstPool")
If Len(strWhereNext) > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "Pool2 " & strWhereNext
End If
End If

If lngSelector <= 2 Then
strWhereNext = BuildWhereCondition("lstBillNetwork")
If Len(strWhereNext) > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "BillNetwork " & strWhereNext
End If
End If

If lngSelector <= 3 Then
strWhereNext = BuildWhereCondition("lstActivity")
If Len(strWhereNext) > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "Activity " & strWhereNext
End If
End If

If lngSelector <= 4 Then
strWhereNext = BuildWhereCondition("lstMActivity")
If Len(strWhereNext) > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "MActivity " & strWhereNext
End If
End If

If lngSelector <= 5 Then
strWhereNext = BuildWhereCondition("lstITM")
If Len(strWhereNext) > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "ITM " & strWhereNext
End If
End If

If lngSelector <= 6 Then
strWhereNext = BuildWhereCondition("lstBillProdOffering")
If Len(strWhereNext) > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "ProjectID " & strWhereNext
End If
End If

FindWhere = strWhere

End Function


Patrick said:
I have 1 ListBox and 5 MultiSelect ListBoxes on an unbound form. I have
written the code to generate the WHERE statement for a DoCmd.OpenForm
command. The problem is that the data being pulled seems to build on each
subsequent part of the where statement instead of filtering each field with
the values from its MSLB.

Private Sub Okay_Click()
Dim MySel As String, frm As Form, ctl As Control, varItem As Variant
Set frm = Forms!frmMain
*****Starting off setting the value for the ListBox*********
MySel = "[tblDetail.COMPANY] = '" & Me.COMPANY & "'"
*****Start in on the MultiSelect ListBoxes**************
Set ctl = frm!Site
b = ctl.ItemsSelected.Count
If b > 0 Then
MySel = MySel & " AND [tblDetail.Site] = '"
For Each varItem In ctl.ItemsSelected
MySel = MySel & ctl.ItemData(varItem) & "' OR [tblDetail.Site]= '"
Next varItem
MySel = Left(MySel, Len(MySel) - 23)
End If
b = 0
Set ctl = frm!Vendor
b = ctl.ItemsSelected.Count
If b > 0 Then
MySel = MySel & " AND [tblDetail.Vendor] = '"
For Each varItem In ctl.ItemsSelected
MySel = MySel & ctl.ItemData(varItem) & "' OR
[tblDetail.Vendor]= '"
Next varItem
MySel = Left(MySel, Len(MySel) - 25)
End If
b = 0
Set ctl = frm!Customer
b = ctl.ItemsSelected.Count
If b > 0 Then
MySel = MySel & " AND [tblDetail.Custkey] = '"
For Each varItem In ctl.ItemsSelected
MySel = MySel & ctl.ItemData(varItem) & "' OR
[tblDetail.Custkey]= '"
Next varItem
MySel = Left(MySel, Len(MySel) - 27)
End If
b = 0
Set ctl = frm!IREF
b = ctl.ItemsSelected.Count
If b > 0 Then
MySel = MySel & " AND [tblDetail.partnbr] = '"
For Each varItem In ctl.ItemsSelected
MySel = MySel & ctl.ItemData(varItem) & "' OR
[tblDetail.partnbr]= '"
Next varItem
MySel = Left(MySel, Len(MySel) - 27)
End If
b = 0
Debug.Print MySel
DoCmd.OpenForm "frmDetail", acFormDS, , MySel, acFormEdit, acWindowNormal
End Sub

When I select a couple Sites and a couple Vendors I generate the statement:

[tblDetail.COMPANY] = 'CB' AND [tblDetail.Site] = '01' OR [tblDetail.Site]=
'12' AND [tblDetail.Vendor] = '0105' OR [tblDetail.Vendor] = '0052' OR
[tblDetail.Vendor]= '0061'

What I end-up getting with my

DoCmd.OpenForm "frmDetail", acFormDS, , MySel, acFormEdit, acWindowNormal

statement is all records for Company CB - Site 01, and only records in the
selected Vendors for Company CB - Site 12

I know it all those ANDs and ORs in there... but I have no idea how to get
what I want.... which is all the records I would get if each field was subset
sequentially.
Can anyone help? Is it even possible to do this in the manner I am
attempting? Is there a better way?

Best regards, patrick
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

Error in multiple select in a list box code 4
More problems with Multiselect List Boxes 10
Multi-Select List Box 2
Run select query from VBA code where criteria comes from a multi select ListBox 2
Multiple selection list box 9
Multi-Select List Box Code Problem 5
My Selected print in the list box does not work 4
Multi-select list box as query parameter 15

Top