Macro to close all open forms

[Jan Il]

Hi all - Access 2002 XP

I have several forms to which I want to add labels in the footer that can be
used to return to a prior form. I want to create a generic macro that can be
put into the On Click event of each label that will close any open forms
within a specified group. There will be a minimum of 4 forms normally
involved in a navigation function, thus there will be a label for each open
form which will be displayed on the footer of the target form. I want the
macro to find any open forms in the group and close them from any one of the
labels when clicked. There will be a specified group of forms to be searched
for possible closure. Then when any one of the labels is clicked, any forms
in the group that are open, including the target form, will be closed.

Is it possible to create this type of macro? If so, I would truly appreciate
any suggestions or information as to a Help file or tutorial that would
address this type of procedure.

Best regards,
Jan

 

[Ken Snell]

Jan -

Doing this via a macro requires a less than elegant approach, but let's see
if this gets you started.

I assume that each label will be uniquely identified in some way (by name)
and that each label is unique for a group of forms.

If so, then create separate macros for each group of forms, and just string
together a series of Close actions that close each form in the group. If the
form is not open, the macro will continue to the next command without
throwing an error.

Then attach each macro to its specific label's OnClick event.

Or am I missing the real question here?

 

[Jan Il]

Hi Ken!

"> Jan -

Doing this via a macro requires a less than elegant approach, but let's see
if this gets you started.

I assume that each label will be uniquely identified in some way (by name)
and that each label is unique for a group of forms.

Yes, each control will be uniquely identified. But, no, the labels are not
unique for a group, as there is only one forms group.

If so, then create separate macros for each group of forms, and just string
together a series of Close actions that close each form in the group. If the
form is not open, the macro will continue to the next command without
throwing an error.

Then attach each macro to its specific label's OnClick event.

Or am I missing the real question here?

My apologies, I may not have explained as clearly as I should have.
Although, you are very close ;-)

'k..there's going to be only one forms group. It will comprise of all the
forms that the macro will apply to. There needs to be a generic macro that
can be used in all the click events of any and all the labels that will
search the group and close any open forms. One forms group, one macro.

Let's say, one form has 4 labels in the footer. Each label represents a form
that was opened during the navigation process to get to the target (end)
form. When the form is no longer needed, and the user needs to go to another
type of function, they can click on one of the labels that represents an
open form or one they want to return to, let's say, Main Switchboard, and
they can click on that label and it will close the current form, all other
open forms, and open the new target form the user wants to open next. But,
for now, just closing is good.

Is this a bit more clear?

Jan

 

[Ken Snell]

Jan -

Am tied up at the moment with other work...but will get back to you later
today.

 

[Jan Il]

Jan -

Am tied up at the moment with other work...but will get back to you later
today.

NP. Thank you.

Jan

 

[Ken Snell]

Jan -

I think this question ties in with the other one that you asked subsequently
regarding how to have a label know which form had opened the form on which
the label is located? Is that approach taking the place of this one? This
seems to be similar in concept...and the idea that I'd given re: the tag
might be the way to go.

Before I get further into this, can you clarify for me the above? And if
it's different, then let me ask another question....the labels of which you
write are all on one form, and it's the "last" form that has been opened?
How does the form know how many labels to have on it -- how does it know how
many forms were opened already before it was?

 

[Jan Il]

Hi Ken!

Jan -

I think this question ties in with the other one that you asked subsequently
regarding how to have a label know which form had opened the form on which
the label is located? Is that approach taking the place of this one? This
seems to be similar in concept...and the idea that I'd given re: the tag
might be the way to go.

In answer to you question above, to a degree, yes. However, it is not the
same question, the difference is that the other post is in regards to how
the series of labels on each form are assigned the name of a form that is
opened during the navigation process to the last form opened. Each label
will assume the name of each successive form opened, which will produce a
visual trail of all the forms that were opened to get to the target (last)
form. Thus, when the user no longer needs to use the current form, they can
click on any one of the labels in the footer, and it will close out any open
forms, including the current one, and take them directly back to the form in
the label they clicked on. They can then jump from one function menu to
another much faster, or return to any form in the original process to
perform another step in the current function. That is what *that* one is
about.

Before I get further into this, can you clarify for me the above? And if
it's different, then let me ask another question....the labels of which you
write are all on one form, and it's the "last" form that has been opened?
How does the form know how many labels to have on it -- how does it know how
many forms were opened already before it was?

