Listbox hierarchy help

General
1

Hi. I was able to follow the simple example in the docs, but have been unable to get the hierarchy to work for my needs. I have a listbox with 5 columns (WhichTest, Domain, Score, TotalCorrect, and TotalTaken). I’m using the DISTINCT keyword in my sql statement to list only the unique WhichTest data. I’d like to use the hierarchy feature of the listbox to expand each WhichTest to show the TestDate (probably just under the WhichTest), Score, TotalCorrect, and TotalTaken for each test within the unique WhichTest.

I found this post (https://forum.xojo.com/8555-hierarchical-list-box-question), which guided me to my AddFolder and While Not Wend statements below. I think this stores the data appropriately because when I added a ListBox1.AddRow(rsDate.Field(“TestDate”).StringValue) after the RowTag, it lists the right dates in the correct order of the WhichTest. However, I am having trouble with the ExpandRow statement. Any help is appreciated!

[code] Dim sql As String
sql = “SELECT DISTINCT WhichTest FROM emailresults WHERE Email LIKE '%” + lbxStudents.Cell(lbxStudents.ListIndex, 1) + “%’ ORDER BY Domain”

Dim data As RecordSet
data = mdb.SQLSelect(sql)

Do Until data.EOF
  dim sqlDate as String
  sqlDate = "SELECT TestDate FROM emailresults WHERE WhichTest LIKE '%" + data.Field("WhichTest").StringValue + "%' AND Email LIKE '%" + lbxStudents.Cell(lbxStudents.ListIndex, 1) + "%'"
  dim rsDate as RecordSet
  rsDate = mdb.SQLSelect(sqlDate)
  
  lbxDetail.AddFolder data.Field("WhichTest").StringValue
  while not rsDate.EOF
    lbxDetail.RowTag(lbxDetail.ListCount - 1) = rsDate.Field("TestDate").StringValue
    rsDate.MoveNext
  Wend
  rsDate.Close
  
  data.MoveNext
Loop[/code]
2

Move your rsDate lookup/loop into the ExpandRow event. When the user expands a row (or when you do it in code) you grab the WhichTest from the listbox row being expanded and look up the individual tests for that WhichTest value. Add them to the listbox and they will appear “beneath” the row being expanded.

3

Thanks Tim. Are you suggesting to move everything I have above to the ExpandRow event and delete it from the current method? I’m trying that but am getting a NilObject on the Do Until data.EOF. Any suggestions?

4

No. You’ve got two recordsets and two loops going. Separate them out by moving the inner loop to the ExpandRow event.

In your current code, select DISTINCT WhichTest and AddFolder them to the listbox.

In ExpandRow, select the test where whichtest like ‘the value from the current row’.

5

You should end up with something like this.

Populate Listbox Method (add folders to listbox)

[code]Dim sql As String
sql = “SELECT DISTINCT WhichTest FROM emailresults WHERE Email LIKE '%” + lbxStudents.Cell(lbxStudents.ListIndex, 1) + “%’ ORDER BY Domain”

Dim data As RecordSet
data = mdb.SQLSelect(sql)

Do Until data.EOF
  lbxDetail.AddFolder data.Field("WhichTest").StringValue
  data.MoveNext
Loop[/code]

Expand row (get test dates for selected row)

[code] dim sqlDate as String
sqlDate = “SELECT TestDate FROM emailresults WHERE WhichTest LIKE '%” +lbxDetail.cell(lbxDetail.ListIndex, 0) + “%’ AND Email LIKE '%” + lbxStudents.Cell(lbxStudents.ListIndex, 1) + “%’”
dim rsDate as RecordSet
rsDate = mdb.SQLSelect(sqlDate)

   while not rsDate.EOF
    lbxDetail.AddRow rsDate.Field("TestDate").StringValue

lbxDetail.RowTag(lbxDetail.LastIndex) = lbxDetail.Cell(lbxDetail.ListIndex, 0) // Save WhichTest in the rowtag
rsDate.MoveNext
Wend
[/code]

6

Great! Moving along! Ok, so I got the TestDate to show when I expand the node, but this only happens when there’s one record in the listbox. I have another sample data where the student has 5 total tests, one result in each except one which has two. None of these are expanding to show the dates

7

Hmm, ok. So for the others with multiple tests, it is loading properly, but I only discovered this when I double-click the row. And if I double-click in one of the rows, then expand the node in another one, it seems to copy all the data from the first one I double-clicked.

I have in my DoubleClick event below, which was taken from the docs example

Me.expanded(Me.listindex)=Not Me.expanded(Me.listindex)
8

What code do you have in the ExpandRow event? That’s where the problem will be.

9

It’s the code Wayne posted. I added the Score, TotalCorrect, and OutOf fields

[code] dim sqlDate as String
sqlDate = “SELECT TestDate, Score, TotalCorrect, OutOf FROM emailresults WHERE WhichTest LIKE '%” +lbxDetail.cell(lbxDetail.ListIndex, 0) + “%’ AND Email LIKE '%” + lbxStudents.Cell(lbxStudents.ListIndex, 1) + “%’”
dim rsDate as RecordSet
rsDate = mdb.SQLSelect(sqlDate)

while not rsDate.EOF
lbxDetail.AddRow rsDate.Field(“TestDate”).StringValue
lbxDetail.Cell(lbxDetail.LastIndex, 3) = rsDate.Field(“Score”).StringValue
lbxDetail.Cell(lbxDetail.LastIndex, 4) = rsDate.Field(“TotalCorrect”).StringValue
lbxDetail.Cell(lbxDetail.LastIndex, 5) = rsDate.Field(“OutOf”).StringValue
lbxDetail.RowTag(lbxDetail.LastIndex) = lbxDetail.Cell(lbxDetail.ListIndex, 0) // Save WhichTest in the rowtag
rsDate.MoveNext
Wend[/code]

10

I wouldn’t use ListIndex in ExpandRow. You’re passed the row being expanded. Use that.

11

Hi Tim. Are you saying to take out this line from the While Not Wend?

lbxDetail.RowTag(lbxDetail.LastIndex) = lbxDetail.Cell(lbxDetail.ListIndex, 0)

I did but still expands from the node when only one test is present, but still need to double-click the row to expand when more than one test present

12

Change lbxDetail.ListIndex to Row, both in that line and in your sql statement.

13

Awesome Tim! Thanks so much! Works like a charm now