List all Form Objects problem

[Peter Hibbs]

Access 2000/3/7.

I am using the following code to populate a combo box with a list of
all the forms in the database.

In a Module :-
'-------------------------------------------------------------------------------
Public Function GetList() As String
'Returns list of database form objects as a string

Dim obj As AccessObject, db As Object

Set db = Application.CurrentProject
For Each obj In db.AllForms
GetList = GetList & obj.Name & ";"
Next obj
db.Close
Set db = Nothing

'Strip off last ; if any form names present
If Len(GetList) > 0 Then GetList = Left(GetList, Len(GetList) - 1)

End Function
'-------------------------------------------------------------------------------
And in the form code :-

Me.cboObject.RowSource = GetList()

which works fine.

First problem-
I would like to ONLY return forms which are NOT sub-forms on another
form. The obj.Type parameter seems to return value 2 for all forms. Is
there any way to determine if a form is a sub-form or not (Access must
know!).

Second problem-
The list returns the form names as a random list, is there any simple
method to sort the form names in alphabetical order (short of creating
a temporary table and binding that to a query).

TIA

Peter Hibbs.

 

[Douglas J. Steele]

No, Access doesn't know. Any form can be used as a subform. A form that's
used as a subform can also (usually) be used stand-alone. You may have to
adopt a naming convention, and use that as a way of weeding them out.

An answer to your second question is to query the MSysObjects table instead.

SELECT [Name] FROM MSysObjects WHERE [Type]=-32768 ORDER BY [Name]

 

[Dirk Goldgar]

First problem-
I would like to ONLY return forms which are NOT sub-forms on another
form. The obj.Type parameter seems to return value 2 for all forms. Is
there any way to determine if a form is a sub-form or not (Access must
know!).

The only way to know that would be to open each form in design view
(hidden), check for the presence of subform controls on the form, and
compile a list of each subform control's SourceObject. Then you could
compare that list against the list of all forms, and remove from the form
list all those that are on the source-object list.

BUT this cannot be reliable. It is possible for the same form object to be
used both as a main form and a subform, and it is possible for a subform
control's Source Object property to be set or changed at run time. If you
are the designer of the database, you can say whether these techniques have
been used, and therefore whether you can apply this technique.

Be aware, also, that opening and closing every form in design view is likely
to take a noticeable amount of time.

 

[Peter Hibbs]

Thanks Douglas and Dirk,

I had suspected as much but it is not a major problem, the user will
usually know which forms are which.

The sort option looks good, I will try that out.

Peter Hibbs.

 

[Douglas J. Steele]

One approach I've taken before is to have my own metadata table. It's simply
a list of all the queries, forms and reports in the database, and includes
the description, the RecordSource for forms and reports, and a boolean field
whether or not to display the object in lists. I've got a maintenance form
that includes code to go through the database and see whether there are
objects missing from that table and allow you to maintain the boolean field.
It may be a lilttle kludgy (and redundant), but it works.

 

[Peter Hibbs]

Doug,

Your code works perfectly (of course) and is much simpler than my
previous attempt. I actually need to show all Reports and Macros as
well which it does with a minor mod. I have shown the code below just
in case it is of use to anyone else.

'----------------------------------------------------------------------------------------
Public Function GetList(vType As Long) As String

'Returns list of database objects as string
'Entry (vType) = Object Type Number
'Exit GetList = List of specified objects (delimited with

Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset("SELECT [Name] FROM MSysObjects " _
& "WHERE [Type] = " & vType & " ORDER BY [Name]")
Do Until rst.EOF
GetList = GetList & rst!Name & ";"
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
If Len(GetList) > 0 Then GetList = Left(GetList, Len(GetList) - 1)

End Function
'----------------------------------------------------------------------------------------

I call it with the Type Number as the in-line argument like so :-

Me.cboObject.RowSource = GetList(-32768) 'show Forms list

Use -32764 for Reports and -32766 for Macros.

This code is part of a Flex Grid demo I am writing to create a
Switchboard form (similar to the Access Switchboard) but with more
facilities. As it could be (but probably won't be) used by anyone, I
don't have the option of naming sub-forms (and sub-reports) with a
naming convention that could identify them (although the developer
could easily add that option if they needed to).

Anyway, thanks again for your invaluable help.

Peter Hibbs.