This post is in regards as to how, when the user clicks on the label that
already displays the name of the form assigned to it to go to another form
or function, any open forms in the select group that might still be open,
including the current form, will be closed when the form name in a label is
clicked and opened or returned to.

Let me try to put it another way;

Main Menu > Label 1 (will display this name)
Wayside Insp. Menu > Label 2 (will display this name)
Switch Inspections Filter > Label 3 (will display this name)
Switch Inspection Records > Label 4 (will display this name)

Each of these labels are set up across the footer of each target (last)
form.

To open the form: - Ref the other post:

As each form is opened in the navigation process, each label will
automatically be assigned the name of each form as it is opened in the
process to the target (last) form. The last form opened will not need to be
listed in the trail. It is the target form of the navigation steps. The
labels are not visible on the form until activated by receiving the name of
the form in its line of progress. Example, label 3 will not be visible until
the 3rd form in the process is opened and it assumes the name of the 3rd
form opened. When the next form is opened, form no. 4, then the 4th label
on the form will become visible when it assumes the name of the 4th form
opened, etc.

To close the form - Ref this post:

The user clicks on one of the labels to go to the form name displayed in the
label, and all other forms involved in the navigation process are
automatically closed, including the last form.

What I am trying to do is find a way to close all the other open forms when
the label with a form name is clicked to jump to that form. A generic macro
or code that will close any open forms within a select group of forms that
will be part of the navigation processes for the various functions to be
performed. The user must walk in, step by step..with each step represented
by each one of the labels in the footer of the end (target) form...but, the
user can jump out to any previous location by clicking on any one of the
labels.

Now to this part of your question:

How does the form know how many labels to have on it -- how does it know how

many forms were opened already before it was?

I don't know yet. ;-)) I may be totally off the board here, because I have
never done anything like this before, and I am not that experienced with
Access to know for sure, but, the target form itself should not care about
the other forms, as the actions will involve only the labels on it's footer,
and other than close the form when the user jumps to another form, the
current form will not be impacted in any way by the activities taking place
with the labels. There is no function with the labels that will control any
functions of the form, except to close it. Once the form is closed, the
labels are invisible again. Plus, each form involved with have the required
number of labels placed in the footers. Most will have 4 labels, a few will
have six. Each label will represent a form in the steps necessary to open
the last (target form).

Additionally, I don't *think* you can define a specific button or control in
the process, as there are several buttons on a filter form that can open the
same form to display different information, and the user will not return to
a button, but to a form. However, OTOH, hold it, wait a minute... this
*may* be the case, as, even though various buttons can be used to open the
same form, they are all on the same form, and they all have unique names.
So, let's say, button cmdDept on the filter form is used to open the form,
then the name of the form will still be shown in the label on the opened
form, but, the button name may have been used by the macro to find the name
of the filter form to put in the label. Yes? No?

I truly am sorry this is a bit long, but, it is very complicated and
complex, at least for me, and I am trying to explain as best I can. I don't
have all the fine tuning of the how's and where's lined out yet, as they are
beyond my abilities. But, I know the end result I am looking for is doable.
Maybe not in the manner I am heading at this time, but, I am doing some
s'posing on some of this, sampling, testing theories and various
possibilities, and perhaps a macro is not the right way to go. The other
post and this one are part of an over all event, but, with different
processes and procedures, perhaps requiring different handling. Sorry, I
just wasn't sure where I should post it not knowing what might be needed.

I hope this is clear enough for you to undertand what I am aattempting to
do. I apologize for not explaining in a more detailed or concise manner.

Thank you very much for your time and assistance, I truly appreciate it.

Jan

 

[Jim/Chris]

You could try using code instead. Put this on the OnClick
event for a button on one of the forms

' Close all open forms
dim lngX as integer
For lngX = Forms.Count - 1 To 0 Step -1
With Forms(lngX)
DoCmd.Close acForm, .Name
End With
Next lngX

Good luck

Jim

 

[Jan Il]

You could try using code instead. Put this on the OnClick
event for a button on one of the forms

' Close all open forms
dim lngX as integer
For lngX = Forms.Count - 1 To 0 Step -1
With Forms(lngX)
DoCmd.Close acForm, .Name
End With
Next lngX

Good luck

Jim

 

[Jan Il]

Hi Jim!

You could try using code instead. Put this on the OnClick
event for a button on one of the forms

