[Just Curious] Easier way to check for open forms?

[ersocia]

so, i was just reviewing some of the most recent changes i've made to my DB here at work, and when i reached this piece of code, i started wondering if there's an easier, more compact way to do this. don't get me wrong, the code definitely works, and works well enough, i'm just curious for education's sake if there is a better way to go about this with less code involved? i feel like there should be a simpler way to test if any forms are open, rather than checking for each one individually.

If CurrentProject.AllForms("Resident_Automobile_Info").IsLoaded Then
MsgBox "Updating tables is not recommended while Data Entry form(s) are open." & vbCrLf & "Please close any open Data Entry forms and try again.", vbCritical
ElseIf CurrentProject.AllForms("Resident_Contact_Info").IsLoaded Then
MsgBox "Updating tables is not recommended while Data Entry form(s) are open." & vbCrLf & "Please close any open Data Entry forms and try again.", vbCritical
ElseIf CurrentProject.AllForms("Resident_Extra_Info").IsLoaded Then
MsgBox "Updating tables is not recommended while Data Entry form(s) are open." & vbCrLf & "Please close any open Data Entry forms and try again.", vbCritical
ElseIf CurrentProject.AllForms("Resident_General_Info").IsLoaded Then
MsgBox "Updating tables is not recommended while Data Entry form(s) are open." & vbCrLf & "Please close any open Data Entry forms and try again.", vbCritical
ElseIf CurrentProject.AllForms("Resident_Social_Sec_Info").IsLoaded Then
MsgBox "Updating tables is not recommended while Data Entry form(s) are open." & vbCrLf & "Please close any open Data Entry forms and try again.", vbCritical
ElseIf CurrentProject.AllForms("Resident_Status_Info").IsLoaded Then
MsgBox "Updating tables is not recommended while Data Entry form(s) are open." & vbCrLf & "Please close any open Data Entry forms and try again.", vbCritical
ElseIf CurrentProject.AllForms("Services_&_Amenities").IsLoaded Then
MsgBox "Updating tables is not recommended while Data Entry form(s) are open." & vbCrLf & "Please close any open Data Entry forms and try again.", vbCritical
Else
DoCmd.SetWarnings False
DoCmd.OpenQuery "UPDATE_RES_INFO_TABLE", acViewNormal, acEdit
DoCmd.OpenQuery "UPDATE_RESIDENT_STATUS_TABLE", acViewNormal, acEdit
DoCmd.OpenQuery "UPDATE_EMER_INFO_TABLE", acViewNormal, acEdit
DoCmd.OpenQuery "UPDATE_SOCIAL_SEC_INFO_TABLE", acViewNormal, acEdit
DoCmd.OpenQuery "UPDATE_AUTO_INFO_TABLE", acViewNormal, acEdit
DoCmd.SetWarnings True
MsgBox "Local tables updated.", vbInformation
End If

 

[DJkarl]

I would write a function in a public module, then pass the form name you are looking for.

Public function IsFormOpen(frmName as string) as boolean
    if syscmd(acSysCmdGetObjectState,acForm,frmName) = 1 then
                 IsFormOpen=true
    end if
end function

 

[ersocia]

I would write a function in a public module, then pass the form name you are looking for.

Public function IsFormOpen(frmName as string) as boolean
    if syscmd(acSysCmdGetObjectState,acForm,frmName) = 1 then
                 IsFormOpen=true
    end if
end function

thanks for the suggestion

two things:

1) while i imagine this would indeed reduce the amount of code necessary, doesn't it still involve passing each form name individually, rather than simply checking to see if any form is open? (in other words, it doesn't matter to me which form is open, just that there is a form that's open.)

2) if i were to try this method, how might i use it? i've pasted this into a new module, however i'm unclear about how i am supposed to call it into action. could you give me a short example of what that might look like in the context of my original snippet?

i'm imagining that it would be something like this:

If IsFormOpen("Resident_General_Info") = 1 Then
...
Else
...
End If

is that about right?

thanks again for the help

 

[ersocia]

please disregard my previous post. i've figured out how to call the newly created function.

If IsFormOpen("<Form Name>") = True Then
...
Else
...
End If

however, it still doesn't quite do what i was hoping for, as i still need to call the function multiple times to check for each individual form name, rather than checking to see if any form is open. it does reduce the amount of code needed, but not by much.

it seems to me fairly silly that there isn't a simple way to do this, but i could just be looking in all the wrong places.

any other ideas/suggestions?

 

[DJkarl]

I guess I misunderstood, if you don't care which forms are open, and you only want to know if there are forms open use Application.Forms.Count, it will return the count of open forms, so if the count = zero, no forms are open.

