generate list of database objects

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've added a few tables to my database, to log updates to the application.
One of the tables lists the objects that were updated, by type (i.e. Table,
Query, Form, etc). Is there a way to create a lookup on this column, to list
all the objects of the specified type, so that I don't have to type the
object name?

Thank you for any help you can provide.
 
Hi Allen,

use a form for data entry

Don't use lookup fields in table design
http://www.mvps.org/access/lookupfields.htm

here is the SQL to generate a list of object types and objects:

SELECT GetObjectType([Type]) AS ObjectType, MSysObjects.Name
FROM MSysObjects
WHERE ( ((Left([Name],1))<>"~") AND ((Left([Name],4))<>"msys"))
ORDER BY GetObjectType([Type]), MSysObjects.Name;

this uses a function to give you the text version of type.

make a general module and paste this in before your run the query or SQL:

'~~~~~~~~~~~

Function GetObjectType(pType) As String
Select Case pType
Case 1: GetObjectType = "Table"
Case 5: GetObjectType = "Query"
Case -32768: GetObjectType = "Form"
Case -32764: GetObjectType = "Report"
Case -32766: GetObjectType = "Macro"
Case -32761: GetObjectType = "Module"
Case Else: GetObjectType = ""
End Select
End Function

'~~~
as for your question to generate a list of just the specified type, you
can replace the SQL for your combo or listbox in code. If you need help
with this, first make your form to show all objects, and then post back
with questions :)


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
Thanks Crystal. That's exactly what I needed.

I used lookup fields because I did a quick and dirty implementation for my
own use, and haven't created data entry forms as yet.

....Larry

strive4peace said:
Hi Allen,

use a form for data entry

Don't use lookup fields in table design
http://www.mvps.org/access/lookupfields.htm

here is the SQL to generate a list of object types and objects:

SELECT GetObjectType([Type]) AS ObjectType, MSysObjects.Name
FROM MSysObjects
WHERE ( ((Left([Name],1))<>"~") AND ((Left([Name],4))<>"msys"))
ORDER BY GetObjectType([Type]), MSysObjects.Name;

this uses a function to give you the text version of type.

make a general module and paste this in before your run the query or SQL:

'~~~~~~~~~~~

Function GetObjectType(pType) As String
Select Case pType
Case 1: GetObjectType = "Table"
Case 5: GetObjectType = "Query"
Case -32768: GetObjectType = "Form"
Case -32764: GetObjectType = "Report"
Case -32766: GetObjectType = "Macro"
Case -32761: GetObjectType = "Module"
Case Else: GetObjectType = ""
End Select
End Function

'~~~
as for your question to generate a list of just the specified type, you
can replace the SQL for your combo or listbox in code. If you need help
with this, first make your form to show all objects, and then post back
with questions :)


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


I've added a few tables to my database, to log updates to the application.
One of the tables lists the objects that were updated, by type (i.e. Table,
Query, Form, etc). Is there a way to create a lookup on this column, to list
all the objects of the specified type, so that I don't have to type the
object name?

Thank you for any help you can provide.
Click to expand...
Click to expand...
 
you're welcome, Larry ;) happy to help

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


Thanks Crystal. That's exactly what I needed.

I used lookup fields because I did a quick and dirty implementation for my
own use, and haven't created data entry forms as yet.

...Larry

strive4peace said:
Hi Allen,

use a form for data entry

Don't use lookup fields in table design
http://www.mvps.org/access/lookupfields.htm

here is the SQL to generate a list of object types and objects:

SELECT GetObjectType([Type]) AS ObjectType, MSysObjects.Name
FROM MSysObjects
WHERE ( ((Left([Name],1))<>"~") AND ((Left([Name],4))<>"msys"))
ORDER BY GetObjectType([Type]), MSysObjects.Name;

this uses a function to give you the text version of type.

make a general module and paste this in before your run the query or SQL:

'~~~~~~~~~~~

Function GetObjectType(pType) As String
Select Case pType
Case 1: GetObjectType = "Table"
Case 5: GetObjectType = "Query"
Case -32768: GetObjectType = "Form"
Case -32764: GetObjectType = "Report"
Case -32766: GetObjectType = "Macro"
Case -32761: GetObjectType = "Module"
Case Else: GetObjectType = ""
End Select
End Function

'~~~
as for your question to generate a list of just the specified type, you
can replace the SQL for your combo or listbox in code. If you need help
with this, first make your form to show all objects, and then post back
with questions :)


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


I've added a few tables to my database, to log updates to the application.
One of the tables lists the objects that were updated, by type (i.e. Table,
Query, Form, etc). Is there a way to create a lookup on this column, to list
all the objects of the specified type, so that I don't have to type the
object name?

Thank you for any help you can provide.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Copy object 2
Counting multiple memberships 1
show number of records updated 2
MS Access Table missing 3
List Run-Time Errors 2
Error Exporting Access Query Results to Excel 1
AI Art Generator 12
PUtting an Access database in the References list 6

Back
Top