' Close all open forms
dim lngX as integer
For lngX = Forms.Count - 1 To 0 Step -1
With Forms(lngX)
DoCmd.Close acForm, .Name
End With
Next lngX

Good luck

This works very well....however, that it closes ALL open forms. ;-) What is
needed it that it needs to close only specific forms that are open in
relation to the target form. I have a table with a all the form names that
will be associated with the various functions. These forms will be opened
during the navigation processes involved in a particular function. Example;

I want to check the status of all switch inspections that are due for the
month of Feb. I open the db, the first form opened is the Opening form. I
click the master command button that takes me to the Main Switchboard. These
two forms will *always* remain open as long as I am working in the db. On
the Main Menu are several buttons that indicate various functions. I select
the one for Wayside Inspections. That opens the next form, Wayside
Inspections Menu. Here I select the button for Switches. This opens the
Wayside Switch Insp Filter form. Here there are 4 buttons associated with
combo boxes for Equipment, Location, Line, Territory, Section. As well as a
From - To sorting process. By making a selection in any of the combo boxes,
or entering a specific date range, and clicking the related buttons, it
opens the Wayside Switch Inspection Form. The last form to be opened in this
function. As you can see, this form can be opened by a series of different
buttons from the filter form.

Main Menu >
Wayside Inspections Menu >
Wayside Switch Insp Filter >
Wayside Switch Inspection Form >

There are now 6 forms open;

Opening form
Main Switchboard
Main Menu
Wayside Inspections Menu
Wayside Switch Insp Filter
Wayside Switch Inspection Form

Only 4 of them will be listed in the Forms table, the last 4. The first two
will always remain active, and are not in the table. Thus, the procedure
will need to search the table of form names to know which open forms are ok
to close when the label is clicked, and open the desired 'go to' form.
Actually, the process is fairly simple. Making it work...ahmm...maybe not
so.<g>

I now have the navigation system set up to close the previous form when the
new form is opened during the steps for each function, thus, there are never
any open forms except target, or last, form opened. When the user navigates
back to another form, each form is opened, and the one behind it closed at
the same time. Keeps the db running easier. But, not all users like the idea
of all forms being closed in this manner. So....I have to plan for the
outset. ;-)

I hope this may explain a bit better. But, I have already found a use for
your code in another db. Cool!. ;-))

Thank you very much for your time and additional assistance and the great
code. I truly appreciate it.

Jan

 

[Ken Snell]

An interesting challenge.

I think we should bypass macros entirely for this and go to VBA code. It's
much more suitable for what I am going to suggest.

Essentially, what I'm envisioning is code that loops through the labels in
the form's footer, checks to see if the Visible property is Yes, and if Yes,
closes the form whose name is in the label's caption; except, of course, the
form whose "label" was clicked.

So, assuming that I'm on the right track, let's start with a subroutine that
will loop through the labels. Let's also assume that you've used a standard
naming convention for these labels (all start with "lblFooter" and have a
number after that; thus, "lblFooter00", "lblFooter01", etc.). When you call
the sub, you'll pass to it the name of the label that is calling it and the
form object that's calling it (this assumes that you'll want this subroutine
to be used by numerous forms, so we'll put it in a regular module)


Public Sub CloseOpenAssociatedForms(frmForm As Form, strLabelName As String)
Dim ctl As Control
Dim strName As String
Const strLabelTemplate As String = "lblFooter"
For Each ctl in frmForm.Controls
If ctl.ControlType = acLabel Then
If Left(ctl.Name, Len(strLabelTemplate)) = strLabelTemplate Then
If ctl.Name <> strLabelName Then
DoCmd.Close acForm, Forms(ctl.Caption)
Else
DoCmd.OpenForm Forms(ctl.Caption)
End If
End If
End If
Next ctl
End Sub


Then, in the OnClick event of each label in the form's footer, use code
similar to this:

Private Sub lblFooter00_Click()
Call CloseOpenAssociatedForms(Me, Me.lblFooter00.Name)
End Sub


I hope that this gives you some ideas!

 

[Ken Snell]

Jan, I left out one thing (checking for visibility) in the code that I just
posted.

Change the code for CloseOpenAssociatedForms subroutine to this:

Public Sub CloseOpenAssociatedForms(frmForm As Form, strLabelName As String)
Dim ctl As Control
Dim strName As String
Const strLabelTemplate As String = "lblFooter"
For Each ctl in frmForm.Controls
If ctl.ControlType = acLabel Then
If Left(ctl.Name, Len(strLabelTemplate)) = strLabelTemplate And _
ctl.Visible = True Then
If ctl.Name <> strLabelName Then
DoCmd.Close acForm, Forms(ctl.Caption)
Else
DoCmd.OpenForm Forms(ctl.Caption)
End If
End If
End If
Next ctl
End Sub

 

[Ken Snell]

And, of course, just to test the "three strikes" rule, there's another typo
in the sub's code. (sigh) Here is corrected version:

Public Sub CloseOpenAssociatedForms(frmForm As Form, strLabelName As String)
Dim ctl As Control
Dim strName As String
Const strLabelTemplate As String = "lblFooter"
For Each ctl in frmForm.Controls
If ctl.ControlType = acLabel Then
If Left(ctl.Name, Len(strLabelTemplate)) = strLabelTemplate And _
ctl.Visible = True Then
If ctl.Name <> strLabelName Then
DoCmd.Close acForm, ctl.Caption
Else
DoCmd.OpenForm ctl.Caption
End If
End If
End If
Next ctl
End Sub

 

[Jan Il]

An interesting challenge.

Yeah....fun, huh! ;-))

I think we should bypass macros entirely for this and go to VBA code. It's
much more suitable for what I am going to suggest.

'k....kinda thought it might migrate in that direction, but, macros are my

Essentially, what I'm envisioning is code that loops through the labels in
the form's footer, checks to see if the Visible property is Yes, and if Yes,
closes the form whose name is in the label's caption; except, of course, the
form whose "label" was clicked.

Yes...this is the plan precisely. My, you catch on quick! ;-))

So, assuming that I'm on the right track, let's start with a subroutine that
will loop through the labels. Let's also assume that you've used a standard
naming convention for these labels (all start with "lblFooter" and have a
number after that; thus, "lblFooter00", "lblFooter01", etc.). When you call
the sub, you'll pass to it the name of the label that is calling it and the
form object that's calling it (this assumes that you'll want this subroutine
to be used by numerous forms, so we'll put it in a regular module)

'k...you're right on track....and I have changed the names of the labels to
the same format you have here, this to make sure we are both talking the
same turkey and on the same foot-ers. You are also correct that this will
be a format that will be used on numerous other forms in the db, so keeping
it generic should allow for this. QUESTION: Is it possible to use the same
labels for each form? By that I mean, since all will be generic, once the
proper format is set up in the test form, would it be possible/wise to
copy/paste the labels from the test form to each of the other forms that
will be using this same function? Just a question for clarity.

Since you have posted a revised code in your other message, I'm going to
continue my response in that message from here, as it will pertain to the
code in your other message.

Jan......>>>>>>>>>>>>

Public Sub CloseOpenAssociatedForms(frmForm As Form, strLabelName As String)
Dim ctl As Control
Dim strName As String
Const strLabelTemplate As String = "lblFooter"
For Each ctl in frmForm.Controls
If ctl.ControlType = acLabel Then
If Left(ctl.Name, Len(strLabelTemplate)) = strLabelTemplate Then
If ctl.Name <> strLabelName Then
DoCmd.Close acForm, Forms(ctl.Caption)
Else
DoCmd.OpenForm Forms(ctl.Caption)
End If
End If
End If
Next ctl
End Sub


Then, in the OnClick event of each label in the form's footer, use code
similar to this:

Private Sub lblFooter00_Click()
Call CloseOpenAssociatedForms(Me, Me.lblFooter00.Name)
End Sub


I hope that this gives you some ideas!

Private Sub lblFooter00_Click()

 

[Jan Il]

con't. from previous message:

Jan, I left out one thing (checking for visibility) in the code that I just
posted.

Change the code for CloseOpenAssociatedForms subroutine to this:

Public Sub CloseOpenAssociatedForms(frmForm As Form, strLabelName As String)
Dim ctl As Control
Dim strName As String
Const strLabelTemplate As String = "lblFooter"
For Each ctl in frmForm.Controls
If ctl.ControlType = acLabel Then
If Left(ctl.Name, Len(strLabelTemplate)) = strLabelTemplate And _
ctl.Visible = True Then
If ctl.Name <> strLabelName Then
DoCmd.Close acForm, Forms(ctl.Caption)
Else
DoCmd.OpenForm Forms(ctl.Caption)
End If
End If
End If
Next ctl
End Sub

