test if item exists in collection

  • Thread starter Thread starter Pierre
  • Start date Start date
P

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.
 
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)
 
Pierre said:
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 ?
Click to expand...


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.
 
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 ?
Click to expand...

Sometimes the answer is to use a Dictionary (with its Exists method)
rather than a collection.
 
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.
Click to expand...
Click to expand...
 
Back
Top