Suggestions on Proper Technique, Please

[Guest]

I have an interesting problem. I will start with the simple part, and save
the scary part for later. First, I am looking for critiques on my
methodology.

Problem:
The use must be able to select "All" or one or more vaules from 5 list boxes
to filter data for some reports. The reports are to include only rows where
all the values match.

My Solution:
The List Boxes are set to Extended MultiSelect.
The List Box Row Sources are SQL that return unique values and "(All") using
a union select.
The queries for the report use the function below to determine whether a
value should be included. They pass the field value and the name of the List
Box control to the function. The function determines whether the value
passed is in the ItemsSelected collection. If it is, it passes the value
back to the query, if it is not, it passes back Null.

This is the best approach I could come up with. If there is a better way or
my method needs some tweaking, please let me know.

Now the Scary part (I knew you were waiting for it). As I previously
stated, there are 5 list boxes. Populating them on the first round is no
problem. The problem is that the data has some interdependancies. For
example:
All rows have a Master Activity Number.
All rows have an Activity Number.
Master Activity to Activity is One to Many.
The other 3 fields have no relationships, but each row may have a value in 0
to 3 of the fields.

If a user selects a Master Activity, then selects an Activity that is not
related to that Master Activity, no rows will be returned. So far, not a
problem. When a Master Activity is chosen, I change the row source of the
Activity list box so that only Activityies related to the Master are included
and requery.

Now the hard part. To avoid allowing the user to select mulually exclusive
values, I want to requery after each selection so that only valid values are
presented for selection. For example. If a user first selects a Bill
Network, then selects a Master Activity, then decides to select a home room.
I am getting lost in which lists to requery, because each requery looses the
previous selections.

Public Function IsInList(varValue As Variant, strControl As String) As Variant
'D Hargis 11/05
'Used by frmBPO to determine if a value is selected in a list box
'for BPO Reports
'VarValue - The field value to evaluate
'strControl - The name of the list box to search

Dim varItm As Variant 'Index to selected items
Dim ctl As Control 'List Box control object reference

Set ctl = Forms!frmbpo.Controls(strControl)
For Each varItm In ctl.ItemsSelected
If ctl.ItemData(varItm) = "(All)" Then
IsInList = varValue
Set ctl = Nothing
Exit Function
End If
If ctl.ItemData(varItm) = varValue Then
IsInList = varValue
Set ctl = Nothing
Exit Function
End If
Next varItm
Set ctl = Nothing
IsInList = ""
End Function


