ItemSelected to Array

[Fantast]

Hi. I have a listbox which contains a large list of items (RowSource Type: "Table/Query"). I would like to store or remove the selected items in an array with distinct values. Currently I achieve this with the following code:

For Each varItem In sourceList.ItemsSelected
    varSelectedValueArray(iCount) = sourceList.Column(0, varItem)
Next varItem

' Some code to compare and append varSelectedValueArray to targetArray

This will probably work fine, but considering the possible large amount of data selected in the listbox, and the fact that with system defined objects usually for loops are the slowest choice, I wondred whether there is any faster method to convert the Listbox.SelectedItems into an array. Also is it possible to convert all data in a listbox, selected or not, into an array without looping through them?

I will put my questiosn regarding comparing and appending the arrays into a new thread

Tanks in advance

 

[jdraw]

Also is it possible to convert all data in a listbox, selected or not, into an array without looping through them?

If the source of the listbox is a Table/query, why not use that table/query to populate the array directly? Why do you still need the listbox, or perhaps you don't.

I think you have to loop through the table/query as a recordset to populate the array.
Here's sample using an array col() and a recordset based on table (Skills) with a field skillName.

Dim db as dao.database
Dim rs as dao.recordset
Dim col() As String
Dim I As Integer
I = 0
Set db = CurrentDb
Set rs = db.OpenRecordset("Skill")
Do While Not rs.EOF
I = I + 1
ReDim Preserve col(I)
col(I) = rs!skillName
rs.MoveNext
Loop

 

[vbaInet]

Using jdraw's suggestions, note that there's also the GetRows() method of a recordset object which returns an array so there's no need to loop through it.

Also, since you're removing it from the listbox and not from the table bound to the listbox, you should be populating your listbox in code. This is how it would work in a multi-user environment.

 

[jdraw]

Good point with the GetRows, I don't use it often enough---

 

[Fantast]

I see, the GetRows solution is what i need indeed to get all the items of the listbox in an array. however, I really would like to convert only the selected items into an array as well. Is it possible to convert the Listbox.ItemsSelected into an array without looping through them?

Also, what data type is listbox.ItemsSelected? Is it a variant?

 

[vbaInet]

The ItemsSelected collection is an array of variants already so it's pointless saving it into an array again. Perform whatever operation you want to perform on ItemsSelected and requery the listbox.

Did you consider the other options presented above?

 

[Fantast]

I have been away for a couple of days, sorry for teh late reply.

Did you consider the other options presented above?

The options presented above concerned the getrows method, which will only will work for the entire query rowsource of the listbox. It will obviously not work for just the selected items. So to convert the selected items into an array I still loop through them and then store the actual value in an array.

The ItemsSelected collection is an array of variants already so it's pointless saving it into an array again.

