List Box with all tables, queries and forms of my database

[isetea]

Hi,

is there any VBA code I can try to get all tables, queries and forms of my database selected and displayed in a list box of a form? And also the list box must be always held the current vlaues. Meaning if I enter a new table or form, the list box needs to show it.

Any help is much appreciated. :)

Thanks

[MMcCarthy]

Create a lookup table called ObjectType as follows:

TypeID TypeDesc
====== ========
1 Table
5 Query
-32768 Form
-32764 Report

You could then use a query on the Msysobjects table as follows:
SELECT DLookUp("[TypeDesc]","ObjectType","[TypeID]=" & [MsysObjects].[Type]) AS ObjType, MsysObjects.Name
FROM MsysObjects
WHERE (((DLookUp("[TypeDesc]","ObjectType","[TypeID]=" & [MsysObjects].[Type])) Is Not Null) AND ((MsysObjects.Name) Not Like '~*' And (MsysObjects.Name) Not Like 'MSys*'))
ORDER BY DLookUp("[TypeDesc]","ObjectType","[TypeID]=" & [MsysObjects].[Type]), MsysObjects.Name;

Hi,

is there any VBA code I can try to get all tables, queries and forms of my database selected and displayed in a list box of a form? And also the list box must be always held the current vlaues. Meaning if I enter a new table or form, the list box needs to show it.

Any help is much appreciated. :)

Thanks

[isetea]

I did like you said. But it is given me an error message saying "data type mismatch in criteria expression". Also I can't exit the error message any more, need to end task via task manager.
Are you sure it works?

Create a lookup table called ObjectType as follows:

TypeID TypeDesc
====== ========
1 Table
5 Query
-32768 Form
-32764 Report

You could then use a query on the Msysobjects table as follows:
SELECT DLookUp("[TypeDesc]","ObjectType","[TypeID]=" & [MsysObjects].[Type]) AS ObjType, MsysObjects.Name
FROM MsysObjects
WHERE (((DLookUp("[TypeDesc]","ObjectType","[TypeID]=" & [MsysObjects].[Type])) Is Not Null) AND ((MsysObjects.Name) Not Like '~*' And (MsysObjects.Name) Not Like 'MSys*'))
ORDER BY DLookUp("[TypeDesc]","ObjectType","[TypeID]=" & [MsysObjects].[Type]), MsysObjects.Name;

[MMcCarthy]

In the lookup table ObjectType did you declare the TypeID as a Number (Long Integer).

I tested the query and it did work.

I did like you said. But it is given me an error message saying "data type mismatch in criteria expression". Also I can't exit the error message any more, need to end task via task manager.
Are you sure it works?

[isetea]

:o
No I did not! Sorry.

It's working now. I needed to add Tpe 6 as well because there were linked tables.

Thanks very much for you help! :)

In the lookup table ObjectType did you declare the TypeID as a Number (Long Integer).

I tested the query and it did work.

[thewolfsden5]

This worked in the query but I was not able to view the field "Names" in the list box of the form. The only field that was displayed was ObjType.