You can always leave off the = True part when the function is a boolean.
If IsFormOpen("<Form Name>") Then ... Else ... End If

 

[ersocia]

eureka

If Application.Forms.Count > 1 Then
     MsgBox "Updating tables is not recommended while Data Entry form(s) are open." & vbCrLf & "Please close any open Data Entry forms and try again.", vbCritical
Else
     DoCmd.SetWarnings False
     DoCmd.OpenQuery "UPDATE_RES_INFO_TABLE", acViewNormal, acEdit
     DoCmd.OpenQuery "UPDATE_RESIDENT_STATUS_TABLE", acViewNormal, acEdit
     DoCmd.OpenQuery "UPDATE_EMER_INFO_TABLE", acViewNormal, acEdit
     DoCmd.OpenQuery "UPDATE_SOCIAL_SEC_INFO_TABLE", acViewNormal, acEdit
     DoCmd.OpenQuery "UPDATE_AUTO_INFO_TABLE", acViewNormal, acEdit
     DoCmd.SetWarnings True
     MsgBox "Local tables updated.", vbInformation
End If

thanks once again. that seems like a much simpler solution than i previously had

 

[mdlueck]

@ersocia:

If you get to opening forms with VBA by the class of the form, then you will need this sort of way to obtain a reference to the form. The Key value in the Forms collection does not get populated when opening forms via the form's class.

  Dim frm As Access.Form

  For Each frm In Forms
    If frm.Name = "NameOfSearchedFormHere" Then
      With frm
        'Do something to the form as it has been found...
      End With
      Exit For
    End If
  Next frm

 

[ersocia]

@ersocia:

If you get to opening forms with VBA by the class of the form, then you will need this sort of way to obtain a reference to the form. The Key value in the Forms collection does not get populated when opening forms via the form's class.

  Dim frm As Access.Form
 
  For Each frm In Forms
    If frm.Name = "NameOfSearchedFormHere" Then
      With frm
        'Do something to the form as it has been found...
      End With
      Exit For
    End If
  Next frm

thank you, and i appreciate the suggestion, however:

1) i didn't completely understand what you are trying to explain here.

2) i went with DJKarl's last suggestion to use "Application.Forms.Count", and that did exactly what i was looking to do

 

[mdlueck]

1) i didn't completely understand what you are trying to explain here.

At the point you advance to opening Forms by the VBA Class which defines the form, and not via the DoCMD method of opening forms... anticipate needing to convert over to code such as I supplied as Access has a bug where the form name does not get set as the .Key the Form may be referred to with.

The capability I wanted to was to be able to receive back from forms multiple pieces of information / and to be able to pass in multiple pieces of information. Such (Inter form communication) requires the VBA code to refer to the forms by their class that defines them to Access. I did that, then discovered that the .Key value was not being correctly set, thus had to switch to loop based code to process the Forms collection searching for the Form.

 

[ersocia]

At the point you advance to opening Forms by the VBA Class which defines the form, and not via the DoCMD method of opening forms... anticipate needing to convert over to code such as I supplied as Access has a bug where the form name does not get set as the .Key the Form may be referred to with.

The capability I wanted to was to be able to receive back from forms multiple pieces of information / and to be able to pass in multiple pieces of information. Such (Inter form communication) requires the VBA code to refer to the forms by their class that defines them to Access. I did that, then discovered that the .Key value was not being correctly set, thus had to switch to loop based code to process the Forms collection searching for the Form.

sorry. i'm still lost.

from what i can gather, you're talking about ways to do far more than what i was looking for, which was simply to have a piece of code to check whether or not any form other than the main one (switchboard) was open. i did not need to pass information either to or from any form. just wanted to know if any were loaded. nothing more than that.

sorry for any confusion.

 

[mdlueck]

a piece of code to check whether or not any form other than the main one (switchboard) was open.

1) Open via traditional method
2) Open via the class of the Form

#1 is what you are doing currently. Which will break when you move onto the #2 method. Thus blipping the #2 case on your radar map so that you might be prepared.

 

[Galaxiom]

At the point you advance to opening Forms by the VBA Class which defines the form, and not via the DoCMD method of opening forms...

Could you explain exactly what you mean by this?

 

[vbaInet]

I think he's talking about creating an instance of a form multiple times. Using this method (as I'm sure you know) can't be referenced from the Forms collection. Unless he's talking about something else :S

 

[Galaxiom]

I think he's talking about creating an instance of a form multiple times. Using this method (as I'm sure you know) can't be referenced from the Forms collection. Unless he's talking about something else :S

I didn't comprehend Michael's terminology without the word "instance" included.

