Enumerate ALL List Box items

[XLEAccessGuru]

I've seen plenty of posts that tell me how to enumerate selected list box items, but none that advise on enumerating the bound column for ALL items in a list box. Access VBA help says "You can use the ListCount property to determine the number of rows in the list box, and then use the ItemData property to return the data for the bound column in each row."

Based on that, I started with the code below, but so far, 'stCriteria' is only returning selected items in the list box. What I need to do, as you can see below, is update the Status field in all records in a separate table that match ALL of the (invisible column) Profile ID numbers from the List Box to "Canceled".

I thought that the 'ItemsSelected' property was what enumerates the selected items in a list box, not 'ItemData', but it seems like ItemData is only returning selected items as well.

What am I not getting here?

Private Sub cmdCancel_Click()
On Error GoTo ErrHandle

Dim stSQL As String
Dim db As DAO.Database
Dim stCriteria As String
Dim x As Integer, y As Integer

Set db = Application.CurrentDb

If lstErrors.ListCount > 1 Then
x = Me.lstErrors.ListCount
y = 0

    'build criteria string containing ProfileID numbers
    For y = 0 To x
        If Len(stCriteria) = 0 Then
            stCriteria = Me.lstErrors.ItemData(y)
        Else
            stCriteria = stCriteria & ", " & Me.lstErrors.ItemData(y)
        End If
    y = y + 1
    Next y
    Debug.Print stCriteria
    
    'build SQL string
    stSQL = "UPDATE tblValidation SET tblValidation.Status = 'Canceled' " _
        & "WHERE (((tblValidation.ProfileRecordID) In (" & stCriteria & ")) AND " _
        & "((tblValidation.Pass)=" & iPass & "));"

    db.Execute (stSQL) 'execute sql string (updates) all validation records in current pass to Canceled
    
    iPass = iPass + 1 'used to update 'Pass' field in validation table to count # times validation
                      'is run for a single dataset
End If

DoCmd.Close acForm, "frmValidationReport"
Set db = Nothing

Exit_ErrHandle:
    Exit Sub
    
ErrHandle:
    MsgBox Err.Description, vbCritical, "Error: " & Err.Number
    Resume Exit_ErrHandle
End Sub

 

[pbaldy]

Try without this:

y = y + 1

and change this:

For y = 0 To x - 1

 

[XLEAccessGuru]

Good stuff Baldy. Thank you.

So help me understand... i often have trouble with counters. Why am I subtracting 1 rather than adding 1 to y? If I want it to find the number of items in the box first, then start from 0 and add each item to the criteria string, why am I subtracting 1 instead of adding if 'y' is initialized to 0?

I kinda get lost on these things.

 

[Moniker]

A for/next loop is an incrementer, as it were. Therefore, in this statement:

For x = 1 to 5
Debug.Print x
Next

The immediate window (press Ctrl-G in the code window to open this) will show you this:

1
2
3
4
5

What's happening here is that X takes the initial value and it keeps that value until the "Next" is found. The value for X is then incremented until the upper limit (in this case 5) is exceeded. Therefore, in my code example, X = 1, it gets printed, then the "Next" statement happens, which increments X by 1 and prints it out again. This repeats five times.

To answer your question, the Y value is being automatically incremented for you. You don't have to touch the Y variable while between the For and Next statements. As for making it like this:

For y = 0 to x-1

You are looping through a listbox, going by an index (or, in this case, the ItemData, which is an index reference). In a listbox, the index starts at zero. Therefore, if you have five items in your listbox, then the indexes are set up like this:

VisibleItem1 = Index 0
VisibleItem2 = Index 1
VisibleItem3 = Index 2
VisibleItem4 = Index 3
VisibleItem5 = Index 4

This is why you are subtracting 1 from X. Keeping with a listbox that contains five values, this value:

lstErrors.ListCount

is equal to 5. However, your highest index is 4. If you try to read ItemData(5), you'll get an error (subscript out of range). Because an Index is zero-bound, you want your loop to go from 0 to listcount-1.

Slightly off topic, but still relevant, you can go both directions in a For/Next, and you can go in larger increments than one. For example, this code:

For x = 0 to 25 Step 5
Debug.Print x
Next

will show this in the immediate window (Ctrl-G):

0
5
10
15
20
25

And this code:

For x = 25 to 0 Step -5
Debug.Print x
Next

will show this in the immediate window:

25
20
15
10
5
0

Make more sense now?

 

[XLEAccessGuru]

Great explanation! That helps quite alot! I can't think of ever having read a better explanation of the counter concept in a 'real life' situation.

One last question to get me past this stupid list box thing. How the heck do you select all items in a list box programmatically?

 

[pbaldy]

Me.lstErrors.Selected = True

 

[XLEAccessGuru]

DOH! That was pretty simple.

Thanks Baldy.

 

[pbaldy]

No problem; glad we got it sorted out.