HAVING (((tblMasterActivity.MActivity)=IsInList([Mactivity],"lstMActivity"))
AND ((tblBudgetVSActualLbrPO.activity)=IsInList([Activity],"lstActivity"))
AND
((tblActivity.actvContractActivity)=IsInList([actvContractActivity],"lstBillNetwork"))
AND ((tblBudgetVSActualLbrPO.acctgunit)=IsInList([acctgunit],"lstHomeRoom"))

 

[Douglas J Steele]

Personal preference, I guess, but I never put All in a multiselect listbox.

Instead, I put 2 buttons either beside or under the listbox: one that lets
them select all records in the listbox, and one that lets them unselect them
all.

Yes, it can make for "messier" queries, but it's far simpler from a
maintenance perspective.

 

[Guest]

Thanks for your response, Douglas. I did consider that. In fact, I do have
a "Clear" button that unselects all 5 list boxes. Would you suggest a loop
to make each item selected like:

Set ctl = Me.lstMActivity
For Each varItm In ctl.ItemsSelected
ctl.Selected(varItm) = True
Next varItm

I don't think it would make any difference to the queries based on the way I
am doing them. The difference I see, and why I chose to use "All" was for
example, there are about 1400 Master Activities. If I use "All", the sub
that feeds the query only has to see the all and bypasses the loop;
otherwise, I would have to loop up to 1400 times for eacy of about 46,000
records. It just seemed like less processing to use the "All"

 

[Douglas J Steele]

No, the ItemsSelected collection contains those that are already selected,
so what you have is redundant: it sets the Selected property to True for all
selected controls.

What I typically do is have a generic routine to toggle them on or off:

Sub ListBoxSelectAll( _
WhatLB As ListBox, _
Optional Status As Boolean = True _
)
Dim lngItem As Long

For lngItem = 0 To (WhatLB.ListCount - 1)
WhatLB.Selected(lngItem) = Status
Next lngItem

End Sub

Then on the Click event of the button, I'll have a single line:

Call ListBoxSelectAll(Me.List0)

to select them all, or

Call ListBoxSelectAll(Me.List0, False)

to unselect them all (yeah, it would be more efficient to clear using the
ItemsSelected collection, but that would mean two routines, when one would
do...)

1400 is a lot of items in a single listbox...

I just reread your entire post, and I guess I wouldn't use something like
IsInList function.

If I were to use such a function, I'd have it return True or False, and use

HAVING IsInList([Mactivity],"lstMActivity")
AND IsInList([Activity],"lstActivity")
AND IsInList([actvContractActivity],"lstBillNetwork")
AND IsInList([acctgunit],"lstHomeRoom")

However, I'd be more inclined to use a technique such as in
http://www.mvps.org/access/forms/frm0007.htm at "The Access Web" to
construct a WHERE clause that contains the appropriate information. (FWIW, I
don't use the exact technique shown in that reference: it would generate
EmpNm = 'Tom' OR EmpNm = 'Dick' OR EmpNm = 'Harry', whereas I generate EmpNm
IN ('Tom', 'Dick', 'Harry')) In this way, you don't have to evaluate 4
functions for each row in the underlying table.

 

[Guest]

yep, I see my mistake. I copied the code from my clear button without
thinking about it first. I see your point.

No, the ItemsSelected collection contains those that are already selected,
so what you have is redundant: it sets the Selected property to True for all
selected controls.

yep, I see my mistake. I copied the code from my clear button without
thinking about it first. I see your point.

What I typically do is have a generic routine to toggle them on or off:

Sub ListBoxSelectAll( _
WhatLB As ListBox, _
Optional Status As Boolean = True _
)
Dim lngItem As Long

For lngItem = 0 To (WhatLB.ListCount - 1)
WhatLB.Selected(lngItem) = Status
Next lngItem

End Sub

Then on the Click event of the button, I'll have a single line:

Call ListBoxSelectAll(Me.List0)

to select them all, or

Call ListBoxSelectAll(Me.List0, False)

to unselect them all (yeah, it would be more efficient to clear using the
ItemsSelected collection, but that would mean two routines, when one would
do...)

I like the idea. Less code is always better

1400 is a lot of items in a single listbox...

Yes, but the user insists on being able to select 1 to all for reporting
purposes.

I just reread your entire post, and I guess I wouldn't use something like
IsInList function.

If I were to use such a function, I'd have it return True or False, and use

HAVING IsInList([Mactivity],"lstMActivity")
AND IsInList([Activity],"lstActivity")
AND IsInList([actvContractActivity],"lstBillNetwork")
AND IsInList([acctgunit],"lstHomeRoom")

That was my first thought, but I did not do it correctly and could not get
any records.

However, I'd be more inclined to use a technique such as in
http://www.mvps.org/access/forms/frm0007.htm at "The Access Web" to
construct a WHERE clause that contains the appropriate information. (FWIW, I
don't use the exact technique shown in that reference: it would generate
EmpNm = 'Tom' OR EmpNm = 'Dick' OR EmpNm = 'Harry', whereas I generate EmpNm
IN ('Tom', 'Dick', 'Harry')) In this way, you don't have to evaluate 4
functions for each row in the underlying table.

It will take some time to fully digest this. I am sure it is better than my
IsInList function.

I really appreciate your taking the time to evaluate my concept.

 

[Douglas J Steele]

Feel free to post back if you run into any problems.

 

[Guest]

Okay, with your kind assistance and a rather heated exchange with my user, I
managed to simplify this considerably. It dawned on me that the query will
be the same for all versions of the report. The only variation is the data
filtering.
So, said I to myself, "Doh! Do this in the OpenReport method". Since I was
able to get the user to agree that logically, all the other lists to select
from should be based on the primary selection for the report. It will always
be Product Offering.
Below is what I put together based on the link you provided and your opinion
for that purpose. As to the other filtering, I will add it later, but I
wanted to do some testing and tweaking first:

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

Set ctl = Me.lstBillProdOffering

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "tblBudgetVSActualLbrPO.BillableProductOffering = '"
& _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = "tblBudgetVSActualLbrPO.BillableProductOffering 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

Thanks again, Douglas.