Listbox limit

[Linton.McCracken]

I have collected data to view object dependencies from my main
switchboard all the way down to the dependent tables. I have created
15 listboxes on a form, each getting its RowSource from a SQL
statement using the next higher level listbox as the "Parent" for the
current "Child" listbox, all of which queries a master 2-column
relationships table which holds all relationships identified
throughout the database. All functions perfectly with the Parent and
the top 6 listbox children. Access is not recognizing my click in the
Level 6 listbox, and thus not executing the SQL to get the children
for the Level 7 listbox.

Is there a limit to the quantity of listboxes on a form or the
quantity of listboxes dependent upon each other? I thought there
might be so I created 15 separate tables, where each set of resulting
children can be stored separately and separately queried instead of
getting Level 7's children by re-executing the SQL statements for
Level 1 - Level 6 children. But that still is not causing any results
in Level 7's listbox.

Any assistance is greatly appreciated.

 

[Douglas J. Steele]

I'm not aware of any limit on the number of list boxes.

Are you sure that the sixth listbox is properly linked to its code? Does the
property for the appropriate event read [Event Procedure]? If you click on
the ellipsis (...) to the right of the property, does it take you into the
correct code?

 

[Klatuu]

In addition to Doug's valid points, beyond the technical, there is another
limit. That is the users' patience. I once wrote a form that had seven
cascading list boxes. It worked great, but it took two minutes to load.
What I did was remove the row source from all the list boxes. I set the
height of each to 0 and added a command button above each list box that set
the row source and set the height to what I wanted. Each list box cascasded
from let to right so that listbox2 was filtered by listbox1, listbox3
filtered on listbox2, etc. Now no matter where the user started in his
selection, the filtering will work its way down.

I also added Reset command button that clears all the row sources and sets
all the heights back to 0.

This make the form open very quickly, and adding only one rowsource at a
time made the form very usable.

Here is the code from the command button for the second list box:

Private Sub cmdMActivity_Click()
Dim strWhere As String

DoCmd.Hourglass True

Call ResetScreen(5)

With Me.lstMActivity
strWhere = FindWhere(5)
If .RowSource = "" Then
.RowSource = "SELECT DISTINCT MActivity " & _
"FROM actual_res_export " & _
"WHERE ProjectID <> 'Billable' And BillCat = 'unbillable' "
& _
strWhere & _
"ORDER BY MActivity;"
End If
If .ListCount = 0 Then
Me.lblMActivity.Caption = "No Matches"
Me.lblMActivity.ForeColor = 255
.Height = 0
Me.cmdActivity.SetFocus
Else
.Height = 3015
.SetFocus
End If
End With
DoCmd.Hourglass False
End Sub
*********************************
Here is the code for FindWhere

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

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

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

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

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

If lngSelector <= 5 Then
If (lngSelector < 5 And strWhere = "") Or lngSelector > 4 Then
strWhere = BuildWhereCondition("lstBillProdOffering")
If Len(strWhere) > 0 Then
strWhere = " AND ProjectID " & _
strWhere
End If
End If
End If

FindWhere = strWhere

End Function
***********************
And the BuildWhereCondition

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

Hopefully, that will give you some ideas.