Listbox of all open forms

[whenthegeeseinvade]

Morning everyone

I'm trying to create an elegant way of switching between open forms in my database.

I thought that a listbox that holds the names of all open forms could be ever present on the screen (like quickbooks which I also use).

I've had a good look around but I can't quite figure out how to get it to work. I've found some code which queries the msysobjects table

===============

SELECT MsysObjects.Name AS DisplayName FROM MsysObjects WHERE ( ((MsysObjects.Type)=-32768)) ORDER BY MsysObjects.Name

===============

but I can't figure out how to test if the form is loaded or how to show the caption rather than the form name.

It wouldn't suprise me if someone has already answered this here, but I'm afraid I can't find it.

Any help would be fab.

Many thanks,

Larry

 

[the_net_2.0]

the objects table will not work because it doesn't have a field that indicates the status. I don't think anyway.

you can use the docmd object I believe. there is a command there that does it. You can also use:

currentproject.allforms

and loop it from 0 to the count-1. I believe the "allforms" property will only give you the indexes of the forms that are open. it's either that or the simple:

forms()

collection. One of those only give the array of open forms, and one gives all the forms, open or not (I think).

for instance, to get the names of the open forms:

for i=0 to currentprojects.allforms.count-1
   listbox.additem currentproject.allforms(i).name
next i

that is not tested, but the idea is there.

 

[whenthegeeseinvade]

Thanks Net

This populates the listbox with all the forms in the database (open and closed).

How could I restrict this to only the forms that are open, list their caption rather than their name and open the relevant form when I double click on the name?

I also need to update this whenever a form is opened or closed. Is there a way to do this with a system-wide event rather than having to add code to every form (about 300 of them!)

Sorry - bit of a newbie to this! Written a huge database here but still struggling with some aspects of VBA.

Cheers,

Larry

 

[vbaInet]

The term "opened" could mean two things: opened in design view or opened in form view. There's a way to test for the latter but as the net 2.0 has already mentioned, you can use the Forms() collection to get the forms opened in design or form view. I'm sure your users won't be opening the forms in design view so this method is safe.

Forms(i).Caption

To update the listbox or combo box, requery it:

Me.Listbox1.Requery

But you would have to do this for everytime a form is closed.

 

[the_net_2.0]

addition to Inet>>

yes you can have a system-wide procedure, but there is no way, other than code behind each form's close or open events to "detect" the opening or closing of a form. so eventually, behind each form's "close" event, you'll have to put:

=updatebox("formname")

then obviously, a global procedure would be used:

public sub updatebox(frm as string)

   //update code, minus the formname, etc...

end sub

yes, that is a pain, but there might be a way to automate the code writing. are you aware of the extensibility library? you can write vba code with vba code. each vba module, forms, reports, whatever, is an object in that library and the form modules' "type" is 100 in the index. so...for instance, if you want to tell the user when they close a form, you can write 300 procedures in a few seconds, maybe like:

sub writeglobal()
dim strcode as string
dim i as long
dim vbe as vbide.vbe
dim vbproj as vbide.vbproject
dim vbobj as vbide.vbcomponent

set vbe = application.vbe
set vbproj = vbe.activeproject

strcode = "Private Sub Form_Close()" & vbCrLf & _
          "msgbox " & """" & "You have just closed form " & """" & _
          " & " & "me.name" & vbCrLf & "end sub"

with vbproj
   for i =1 to .vbcomponents.count
      with .vbcomponents(i)
         if .type=100 then
            .codemodule.insertlines _
               .codemodule.countoflines, strcode
         end if
      end with
   next i
end with

end sub

the only problem with doing that is that I don't THINK the code is linked automatically to the actual object. e.g.- it's in the obj's vba module, but it is not "known" by the object, thus no execution will happen. but you can try it.

The other thing that is available to you, automation-wise, is actually "putting" the "=procedure(arg)" string into the property sheet with code. That is always available, but unfortunately to do stuff like that (with obj properties and such), each object has to be opened in design view first, then modified, then saved and closed. so, 300 times for you. and doing that, without a linebreak to "reguvenate" the memory once in the loop code, would certainly crash the program for that high number of required forms. That may be the only option for you though, if the link doesn't take place with my first example.

 

[vbaInet]

Or you can use the form's Timer event to requery it every 3 seconds or so. It shouldn't be too costly.