By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,663 Members | 762 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,663 IT Pros & Developers. It's quick & easy.

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

P: 44
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
Oct 17 '06 #1
Share this Question
Share on Google+
5 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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
Oct 18 '06 #2

P: 44
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;
Oct 18 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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?
Oct 18 '06 #4

P: 44
: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.
Oct 18 '06 #5

P: 1
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.
Feb 24 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.