Determine What Command Button Opens A Form

[Steve]

I am working on a database that has a main menu, many sub-menus and some
sub-sub-menus. They are all forms that have numerous command buttons on them
to open forms and reports in the database. The
database has hundreds of forms and reports. I was asked to go through all
the menu forms and determine if all the buttons worked, if there were any
problems when either the form or report opened and to come up with
a list of the forms and reports that were no longer used in the database. I
started this project by printing a list of the forms and reports in the
database. As I went through each menu, I systematically clicked each button,
determined the name of the form or report the button opened and then cheched
off the name in my list. I discovered there are many forms and reports with
various problems and I also marked those in my list. I'm done with all that.
The problems need fixed. I know the name of each form or report with a
problem from my list but I wasn't thinking. I did not note how to navigate
to the form or report. I need to create a list of all the poroblem forms in
the database and for each form show which menu form to go to and which
button on that menu form opens the form with a problem. For example:
MyForm MyMenuForm Cmd1032
HisForm HisMenuForm Cmd1243
HerForm MyMenuForm Cmd17

Is there a way to programatically create this list?

Thanks!

Steve

[Salad]

Re: Determine What Command Button Opens A Form

Steve wrote:

I am working on a database that has a main menu, many sub-menus and some
sub-sub-menus. They are all forms that have numerous command buttons on them
to open forms and reports in the database. The
database has hundreds of forms and reports. I was asked to go through all
the menu forms and determine if all the buttons worked, if there were any
problems when either the form or report opened and to come up with
a list of the forms and reports that were no longer used in the database. I
started this project by printing a list of the forms and reports in the
database. As I went through each menu, I systematically clicked each button,
determined the name of the form or report the button opened and then cheched
off the name in my list. I discovered there are many forms and reports with
various problems and I also marked those in my list. I'm done with all that.
The problems need fixed. I know the name of each form or report with a
problem from my list but I wasn't thinking. I did not note how to navigate
to the form or report. I need to create a list of all the poroblem forms in
the database and for each form show which menu form to go to and which
button on that menu form opens the form with a problem. For example:
MyForm MyMenuForm Cmd1032
HisForm HisMenuForm Cmd1243
HerForm MyMenuForm Cmd17
>
Is there a way to programatically create this list?
>
Thanks!
>
Steve
>

No.

You asked this question before. You didn't like the solution using
OpenArgs.

[Albert D. Kallal]

Re: Determine What Command Button Opens A Form

All you have to do is iterate the commandbars collection and print out the
report/form that the button calls.