True, but what I actually need is not the index number of the selected items, but the value of the selected items. I can not retrieve those values without looping through the ItemsSeleced collection and storing the value of the found index number in an array (please correct me if I'm wrong). Thus right now my code looks like this:

Public Function ConvertLbItemsSelectedToArray(lbItemsSelected As ListBox) As Variant
    Dim varArray() As Variant
    Dim varItem As Variant
    Dim iCount As Long
    
    iCount = 1
    For Each varItem In lbItemsSelected.ItemsSelected
        ReDim Preserve varArray(1 To iCount)
        varArray(iCount) = lbItemsSelected.ItemData(varItem)
        
        iCount = iCount + 1
    Next varItem
    
    ConvertLbItemsSelectedToArray = varArray
End Function

I guess the conclusion is that it is not possible to get the values of selected items in a listbox into an array without looping through their index numbers in the ItemsSelected collection.

 

[vbaInet]

I guess the conclusion is that it is not possible to get the values of selected items in a listbox into an array without looping through their index numbers in the ItemsSelected collection.

Correct!

But I still don't see why you need a separate array for this. What are you using the array for?

 

[Fantast]

I have build a dual listbox module class where selected items are moved from one listbox to the other one after clicking a button. The selected item vlaues are stored in an array. A property of the class holds a string of the vlaues which have been selected, and thus should show up in the listbox holding the selected records, but no longer should show up in the listbox which shows the remaining available choices. The string containing those values is the result of a Join(array, ",") function, joining the values in the selected items array.

Of course I could also had stored teh values of the selected items directly into a string when I looped through them, instead of stroing them first in an array. But I feel that an array will give me more freedom to "do things" with the data before they are joined into a string. Plus, as far as I can see, a Join() operation is quiet inexpensive and thus not a speed issue.

Right now I'm running into the issue that when the selected records count is really big (6200+), the resulting Join also becomes too big and can no longer be stored into a string variable. I'm concidering creatign a temporary table and storing the data into such a table instead of an array at the moment. Or is there a way to tell the query something like this:

SELECT * FROM TableName WHERE [ID] NOT IN Array

 

[vbaInet]

Right now I'm running into the issue that when the selected records count is really big (6200+), the resulting Join also becomes too big and can no longer be stored into a string variable.

Is it a human being that will make 6200+ selections?

 

[Fantast]

Yes. Although this is not something for which I forsee that it will happen often, I like to have form being capable of handling all possibillities. The listbox query rowsource will produce almost 10000 records. Thus, theoretically it should be possible for the user to select any number betweer 1 to 10000 records.

The way the calss module handles the rowsource right now is by putting a NOT into the rowsource query of one of the listboxes. So one listbox would have:

SELECT * FROM TableName WHERE NOT [ID] IN (1,2,3)

While the other listbox would have:

SELECT * FROM TableName WHERE [ID] IN (1,2,3)

.

I considered adding a test which switches the NOT to the other rowsource query as soon as the selected rocord count exceeds the half of all Records. This would double the capacity of the listbox selection method, as instead of a query saying

SELECT * FROM TableName WHERE NOT [ID] IN (1,2,3,...,8500)

I would get a mutch shorter query like this:

SELECT * FROM TableName WHERE [ID] IN (8500, 8501, 8502, ..., 10000)

But I'm in doubt about this solution, as it is still not completely error proof. If at some point the total data count would rise to by example 14000 records, even this method will start giving errors. So I'm affraid I have no other option than to create a temporary table where the data gets stored.

 

[vbaInet]

My advice to you is don't perform that calculation on the fly if you're not going to use it immediately. Calculate it when requested.

Note, that it's possible for you to hit the character limit of an SQL statement with what you're doing. I don't know if there is a maximum limit of characters in the IN() operator in MS Access but I suspect there isn't but obviously it's not going to be the quickest query (depending on the number of rows selected).

It would be sensible to limit the number of selections the user can make.

 

[Fantast]

I really don't like user limitations when they are only technique based. In my opinion programmers should strive to give users maximum functionality, independent of the hassle they have to go through to achieve that~

I worked it out through the use of temporary tables and looping through the selected records to insert each value individually into the temporary table. The table will be the rowsource for the list with selected items. I hate looping through arrays when I'm working in a high-level language like VBA. I always keep the feeling that there must be a language specefic function which will achieve the same result in mutch higher speed.

Anyway, thanks for the advise and support.

 

[vbaInet]

VBA is not as powerful as other programming languages. Yes programmers should give maximum functionality but you also have to weigh out the cost or expense (i'm not talking about money here) of the process before you implement. It's easy to write code to do what you want but you have to think about the impact on your environment.

If you were doing this in a fully OOP supported environment then I'm sure there will be a much quicker way of doing things without the need of a temp table.

I hope the temp table is local.

 

[Fantast]

You are right there. If it slows down the pocess significantly, limiting the functionality is a valid choice.

The table is local. When looking for temporary tables options in Access I did read about creating a whole temporary database and storing temporary tables in there. I can see its advantages, as you no longer need to keep track of which tables should be deleted after the end of the user session. But I was doubtfull about it because it seemed to me as an overkill solution for what I'm trying to achieve. Keeping the tables local and keeping track of their names so that I can close them one by one after the end of the user session, seemed to me as a cheaper solution. I assume you agree on this, considering your last reply?

 

[vbaInet]

The main reason why you need to keep the temp table local is because the database is being used by more than one person and user needs their own table to perform this operation.

 

[Fantast]

I'm starting to doubt whether I understood you well the first time... By local you mean that the table is stored in the same database as the one in which all the modules, forms and tables are stored, right? Because creating a second database to store the temp tables into seems to me just as well a solution which is user independent, as long as the database name is automated.

Just to be safe, I thought that actuall temporary tables do not exist in Access. When I say temporary table, I'm talking about regular tables which are deleted by VBA code after their use has expired. Is that correct?

 

[vbaInet]

When you deploy your database it is split into two parts - Back End which will contain all your tables and kept on the server and a Front End which will contain all your forms, reports, queries, macros and modules and is placed in every user's machine.

A local table is one that you include in the Front End. You don't necessarily need to delete the table if it's being used all the time. Just empty when necessary.

 

[Fantast]

I see. The users for the tool prefer to copy the complete database to their notebook and then run the forms whenever they like, wherever they like. I did consider splitting the database into a front end and a back end, but decided not to do so after some consideration. The users will download the complete database every so now and then to their own laptop. Having two files which need to be downloaded makes things unnecesarrily complex for them in my opnion.

Right now there is one secured database which only I can access. This database updates it's own data once per week and stores an accde version of its self on a location which is accessable for everyone. The accde is then renamed by a bat file into accdr. The bat file also makes a zip file with the accdr in it for faster downloads. They can run the accdr directly from teh shared location or download the zip file. This all seems to me as a good and save solution for the users, I do not see why I should split db at the moment.

Emptying or deleting the temprary tables will both do the trick. I feel the tool will be more in control of the database if it drops and creates the table every time. I admit though that I can not back that feeling up with reason at the moment ~

 

[vbaInet]

Sounds like you should start looking into a web based solution instead.

Batch updating then? That's fine, but you will have less control over data consistency unless each user has differing responsibilities and most of the time whatever actions they perform on data wouldn't affect any other user's input after you batch update.

Constantly creating and deleting tables in MS Access can cause bloating. Deleting is more recommended.