test if item exists in collection

[Pierre]

Hello,

In a VBA collection I need to test if an item exists or not.
How to do this without having to loop accross all the collection ?

Thanks.

Pierre.

 

[bob]

Use this query to show the objects and set your desired object as a
parameter:


SELECT
IIf(Nz(Object.Type,0)=5,"Queries",IIf(Parent.Name="Scripts","Macros",Parent.Name))
AS ObjectType, Object.Name AS ObjectName,
Switch([ObjectType]="Tables",1,[ObjectType]="Queries",2,[ObjectType]="Forms",3,[ObjectType]="Reports",4,[ObjectType]="Macros",5,[ObjectType]="Modules",6,True,0)
AS SortOrder, CurrentDb.Name AS WhatDatabase
FROM MSysObjects AS Parent INNER JOIN MSysObjects AS [Object] ON
Parent.Id=Object.ParentId
WHERE (((Left$(Nz(Object.Name," "),1))<>"~") And ((Left$(Nz(Object.Name,"
"),4))<>"Msys") And ((Parent.Type)=3));


Bob Galway
(e-mail address removed)

 

[Brendan Reynolds]

If you don't want to walk the collection, then the alternative is to attempt
to do something with the item, and trap the error that will be raised if it
doesn't exist. I have examples of both approaches at the following URL ...

http://brenreyn.brinkster.net/isobject.asp

 

[Marshall Barton]

In a VBA collection I need to test if an item exists or not.
How to do this without having to loop accross all the collection ?

Searching the collection is the preferred method. If
that's taking too long, try retrieving the item's value and
using error traping when the item is not in the collection.

 

[John Nurick]

In a VBA collection I need to test if an item exists or not.
How to do this without having to loop accross all the collection ?

Sometimes the answer is to use a Dictionary (with its Exists method)
rather than a collection.

 

[David C. Holley]

For that matter, why not just do a DLookup() or DCount() on MSysObjects?

Use this query to show the objects and set your desired object as a
parameter:


SELECT
IIf(Nz(Object.Type,0)=5,"Queries",IIf(Parent.Name="Scripts","Macros",Parent.Name))
AS ObjectType, Object.Name AS ObjectName,
Switch([ObjectType]="Tables",1,[ObjectType]="Queries",2,[ObjectType]="Forms",3,[ObjectType]="Reports",4,[ObjectType]="Macros",5,[ObjectType]="Modules",6,True,0)
AS SortOrder, CurrentDb.Name AS WhatDatabase
FROM MSysObjects AS Parent INNER JOIN MSysObjects AS [Object] ON
Parent.Id=Object.ParentId
WHERE (((Left$(Nz(Object.Name," "),1))<>"~") And ((Left$(Nz(Object.Name,"
"),4))<>"Msys") And ((Parent.Type)=3));


Bob Galway
(e-mail address removed)

Hello,

In a VBA collection I need to test if an item exists or not.
How to do this without having to loop accross all the collection ?

Thanks.

Pierre.