Moreover I don't think Micheal is actually referring to multiple instances but sees opening a form as an instance rather than directly is an "advanced" technique. In the context of the code he provided it appears he uses this even to open a single instance.

I get get this impression because looping through the Forms collection looking for name can't designate a particular instance of the form if there are multiple instances (since they all have the same name) so it would not be a very useful procedure unless there was only a single instance.

With a single instance of a form, as far as I can see it is really quite a pointless exercise to open it in this way.

References to a particular instance of a form are best done via the object that has been Set to the form. Otherwise if the instances need to be referred to collectively they should be added to their own Collection.

When I use collections like this I set them up as a Class such that I can refer to a member of the collection via the index. As such they can be refered to with equivalent syntax as one would use in the Forms Collection.

I also include a Dicionary object for the index names. This allows the Collection to be searched without using a loop.

 

[mdlueck]

sees opening a form as an instance rather than directly is an "advanced" technique. In the context of the code he provided it appears he uses this even to open a single instance.

Correct, I still only open a single instance with call back events enabled.

I only use this coding style when I need to open form dialogs and receive back the form's response.

Otherwise I use simple DoCmd.OpenForm style code to open forms.

My point still remains, the code example will work until the OP gets fancy and needs to utilize events enabled inter-form communication.

 

[ersocia]

My point still remains, the code example will work until the OP gets fancy and needs to utilize events enabled inter-form communication.

in this particular DB, "inter-form communication" won't be necessary. there aren't many forms, and the ones that do exist are simple data entry forms that have no need to interact with eachother at all. granted, i could very well still be completely misunderstanding what you're all talking about, since i'm still pretty much just a novice around here and still don't know much of the terminology you all use a lot of the time.

 

[vbaInet]

References to a particular instance of a form are best done via the object that has been Set to the form. Otherwise if the instances need to be referred to collectively they should be added to their own Collection.

When I use collections like this I set them up as a Class such that I can refer to a member of the collection via the index. As such they can be refered to with equivalent syntax as one would use in the Forms Collection.

I also include a Dicionary object for the index names. This allows the Collection to be searched without using a loop.

Precisely what I would do.

 

[Galaxiom]

At the point you advance to opening Forms by the VBA Class which defines the form, and not via the DoCMD method of opening forms... anticipate needing to convert over to code such as I supplied as Access has a bug where the form name does not get set as the .Key the Form may be referred to with.

This is not a bug! It is by design since multiple instances of the same form obvously cannot share the form name as the items' key in the Forms Collection.

The capability I wanted to was to be able to receive back from forms multiple pieces of information / and to be able to pass in multiple pieces of information. Such (Inter form communication) requires the VBA code to refer to the forms by their class that defines them to Access.

I really have no idea what you are saying here particularly the last sentence. Moreover I don't see how opening the form as an instance makes any difference.

Forms can communicate with each other via their their Properties and Methods. The developer has the option to assign any number of additional Properties and Methods to the form so there is no limit to the pieces of information that can be passed.

I get the impression you overcomplicate much of your coding. You seem to be using classes for objects that never have more than a single instance to contain objects that are quite capable of doing what is required without being encapsulated inside another object.

I did that, then discovered that the .Key value was not being correctly set, thus had to switch to loop based code to process the Forms collection searching for the Form.

As I said before, the loop based code is useless where there is more than one instance of the same form because you cannot distinguish the instances by the Name property.

 

[vbaInet]

This is not a bug! It is by design since multiple instances of the same form obvously cannot share the form name as the items' key in the Forms Collection.

Just to compound on what Galaxiom mentioned here, when you add an object to a dictionary using the Key, it will also return a pointer to the instance of the object which is unique. So when adding an instance of a form to a dictionary you add it as an object, and not try to add the form name in the key. For example:

DictionaryObject.Add FormObject, vbNullstring

 

[mdlueck]

This is not a bug! It is by design since multiple instances of the same form obviously cannot share the form name as the items' key in the Forms Collection.

Back then I had no idea that a Form was itself a class object and multiple instances could be created of it.

I just saw the alternate syntax to open the form as a requirement to get inter-form communication to work... and that sharp spot came up that the .Key was not getting set. At that time, I was being accused in this forum of various things, including having corrupted the database file I was developing for my client. I then thought how to articulate the problem with the least number of words, entered those words into an Internet search, and Praise the Lord one of the top listings was of someone having encountered the exact same thing with Access / VBA. The workaround mentioned was also to spin through the Forms collection to find one with matching name. So, not corrupted after all.

So, I understand that if I were to ever create multiple instances of a form, then my spin through the Forms collection would also break. Operating with known risk since the application only ever creates one instance at a time.