multi-valued variables intVar(1)=X intVar(2)=y

[rolaaus]

I know there is a way to fill a single variable with multiple values, and I have been trying to find some examples of this on the web, but to no avail.

What I need, in addition to filling the variable with multiple values, is the syntax for cycling trhough the values added, no matter how many or few are added.

Thanks.

 

[Moniker]

Lookup Collections in Access Help.

In short, a Collection is similar to an array, but doesn't have a predefined size (and you need that feature, according to your description), and it can accept multiple data types (text, numbers, etc.) whereas an Array has a predefined size and only accepts one data type.

Generically, it'll look something like this:

Dim colMyCollection As Collection

Set colMyCollection = New Collection

TO ADD STUFF TO IT:

colMyCollection.Add "ItemA"
colMyCollection.Add NumberB

Lookup the Add Method in VBA help as there are more optional parts for an add.

TO REMOVE STUFF FROM IT:

colMyCollection.Remove 2
colMyCollection.Remove KeyValue

The colMyCollection.Remove 2 removes the item with index=2. (Collections are one-based, so this is in fact removing item #2.) The colMyCollection.Remove KeyValue removes the entry where the keyValue = whatever keyValue you setup. Again, look at the Add Method for help on this.

TO ACCESS SPECIFIC STUFF IN THE COLLECTION:

colMyCollection(1) = the entry for item #1
colMyCollection(16) = the entry for item #16

 

[pbaldy]

Sounds like you want an array. This one peels out multiple email addresses from a string:

      Dim astrAddy()       As String
      Dim x                As Integer
  
      astrAddy = Split(Me.TextboxName, ";")
  
      For x = LBound(astrAddy) To UBound(astrAddy)
        Debug.Print astrAddy(x)
      Next x

 

[rolaaus]

Moniker,

Actually, I will be able to define the array size, beause what I'm trying to do is fill the array with a list of items selected in a listbox.

I don't know if you have seen my discussion's in other posts on this board, but I am trying to remove multiple items from a listbox based on if they are Selected, however, the problem that I think I am running into (and I may be wrong about this) is that Access appears to remove all the selections from the selecteditems as soon as you use RemoveItem the very first time. For some reason that doesn't seem logical because my loop procedure should still run since the amount of times it loops is designated before the RemoveItem line of code.

Anyways, since I am running into this problem, I though that filling an array (I didn't remember the name at the time I posted - I've only had to use an array once or twice in about 15 years of programming) with the selected items first will allow me to then loop through the array and delete those items.

In addition to being able to define the array (using the ItemsSelected.count property), I am filling each item in the array with an integer value.

If you have some array code handy that will point me in the right direction, it would be appreciated, in the meantime, I'll start doing some 'net searching on array, since I forgot about that terminology.

 

[Moniker]

Using an array to do what you're doing is making it far too complex. Removing items from a listbox just isn't that hard. What exactly is the code you're using?

 

[rolaaus]

Dim varItem As Variant

For Each varItem In Me.lst_Files.ItemsSelected
    Me.lst_Holdings.AddItem Item:=Me.lst_Files.ItemData(varItem)
Next varItem

The code will remove the first .ItemSelected, then stop running - I've used breakpoints to see what is happening and once it removes the first item, it just stops dead.

 

[Moniker]

Here's how to get through all the items in a listbox. You should be able to take over from here.

For ctr = Me.lst_Files.ListCount - 1
If Me.lst_Files.Item(ctr).Selected Then
Debug.Print "Item " & Me.lst_Files.Item(ctr) & " is Selected"
End If
Next

 

[rolaaus]

Here's how to get through all the items in a listbox. You should be able to take over from here.

For ctr = Me.lst_Files.ListCount - 1
If Me.lst_Files.Item(ctr).Selected Then
Debug.Print "Item " & Me.lst_Files.Item(ctr) & " is Selected"
End If
Next

I had to change the posted code to the following,

For myCounter = 0 To Me.lst_Files.ListCount - 1
    If Me.lst_Files.Selected(myCounter) Then
        Me.lst_Holdings.AddItem Item:=Me.lst_Files.ItemData(myCounter)
        Me.lst_Files.RemoveItem myCounter
    End If
Next myCounter

But it still stops after adding the 1st selected item to lst_Holdings (the 2nd list), and deleting it, then it doesn't even run anymore IF evaluations.

 

[Moniker]

That's because you're using lstFiles as the counter source and you are deleting from it, thereby nullifying the value of MyCounter.

Change it to a two-step process. Go through and add everything to list2, and then go back and remove from list1.

A much easier to handle this would be to have a hidden column in list1 that says "ImSelected" in one way or another, and then use a query to populate both listboxes. list1 = SELECT * FROM SourceTable WHERE ImSelected = False. list2 = SELECT * FROM SourceTable WHERE ImSelected = True

 

[rolaaus]

Moniker, I took your suggestion and changed it a tad bit. I went and used the 2nd listbox as a source of what to delete, basically cycling through anything in that list and deleting it from list 1.

For Each varItem In Me.lst_Files.ItemsSelected
    Me.lst_Holdings.AddItem Item:=Me.lst_Files.ItemData(varItem)
Next varItem

For myCounter = 0 To Me.lst_Holdings.ListCount - 1
    Me.lst_Files.RemoveItem Me.lst_Holdings.ItemData(myCounter)
Next myCounter

Okay, now I feel kind of stupid. I actually have 3 lists - the 1st is a main list as I've described before, and the other 2 are sub-sets. I take the 1st main list and fill the other 2 with the filenames that are listed in an browsefolder API.

I guess none of this matters as to why I feel stupid, but basically, this code will only work right if you manage to grab all of the files you want the very first time for each sub-set, otherwise, if you try re-adding another file into a sub-set that already has some filenames in it, the procedure throws an error about not being able to find that file in the main list (it was already deleted).

Guess I'll throw an error handler in here and ignore this particular error and proceed with the next line in the loop. I don't typically like to do on error resume next, but in the rare case, I guess that's the best way to handle this.