I've got some forms with Textbox input widgets wherein

the user enters an item to process. To determine whether

the item exists, I've been using:



bFound = false

for i = 0 to myCollection.count -1

    if myCollection(i).Name = strName then

        bFound = true

        exit for

    end if

next

if bFound then

   ... do whatever

end if



A pre-populated listbox or combobox isn't a good option for

the interface.

Is there a better way than iterating thru the collection

(which may be a table, query, or any other valid object)

to determine if an item is an object in the database?



Thanks,

Gary

Why not let VBA do the work for you:



'The function return will default to False

Private Function InCollection(ByRef strName as String) as Boolean

   'Use a variant because I don't know what type of item is

   ' stored in the collection

   Dim varRet as Variant



   On Error Resume Next

   varRet = myCollection(strName)

   'If the item is in the collection the Err object will

   ' not be set and the Err.Number will be 0

   If Err.Number= 0 then InCollection = True



End Function



|-----Original Message-----

|From: Gary M. Greenberg [mailto:garyg@g...]

|Sent: Wednesday, February 20, 2002 3:23 PM

|To: Access

|Subject: [access] searching collections

|

|

|I've got some forms with Textbox input widgets wherein

|the user enters an item to process. To determine whether

|the item exists, I've been using:

|

|bFound = false

|for i = 0 to myCollection.count -1

|    if myCollection(i).Name = strName then

|        bFound = true

|        exit for

|    end if

|next

|if bFound then

|   ... do whatever

|end if

|

|A pre-populated listbox or combobox isn't a good option for

|the interface.

|Is there a better way than iterating thru the collection

|(which may be a table, query, or any other valid object)

|to determine if an item is an object in the database?

|

|Thanks,

|Gary

|




|