It not hard to do this. the only thing here is do you want the listing by
menu bars, or simply grouped by reports? (if you send the resulting data to
a table, then you can report grouped by each form....

The basic code will look like:

Sub ListMyBars()

Dim cbar As CommandBar

For Each cbar In CommandBars
Call DisplayControls (cbar)
Next

End Sub

Sub DisplayControls (cbar As CommandBar)

Dim cControl As CommandBarContr ol

If cbar.BuiltIn = False Then
For Each cControl In cbar.Controls
Debug.Print cControl.Captio n & _
" Action = " & cControl.OnActi on & _
" form/reprot = " & cControl.Parame ter

If cControl.Type = 10 Then
Debug.Print cControl.Captio n & "---->"
Call DisplayControls (cControl.Comma ndBar)
End If
Next
End If

End Sub


The above is recursive and "calls" itself since menu bars can go "many"
levels deep. I would likely add a 4-5 more lines of code to send the above
data to a reocrdset. You thus can then run a report grouped by form/report.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKa llal@msn.com

[Albert D. Kallal]

Re: Determine What Command Button Opens A Form

"Salad" <oil@vinegar.co mwrote in message
news:wuOdncXFGL T3N0vVnZ2dnUVZ_ t3inZ2d@earthli nk.com...

>HerForm MyMenuForm Cmd17
>>
>Is there a way to programatically create this list?
>>
>Thanks!
>>
>Steve

No.
>
You asked this question before. You didn't like the solution using
OpenArgs.

NO NO NO!

He is not asking to fix/solve a coding problem. He is asking how can he
generate a list for documentation purposes that displays what custom menu
bar
button calls what form or report. This is a grand canyon DIFFERENT of a
problem. See my response for the solution....

He is not trying to modify existing code, nor is he trying have the existing
code "know" what button called the code, he simply wants a list of his
custom menu bars showing what buttons on those custom menu bars call what
forms and reports...


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKa llal@msn.com

[Marshall Barton]

Re: Determine What Command Button Opens A Form

Steve wrote:

>I am working on a database that has a main menu, many sub-menus and some
>sub-sub-menus. They are all forms that have numerous command buttons on them
>to open forms and reports in the database. The
>database has hundreds of forms and reports. I was asked to go through all
>the menu forms and determine if all the buttons worked, if there were any
>problems when either the form or report opened and to come up with
>a list of the forms and reports that were no longer used in the database. I
>started this project by printing a list of the forms and reports in the
>database. As I went through each menu, I systematically clicked each button,
>determined the name of the form or report the button opened and then cheched
>off the name in my list. I discovered there are many forms and reports with
>various problems and I also marked those in my list. I'm done with all that.
>The problems need fixed. I know the name of each form or report with a
>problem from my list but I wasn't thinking. I did not note how to navigate
>to the form or report. I need to create a list of all the poroblem forms in
>the database and for each form show which menu form to go to and which
>button on that menu form opens the form with a problem. For example:
>MyForm MyMenuForm Cmd1032
>HisForm HisMenuForm Cmd1243
>HerForm MyMenuForm Cmd17

I suspect that you are using the word "menu" generically
when you really mean a form with a bunch of command buttons.

If so, the this might get you going:

Public Sub FindDoCmds()
Dim CP As CodeProject
Dim ao As AccessObject
Dim mdl As Module
Dim bolMatch As Boolean
Dim lngStart As Long
Set CP = CodeProject
For Each ao In CP.AllForms
If CP.AllForms(ao. Name).IsLoaded _
Then DoCmd.Close acForm, ao.Name, acSaveNo

DoCmd.OpenForm ao.Name, acDesign
With Forms(ao.Name)
If .HasModule Then
With .Module
lngStart = 0
Do
lngStart = lngStart + 1
bolMatch = .Find("DoCmd.Op enForm", _
lngStart, 100000, 1, 1000)
If bolMatch Then
Debug.Print ao.Name; Spc(4); _
.ProcOfLine(lng Start, vbext_pk_Proc); _
Spc(4); .Lines(lngStart , 1)
End If
Loop While bolMatch
End With
End If
End With
DoCmd.Close acForm, ao.Name, acSaveNo
Next ao
Set CP = Nothing
End Sub

--
Marsh
MVP [MS Access]

[Steve Schapel]

Re: Determine What Command Button Opens A Form

Albert,

Excuse me if I have missed the meaning here too. But I have understood
that Steve is using to the word "menu" not to refer to a menu as in menu
bar, but menu as in a form with a bunch of command buttons. So he has a
number of forms, each with a number of command buttons, and it looks
like the command buttons are not well named. And it looks like each
command button is associated with opening only one form or report. So
he is asking for a way to programmaticall y make a list of the command
buttons to show which form they are on, and which form/report they open.

--
Steve Schapel, Microsoft Access MVP


Albert D. Kallal wrote:

All you have to do is iterate the commandbars collection and print out the
report/form that the button calls.
>
It not hard to do this. the only thing here is do you want the listing by
menu bars, or simply grouped by reports? (if you send the resulting data to
a table, then you can report grouped by each form....
>
The basic code will look like:
>
Sub ListMyBars()
>
Dim cbar As CommandBar
>
For Each cbar In CommandBars
Call DisplayControls (cbar)
Next
>
End Sub
>
Sub DisplayControls (cbar As CommandBar)
>
Dim cControl As CommandBarContr ol
>
If cbar.BuiltIn = False Then
For Each cControl In cbar.Controls
Debug.Print cControl.Captio n & _
" Action = " & cControl.OnActi on & _
" form/reprot = " & cControl.Parame ter
>
If cControl.Type = 10 Then
Debug.Print cControl.Captio n & "---->"
Call DisplayControls (cControl.Comma ndBar)
End If
Next
End If
>
End Sub
>
>
The above is recursive and "calls" itself since menu bars can go "many"
levels deep. I would likely add a 4-5 more lines of code to send the above
data to a reocrdset. You thus can then run a report grouped by form/report.
>
>

[Salad]

Re: Determine What Command Button Opens A Form

Albert D. Kallal wrote:

"Salad" <oil@vinegar.co mwrote in message
news:wuOdncXFGL T3N0vVnZ2dnUVZ_ t3inZ2d@earthli nk.com...
>

>>>HerForm MyMenuForm Cmd17
>>>
>>>Is there a way to programatically create this list?
>>>
>>>Thanks!
>>>
>>>Steve

>>
>>No.
>>
>>You asked this question before. You didn't like the solution using
>>OpenArgs.

>
>
NO NO NO!
>
He is not asking to fix/solve a coding problem. He is asking how can he
generate a list for documentation purposes that displays what custom menu
bar
button calls what form or report. This is a grand canyon DIFFERENT of a
problem. See my response for the solution....
>
He is not trying to modify existing code, nor is he trying have the existing
code "know" what button called the code, he simply wants a list of his
custom menu bars showing what buttons on those custom menu bars call what
forms and reports...
>
>

Hi Albert. I thought he was talking about forms and command buttons
that open a form/report. I didn't think from reading his post, or his
post a week ago, had anything to do with commandbars, menubars,
toolbars, shortcuts, or whatever.

[Albert D. Kallal]

Re: Determine What Command Button Opens A Form

"Steve Schapel" <schapel@mvps.o rg.nswrote in message
news:%235AqpqCH JHA.4272@TK2MSF TNGP03.phx.gbl. ..

Albert,
>
Excuse me if I have missed the meaning here too.

I agree, it would be sooooo simple if you were aksing


"Can I get a list of menus that show what forms/reprots they call/open?"

REally, a VERY simple question. However, from the context, we get:

>MyForm MyMenuForm Cmd1032
>HisForm HisMenuForm Cmd1243
>HerForm MyMenuForm Cmd17

So, I have to wait for the original poster to clarify what they are looking
for..but, there is a "real" confusing as to if we taking about custom menu
bars, or in fact regular plain controls on form which ARE NOT what we call
menus.

To be fair, looking at the above, the naming convention by access "defaults"
suggests you have this context correct..and I am 100% dead wrong on my view
of his question...

So, to the original poster, you are creating a LARGE amount of confusing
here by not distinguishing if you are taking about custom menus and menu
bars, or simply that of buttons on a form. We don't call command buttons on
a form a menu, or a menu bar....

(a grand canyon of difference here).

So, you have to clarify what you talking about now, we are EXTREMELY and
THOROUGHLY confused here...

Are you taking about custom menus (and menu bars), or just buttons on a
form?


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKa llal@msn.com

[Albert D. Kallal]

Re: Determine What Command Button Opens A Form

"Albert D. Kallal" <PleaseNOOOsPAM mkallal@msn.com wrote in message
news:O96e%23zCH JHA.1304@TK2MSF TNGP02.phx.gbl. ..

"Steve Schapel" <schapel@mvps.o rg.nswrote in message
news:%235AqpqCH JHA.4272@TK2MSF TNGP03.phx.gbl. ..
>

>Albert,
>>
>Excuse me if I have missed the meaning here too.

>

Looking at the post we see:

>I am working on a database that has a main menu, many sub-menus and some

sub-sub-menus. They are all forms that have numerous command buttons on them
to open forms and reports in the database.

So, they ****are**** forms...NOT menu bars. I simply 100% dead wrong on my
assuming here. (sorry to the origiona poster...I got this one wrong..very
one).

to op:

If macros were used, then this might be easy, but as it stands, if each of
the butitons runs code, then you have a problem since:


dim strForm as string


strForm = "Test"

docmd.OpenForm strForm


In the above, how are we going to determine the above code behind a button
opens a particular form?

We not only faced with parsing the code, but the above Openform command does
NOT open a form called "strForm", but in fact uses the value **inside** of
the variable. This really makes this problem VERY difficult to solve since
what you asking for would require one to "run and interpret" code...

So, if those buttons runs code when clicked on, you not going to have much
success documenting this problem.

On the other hand, if they used the switchboard, then the database of what
button does what is already built!!

So, to the op:
are these forms with buttons built via the switchboard wizard, or does
each button simply run/call code as above?

if one used the switch bord, then you are in luck, but if each buttion
simply runs vba code, the you are much out of luck....


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKa llal@msn.com

[Steve]

Re: Determine What Command Button Opens A Form

Sorry for the confusion! Buttons on a form!

Steve


"Albert D. Kallal" <PleaseNOOOsPAM mkallal@msn.com wrote in message
news:O96e%23zCH JHA.1304@TK2MSF TNGP02.phx.gbl. ..

"Steve Schapel" <schapel@mvps.o rg.nswrote in message
news:%235AqpqCH JHA.4272@TK2MSF TNGP03.phx.gbl. ..
>

>Albert,
>>
>Excuse me if I have missed the meaning here too.

>
I agree, it would be sooooo simple if you were aksing
>
>
"Can I get a list of menus that show what forms/reprots they call/open?"
>
REally, a VERY simple question. However, from the context, we get:
>

>>MyForm MyMenuForm Cmd1032
>>HisForm HisMenuForm Cmd1243
>>HerForm MyMenuForm Cmd17

>
So, I have to wait for the original poster to clarify what they are
looking for..but, there is a "real" confusing as to if we taking about
custom menu bars, or in fact regular plain controls on form which ARE NOT
what we call menus.
>
To be fair, looking at the above, the naming convention by access
"defaults" suggests you have this context correct..and I am 100% dead
wrong on my view of his question...
>
So, to the original poster, you are creating a LARGE amount of confusing
here by not distinguishing if you are taking about custom menus and menu
bars, or simply that of buttons on a form. We don't call command buttons
on a form a menu, or a menu bar....
>
(a grand canyon of difference here).
>
So, you have to clarify what you talking about now, we are EXTREMELY and
THOROUGHLY confused here...
>
Are you taking about custom menus (and menu bars), or just buttons on a
form?
>
>
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKa llal@msn.com
>

[Albert D. Kallal]

Re: Determine What Command Button Opens A Form

"Salad" <oil@vinegar.co mwrote in message
news:h8ednXaKHK 8qL0vVnZ2dnUVZ_ rXinZ2d@earthli nk.com...

Albert D. Kallal wrote:
>

>"Salad" <oil@vinegar.co mwrote in message
>news:wuOdncXFG LT3N0vVnZ2dnUVZ _t3inZ2d@earthl ink.com...
>>

>>>>HerForm MyMenuForm Cmd17
>>>>
>>>>Is there a way to programatically create this list?
>>>>
>>>>Thanks!
>>>>
>>>>Steve
>>>
>>>No.
>>>
>>>You asked this question before. You didn't like the solution using
>>>OpenArgs.

>>
>>
>NO NO NO!
>>
>He is not asking to fix/solve a coding problem. He is asking how can he
>generate a list for documentation purposes that displays what custom menu
>bar
>button calls what form or report. This is a grand canyon DIFFERENT of a
>problem. See my response for the solution....
>>
>He is not trying to modify existing code, nor is he trying have the
>existing code "know" what button called the code, he simply wants a list
>of his custom menu bars showing what buttons on those custom menu bars
>call what forms and reports...
>>
>>

Hi Albert. I thought he was talking about forms and command buttons that
open a form/report. I didn't think from reading his post, or his post a
week ago, had anything to do with commandbars, menubars, toolbars,
shortcuts, or whatever.
>

You are right (my apologies). However, it is a documentation issue he trying
to solve, not a coding issue. So, while he is NOT talking about custom menu
bars, he asking for a solution to tell him "what" form + button calls other
forms. As far as I can tell, there really not an easy answer unless he
talking about a switchboard created by the wizard......


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKa llal@msn.com

[Steve]

Re: Determine What Command Button Opens A Form

Yes, Yes, Yes - You have interpreted my question correctly!!

Steve


"Steve Schapel" <schapel@mvps.o rg.nswrote in message
news:%235AqpqCH JHA.4272@TK2MSF TNGP03.phx.gbl. ..

Albert,
>
Excuse me if I have missed the meaning here too. But I have understood
that Steve is using to the word "menu" not to refer to a menu as in menu
bar, but menu as in a form with a bunch of command buttons. So he has a
number of forms, each with a number of command buttons, and it looks like
the command buttons are not well named. And it looks like each command
button is associated with opening only one form or report. So he is
asking for a way to programmaticall y make a list of the command buttons to
show which form they are on, and which form/report they open.
>
--
Steve Schapel, Microsoft Access MVP
>
>
Albert D. Kallal wrote:

>All you have to do is iterate the commandbars collection and print out
>the
>report/form that the button calls.
>>
>It not hard to do this. the only thing here is do you want the listing by
>menu bars, or simply grouped by reports? (if you send the resulting data
>to
>a table, then you can report grouped by each form....
>>
>The basic code will look like:
>>
>Sub ListMyBars()
>>
> Dim cbar As CommandBar
>>
> For Each cbar In CommandBars
> Call DisplayControls (cbar)
> Next
>>
> End Sub
>>
>Sub DisplayControls (cbar As CommandBar)
>>
> Dim cControl As CommandBarContr ol
>>
> If cbar.BuiltIn = False Then
> For Each cControl In cbar.Controls
> Debug.Print cControl.Captio n & _
> " Action = " & cControl.OnActi on & _
> " form/reprot = " & cControl.Parame ter
>>
> If cControl.Type = 10 Then
> Debug.Print cControl.Captio n & "---->"
> Call DisplayControls (cControl.Comma ndBar)
> End If
> Next
> End If
>>
>End Sub
>>
>>
>The above is recursive and "calls" itself since menu bars can go "many"
>levels deep. I would likely add a 4-5 more lines of code to send the
>above
>data to a reocrdset. You thus can then run a report grouped by
>form/report.
>>

[Albert D. Kallal]

Re: Determine What Command Button Opens A Form

"Steve" <nonsense@nomse nse.comwrote in message
news:KqCdnQF2Y-ysJEvVnZ2dnUVZ_ jKdnZ2d@earthli nk.com...

Sorry for the confusion! Buttons on a form!
>
Steve

Unless those forms are built using the switch board, or perhaps the buttons
use macros, you have NO WAY to determine this. Look at the following code
which is typical of code behind a button:


dim strForm as string
strForm = "Test"
docmd.OpenForm strForm


In the above, how are we going to determine the above code behind a button
opens a particular form?

We are not only faced with parsing the code, but the above Openform command
does
NOT open a form called "strForm", but in fact uses the value **inside** of
the variable. The above code opens a form called "test"

What about:

dim strForm as string
strForm = Inputbox("what form to open")
docmd.OpenForm strForm

The above code if placed behind a button actually prompts the user for what
form and then opens it.

This really makes this problem VERY difficult to solve since
what you asking for would require one to "run and interpret" code.

So, if those buttons runs code when clicked on, you not going to have much
success documenting what button does what.

If they used macros, then you have a better chance. And, if they used the
switchboard which is "database" driven, then again you have a good chance.

However, trying to "look at" code that runs behind the button is a difficult
task. You really have no way of knowing if that button sends out a email,
does payroll calculations, or opens up a form unless you interpret the
code...

You could try write some code that reads the above code line by line and
then parses out the openform command. However, even once you get that line
of code, often there is a variable used for the openform command, so this is
not an
easy problem to solve...


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKa llal@msn.com

[Steve Schapel]

Re: Determine What Command Button Opens A Form

Steve,

I tend to be a pragmatist in such circumstances. Bite the bullet, grab
a mate during their coffee break and tell them to write down the numbers
you call out, open the module behind each of these "menu" forms, use the
Edit/Find menu to find "OpenForm", use F3, and record the button name
and form name for each one. I appreciate that you have a lot of forms
and a lot of buttons. So it will be a drag of a job. But really, once
you're on a roll you'll be grabbing them at the rate of about 1 every 2
seconds, and in the end you're likely to get the job done a lot quicker
than trying to discuss it in the newsgroup and then getting code working
correctly.

Just my 2c.

--
Steve Schapel, Microsoft Access MVP

Steve wrote:

Yes, Yes, Yes - You have interpreted my question correctly!!
>

[John... Visio MVP]

Re: Determine What Command Button Opens A Form

I would send him to http://pcdatasheet.com/ to check out the Access help on
the website.

John... Visio MVP

"Steve Schapel" <schapel@mvps.o rg.nswrote in message
news:O$JybfDHJH A.2580@TK2MSFTN GP05.phx.gbl...

Steve,
>
I tend to be a pragmatist in such circumstances. Bite the bullet, grab a
mate during their coffee break and tell them to write down the numbers you
call out, open the module behind each of these "menu" forms, use the
Edit/Find menu to find "OpenForm", use F3, and record the button name and
form name for each one. I appreciate that you have a lot of forms and a
lot of buttons. So it will be a drag of a job. But really, once you're
on a roll you'll be grabbing them at the rate of about 1 every 2 seconds,
and in the end you're likely to get the job done a lot quicker than trying
to discuss it in the newsgroup and then getting code working correctly.
>
Just my 2c.
>
--
Steve Schapel, Microsoft Access MVP
>
Steve wrote:

>Yes, Yes, Yes - You have interpreted my question correctly!!
>>