I cut and pasted the above code into a new regular module, then I copied and
pasted the following code in the On Click events of each label in the form
footer, as you instructed in part 1.
**************************
Private Sub lblFooter00_Click()
Call CloseOpenAssociatedForms(Me, Me.lblFooter00.Name)
End Sub
**************************
However, when I did the compile I got the following error message:

Compile error
Expected variable or precedure -
not module

The part of the code that was highlighted was in the labels;

Private Sub lblFooter00_Click()

So..I must have made an error somewhere along the way. What have I
overlooked or not done correctly? Should I have changed something in the
code, such as given something a proper name somewhere? I have read through
it all to see if there is something somewhere that should have had the
correct name or such replaced, but, I just can't seem to see it. ?

Thank you very much for your help, this really is an excellent start. I
truly appreciate all your time and help.

Jan

 

[Jan Il]

"> And, of course, just to test the "three strikes" rule, there's another
typo

in the sub's code. (sigh) Here is corrected version:

Public Sub CloseOpenAssociatedForms(frmForm As Form, strLabelName As String)
Dim ctl As Control
Dim strName As String
Const strLabelTemplate As String = "lblFooter"
For Each ctl in frmForm.Controls
If ctl.ControlType = acLabel Then
If Left(ctl.Name, Len(strLabelTemplate)) = strLabelTemplate And _
ctl.Visible = True Then
If ctl.Name <> strLabelName Then
DoCmd.Close acForm, ctl.Caption
Else
DoCmd.OpenForm ctl.Caption
End If
End If
End If
Next ctl
End Sub

Are 'we' quite done now?


.... ;-)))

 

[Ken Snell]

First to answer your question:
"QUESTION: Is it possible to use the same
labels for each form? By that I mean, since all will be generic, once the
proper format is set up in the test form, would it be possible/wise to
copy/paste the labels from the test form to each of the other forms that
will be using this same function? Just a question for clarity."

ANSWER: Yes. You can use the same label setup and naming convention in each
form.

Now, on to the compiler error:
From your statement "...then I copied and pasted the following code in the
On Click events of each label in the form footer, as you instructed in part
1.", I surmise that you pasted the code into the box next to the "On Click"
event name in the Properties window in the form's design view? If yes, this
is the wrong place to put it.

Delete all that text from that box. Instead, in that box, select from the
dropdown list "[Event Procedure]". Then click the "three-dots" box at far
right of the textbox. The Visual Basic Editor will open so that we can
insert the correct code.

When the VBE opens, you'll see three lines of "code":

Private Sub lblFooter00_Click()

End Sub

The cursor will be on line 2, which is blank.

Paste the second line of the code that I'd given to you onto this second
line. Then you should see the entire code as I posted it. Assuming that you
do, repeat this process for all the labels' On Click events.

Then you should be ready to test the setup.

Last, in answer to "Are 'we' quite done now? ... ;-))) ":
Yes, I think so! < g >

 

[Jan Il]

"> First to answer your question:

"QUESTION: Is it possible to use the same
labels for each form? By that I mean, since all will be generic, once the
proper format is set up in the test form, would it be possible/wise to
copy/paste the labels from the test form to each of the other forms that
will be using this same function? Just a question for clarity."

ANSWER: Yes. You can use the same label setup and naming convention in each
form.

Ah! Simplicity...it does a database good!

Now, on to the compiler error:
From your statement "...then I copied and pasted the following code in the
On Click events of each label in the form footer, as you instructed in part
1.", I surmise that you pasted the code into the box next to the "On Click"
event name in the Properties window in the form's design view? If yes, this
is the wrong place to put it.

Delete all that text from that box. Instead, in that box, select from the
dropdown list "[Event Procedure]". Then click the "three-dots" box at far
right of the textbox. The Visual Basic Editor will open so that we can
insert the correct code.

When the VBE opens, you'll see three lines of "code":

Private Sub lblFooter00_Click()

End Sub

The cursor will be on line 2, which is blank.

Paste the second line of the code that I'd given to you onto this second
line. Then you should see the entire code as I posted it. Assuming that you
do, repeat this process for all the labels' On Click events.

Then you should be ready to test the setup.

Yes, I did put it in the VBE (...) part. Code like that usually does not go
on the line On Click Event line. But, It's good that you double checked
with me on that. ;-) The part that the debugger highlighted in yellow was
the line I mentioned before, but, the other part in the code that was
darkened was this part.

