Poplulating a list box

[netsmurph]

have taken over on a project making enhancements to an auditing tool. It has one module that takes in stock entries and loads values into a list box to "remember" the last selected list.

The code being used to popluate this list box on form load is below, but it takes an absolute age (maybe 60 secs - which is deemed unacceptable) to run through this procedure if there are more than 2000 records or so.

What i am wondering if whehter there is any other way to update this list box object other than looping through each value and updating as below.

Any suggestions would be much appreciated.


Andrew


Dim iCurrentRow As Integer
Dim ssql As String
Dim totalRows As Long

'* Iterate through the multi-list object and either selecting or deselecting all choices
totalRows = Me.lstType.ListCount
With Me.lstType
'For iCurrentRow = 0 To .ListCount - 1
For iCurrentRow = 0 To Me.lstType.ListCount - 1
Select Case bySelectAll
Case 1 '* True
.SELECTED(iCurrentRow) = True
Case 2 '* Invert
.SELECTED(iCurrentRow) = Not (.SELECTED(iCurrentRow))
' Case 3 '* Invert
' .Selected(iCurrentRow) = DLookup("Selected", "tblTYPE", "type like '" & LTrim(Me.lstType.Column(0, iCurrentRow)) & "'")
End Select
Next iCurrentRow
End With

 

[traingamer]

This code is not building the list entries. It is selecting OR toggling selection of entries.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[Zion7]

I agree with Greg, so I'm assuming, this list bosx that you are showing,
is where the data comes from, and you populate ANother list box elsewhere??? Otherwise, I don't see the purpose
of the code, although you claim it "works", but takes too long?

So this iteration takes forever, as would be expected,
because of the Aggregate function.

I personally, would suggest modifying your table or query,
that is behind your listbox, to avoid using the DLookup().
Try a Sum Query (I forget the name, Aggregate Query?).
Totals Query? Grouping Query?

 

[netsmurph]

Sorry - you are right, it is not populating the list box, but marking the entries as checked or not.

The reason for this is so that the list box selection is effectively stored in memory so that one a user has selected their entries for analysis, they do no need select them each time they open the form.

The suggestion about the dlookup function is valid, although this line of code is not in use (i have commmented it out) and it is still running like a dog.

The key question I guess is whether there is an alternative to updating properties of list box items without looping through a recordset.

I am thinking maybe an alternative would be just show all records in the list box when they first open it and mark each one when selected so that the next time they open the form, they will only see the items that have been selected in the list box, with a "show all" button that will bring back all records.

The drawback of this is that the user will have to then re-select all items needed, which they will probably complain about too!

Not sure if there's any better option (but hoping there visionary to correct me!)

Thanks for your replies.


Andrew