Module For Load UnLoad Forms

[Gethzerion]

K, The database I'm currently building is growing at an exponential rate, and I'm bored of typing in the same code to check if a form is loaded before opening it, and then closing down the old form. So I decided to write a vba script to do this for me

Option Compare Database
Option Explicit


Private Sub Command0_Click()

Call Form_Switch(Forms![frm_Test_One], Forms![frm_Test_Two])

End Sub

Private Sub Form_Switch(FormIn As Form, FormOut As Form)

If CurrentProject.AllForms(FormIn).IsLoaded = False Then

    DoCmd.OpenForm FormIn, acNormal
    
Else

    Forms![FormIn].Visible = True
    
End If

DoCmd.Close acForm, FormOut, acSavePrompt

End Sub

Now this doesn't work as I keep getting the message that access cannot find the form to be opened.

Any suggestions? Using Access 2000 btw.

 

[OlcayM]

Hi,

There is a small mistake in your code. I believe, it will be solved after you change;

WRONG Call Form_Switch(Forms![frm_Test_One], Forms![frm_Test_Two])
RİGHT Call Form_Switch("frm_Test_One", "frm_Test_Two")

HTH
Olcaym

 

[Gethzerion]

Hmm sorry, that doesn't work.

I get a compile error - "Type Mismatch" pointing at the call statement.

 

[OlcayM]

Hi Again,

Change Form_Switch parameters to STRING.
I am using a similar function with the same name IsLoaded. It is below;

Function IsLoaded(ByVal FormName As String) As Boolean
On Error Resume Next
If SysCmd(acSysCmdGetObjectState, acForm, FormName ) <> 0 Then IsLoaded= True
End Function

With this function it works.

OlcayM

 

[GolferGuy]

If CurrentProject.AllForms(FormIn).IsLoaded = False Then

To reference a single object within a collection (AllForms is a collection) you either refer to the index like Allforms(0) or AllForms(1), etc. or you refer to the single object by its name, like AllForms("MyFormName"). The format you have choosen is this second method. A form's name is a string, therefore it can not be refered as a Form object as you defined the input parameters in your function. As OlcayM said, they need to be defined as String. I just thought you might like to know why.

 

[Gethzerion]

Hi Folks,

Yeah, To both GolferGuy and OlcayM, I twigged the issue with defining it as a string rather than as an object, specifically as a form, as I drove home. Then I wondered why I had set it to be an object.

Changing the received variable to type string makes both my method and OlcayM's versions work with regards to checking if the form is loaded. However, none of the versions work when either the form is hidden = True or visible = false, I *think* because we're referring to the form as an object:

Forms![FormIn].Visible = true

Its this line that is now my bugbear. The error message I keep getting is that it cannot find the form FormIn, so I'm guessing I need to declare both FormIn and FormOut as an object or as a Form. But, I cannot find the correct syntax to do this either in the routine that calls the sub routine nor in the sub routine itself.

Any further help would be greatly appreciated.

 

[Guus2005]

You shouldn't use Call in current versions of VBA (from Acc97 up)

When using the declaration

dim FormIn as Form

and there is a string expected: use FormIn.name:

DoCmd.OpenForm FormIn.name, acNormal

FormIn.name doesn't necessarily have to be "FormIn". Could also be "frmSwitchboard" or whatever.

The code:

FormIn.visible = true

is also accepted.

Don't know why you are using the AllForms collection:

Private Sub Form_Switch(FormIn As Form, FormOut As Form)

If IsLoaded(FormIn.name) Then
    DoCmd.OpenForm FormIn.name, acNormal    
Else
    Forms![FormIn.name].Visible = True    '?
End If

DoCmd.Close acForm, FormOut.name, acSavePrompt

End Sub

Public Function IsLoaded(sFormName As String) As Boolean
    Dim I As Integer
    IsLoaded = False
    For I = 0 To Forms.Count - 1
        If Forms(I).Name = sFormName And Forms(I).Visible Then
            IsLoaded = True
            Exit Function
        End If
    Next
End Function

Enjoy!

 

[GolferGuy]

As I said before, you need the form names to be strings, not form objects. I did miss this line though.

  Forms![FormIn].Visible = True

This line is incorrect. Again, when dealing with a collection, and Forms is also a collection, so instead of the syntax above, which is looking for a form object, use this syntax which is looking for a form name.

  Forms([FormIn].Visible = True

So, here is the whole routine. Please note I have removed the Private before the Sub. This routine should be in a general module rather than in a form module. That way, it can be called from anywhere, rather than just the form you are currently working from.

Sub Form_Switch(FormIn As String, FormOut As String)

If CurrentProject.AllForms(FormIn).isLoaded = False Then
  DoCmd.OpenForm FormIn, acNormal
Else
  Forms([FormIn]).Visible = True
End If

DoCmd.Close acForm, FormOut, acSavePrompt

End Sub

 

[Gethzerion]

Thanks again guys, I realy appreciate the help.

At the risk of sidetracking this thread can I ask:

(1) Why shouldn't we use the 'Call' statement? Can anyone point me to a knowledge base article or similar on this?

(2) What is the drawback to using "AllForms" compared to looping through all the forms?

 

[GolferGuy]

Gethzerion,
Did my last post work for you?
I have no idea why Guus2005 said you should not use Call. I know it is not needed, but I still use it sometimes, but I have never seen anything that said one should not use it.
I have also have no idea why someone would say that AllForms should not be used. It is a Collection that has been supplied for us to use, and I sure would think it works much quicker than trying to loop through all the forms with our own VBA code.

 

[Gethzerion]

To: GolferGuy

Yes the code you posted does appear to work. No error messages either in comiling or operation.

So many thanks )

 

[Guus2005]

Why would you use Call when it is not necessary?

The code in Post #8 by Golferguy should work since the input parameters are replaced by strings.

CurrentProject.AllForms gives you all the forms. Forms gives you all the open forms. Which means the looping through Forms should finish faster especially if you are looking for open forms.


Enjoy!

 

[Gethzerion]

OK, understood. Many thanks to all of you for your help.

 

[GolferGuy]

Guus2005,
I think one of my posts got lost out here somewhere too. So here it what I think I said, plus a little research results too.
There is no need to use Call, but sometimes I do, sometimes I don't. If you use Call, you must put the parameters inside parens "()", if you don't use Call, the parameters MUST not be inside of parens. It's just that sometimes, for no reason, I just use Call
Now, about the speed of Forms vs. AllForms. I timed both Forms! and AllForms by checking to see if a form was loaded. I used a loop to do this 3000 times. You really can't tell any difference in speed using a clock (the Time() function), so I used GetTickCount which I found on the web:

'In general section
Private Declare Function GetTickCount& Lib "kernel32" ()
Function MyGetTickCount()
    'KPD-Team 1998
    'URL: http://www.allapi.net/
    'E-Mail: KPDTeam@Allapi.net
    'Get the tickcount
    MyGetTickCount = GetTickCount&
End Function

Drum roll please!
If the form is loaded, Forms! used 1250 milliseconds and AllForms used 1438.
If the form is not loaded, Forms! used 94 milliseconds and AllForms used 1343.
So, Forms! is MUCH faster if the form is NOT loaded, and right at the same if the form is loaded. But, these are milliseconds used for 3000 IsLoaded() function calls. Therefore, the human user can not tell the difference in time taken no matter which is used. The milliseconds used for this same test, but with only one check of the form, is 0. That is, the measuring tool I used could not tell any time lapse. That was with the form loaded and not loaded.

 

[Guus2005]

I rest my case!

Thanks for the explanation!