Call CloseOpenAssociatedForms

Not sure why. But, lem'me check with you. I gave the module the name
CloseOpenAssociatedForms. I did this, as I thought that as the part of the
code in the label appeared to be calling this, that was the name the module
should be. Is this correct? If not, my assumption was wrong and this may be
the problem.

Last, in answer to "Are 'we' quite done now? ... ;-))) ":
Yes, I think so! < g >

<vbg>

Jan

 

[Ken Snell]

A module cannot have the same name as a subroutine or a function in it.
Rename the module to something like basFunctionSub.

--
Ken Snell
<MS ACCESS MVP>

"> First to answer your question:

"QUESTION: Is it possible to use the same
labels for each form? By that I mean, since all will be generic, once the
proper format is set up in the test form, would it be possible/wise to
copy/paste the labels from the test form to each of the other forms that
will be using this same function? Just a question for clarity."

ANSWER: Yes. You can use the same label setup and naming convention in each
form.

Ah! Simplicity...it does a database good!

Now, on to the compiler error:
From your statement "...then I copied and pasted the following code in the
On Click events of each label in the form footer, as you instructed in part
1.", I surmise that you pasted the code into the box next to the "On Click"
event name in the Properties window in the form's design view? If yes, this
is the wrong place to put it.

Delete all that text from that box. Instead, in that box, select from the
dropdown list "[Event Procedure]". Then click the "three-dots" box at far
right of the textbox. The Visual Basic Editor will open so that we can
insert the correct code.

When the VBE opens, you'll see three lines of "code":

Private Sub lblFooter00_Click()

End Sub

The cursor will be on line 2, which is blank.

Paste the second line of the code that I'd given to you onto this second
line. Then you should see the entire code as I posted it. Assuming that you
do, repeat this process for all the labels' On Click events.

Then you should be ready to test the setup.

Yes, I did put it in the VBE (...) part. Code like that usually does not go
on the line On Click Event line. But, It's good that you double checked
with me on that. ;-) The part that the debugger highlighted in yellow was
the line I mentioned before, but, the other part in the code that was
darkened was this part.

Call CloseOpenAssociatedForms

Not sure why. But, lem'me check with you. I gave the module the name
CloseOpenAssociatedForms. I did this, as I thought that as the part of the
code in the label appeared to be calling this, that was the name the module
should be. Is this correct? If not, my assumption was wrong and this may be
the problem.

Last, in answer to "Are 'we' quite done now? ... ;-))) ":
Yes, I think so! < g >

<vbg>

Jan

And copied
and

form

on out
any menu
to one above?
And need steps.
The the
name the
3rd 4th
form displayed to of
the it
know

because

I experienced
with closed,
the can
open

can.

I as
they Sorry, appreciate
it. approach,
but go form,
all form. forms

will

be

 

[Jan Il]

Hi Ken,

A module cannot have the same name as a subroutine or a function in it.
Rename the module to something like basFunctionSub.

Sorry...my bad, the brain went to recess before class was dismissed. I
completely forgot about that part of the naming thingies. I've changed the
module name to basFunctionSub as you instructed. Now when I click the label
on the form the debugger comes up with the following error;

Run-time error '2102':
The form name " is misspelled or refers to a Form that doesn't exist.

The part of the code in the module that is highlighted in yellow is the line
below indicated by the < > (included here only to show the line in
question, and are not included in the module code) in this part of the code.
I'm not sure what it is pitching a fit about here.
***
Else
< DoCmd.OpenForm ctl.Caption >
End If
End If
End If
Next ctl
End Sub
***
Thank you very much.

Jan

<snip>
-
-
Current code in module below for reference;

(And, of course, just to test the "three strikes" rule, there's another typo
in the sub's code. (sigh) Here is corrected version:

************************Start if code********************************
Public Sub CloseOpenAssociatedForms(frmForm As Form, strLabelName As String)
Dim ctl As Control
Dim strName As String
Const strLabelTemplate As String = "lblFooter"
For Each ctl in frmForm.Controls
If ctl.ControlType = acLabel Then
If Left(ctl.Name, Len(strLabelTemplate)) = strLabelTemplate And _
ctl.Visible = True Then
If ctl.Name <> strLabelName Then
DoCmd.Close acForm, ctl.Caption
Else
DoCmd.OpenForm ctl.Caption
End If
End If
End If
Next ctl
End Sub