[RESOLVED] Searching Collection - alternative solution !

[NeedSomeAnswers]

Hi Everyone,

i have 2 collections, one holding a subset of the others data, and i am trying to find out if an item in the smaller collection exists in the bigger collection.

What i have is a full list of tables & a list of enabled tables. From these i have to extrapolate the disabled tables.

I do have a solution for this but it is a bit messy and uses On Error Resumes Next which i would like to avoid, so i was wondering if anyone has an alternative solution.

This is the full sub which basically fills two listbox's. Enabled tables in one & Disabled tables in the other. a user can the move the tables between the list to enable or disable them.

Dim arrEnabledTables() As String 
Dim colEnabledTables As Collection 
Dim colAvailableTables As Collection 
Dim vitem As Variant 
Dim vitem2 As Variant 
 
On Error GoTo errorhandler 
 
Set mParent = FrmParent
 
Set colAvailableTables = New Collection 
Set colEnabledTables = New Collection 
 
colAvailableTables.Add "item1", "item1"  'List of All Tables
colAvailableTables.Add "item2", "item2" 
colAvailableTables.Add "item3", "item3" 
colAvailableTables.Add "item4", "item4" 
colAvailableTables.Add "item5", "item5" 
colAvailableTables.Add "item6", "item6" 
colAvailableTables.Add "item7", "item7" 
colAvailableTables.Add "item8", "item8" 
colAvailableTables.Add "item9", "item9" 
colAvailableTables.Add "item10", "item10" 
 
arrEnabledTables = mParent.Application.EnabledTables  'external method that returns an array of enabled tables
 
For n = 0 ToUBound(arrEnabledTables)  'add Enabled Tables to listbox & a collection
     LstEnableTables.AddItem Right(arrEnabledTables(n), Len(arrEnabledTables(n)) - 3) 
     colEnabledTables.Add Right(arrEnabledTables(n), Len(arrEnabledTables(n)) - 3), Right(arrEnabledTables(n), Len(arrEnabledTables(n)) - 3) 
Next 
 
On Error Resume Next 
For Each vitem In colAvailableTables  'get Disabled Tables and add them to the other listbox
     vitem2 = colEnabledTables(vitem) 
     If IsEmpty(vitem2) Then 
          LstDisabledTables.AddItem CStr(vitem) 
     End If 
     vitem2 = Nothing 
Next 
On Error GoTo 0

The reason i have to use OERN is that when you search a collection for an item that is not in it, it throws an error (unlike .Net which if you use an ArrayList it returns a boolean when using the contains method)

Any ideas for alternatives ?

[westconn1]

Any ideas for alternatives ?

how about a recordset? you can search result using filter

[Magic Ink]

Using OERN is pretty standard fare when it comes to determining if a collection contains a given item, however life is a little easier if you also employ the Key field when using the Add method.

Option Explicit

Private Sub Command1_Click()

    Dim col As New Collection
    Dim Search4 As String
    
    col.Add "Henry", "Henry"
    col.Add "John", "John"
    
    Search4 = "Henry"
    
    On Error Resume Next
    col.Add Search4, Search4
    If Err = 457 Then
        MsgBox Search4 & " is already in the collection"
    ElseIf Err Then
        MsgBox "Unanticipated error " & Err & " " & Err.Description
    Else
        'if we do not want Search4 to be added
        col.Remove (Search4)
        
        MsgBox Search4 & " is NOT already in the collection"
    End If
    On Error GoTo 0

End Sub

[NeedSomeAnswers]

Hi Guys thanks for the responses,

i have been so busy fixing some post implementation bugs i almost forgot i had even posted this. I got the answer i expected anyway, i didn't think there would be a way.

Westconn - that is an interesting idea to use a disconnected recordset, and one i will try in the future i just dont have any time to really rewrite it now so i will leave it as is.