Determine What Command Button Opens A Form

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

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.

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 CommandBarControl

If cbar.BuiltIn = False Then
For Each cControl In cbar.Controls
Debug.Print cControl.Caption & _
" Action = " & cControl.OnAction & _
" form/reprot = " & cControl.Parameter

If cControl.Type = 10 Then
Debug.Print cControl.Caption & "----"
Call DisplayControls(cControl.CommandBar)
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

"Salad" wrote in message
m...
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

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.OpenForm", _
lngStart, 100000, 1, 1000)
If bolMatch Then
Debug.Print ao.Name; Spc(4); _
.ProcOfLine(lngStart, 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]

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 programmatically 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 CommandBarControl

If cbar.BuiltIn = False Then
For Each cControl In cbar.Controls
Debug.Print cControl.Caption & _
" Action = " & cControl.OnAction & _
" form/reprot = " & cControl.Parameter

If cControl.Type = 10 Then
Debug.Print cControl.Caption & "----"
Call DisplayControls(cControl.CommandBar)
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 wrote:

"Salad" wrote in message
m...

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.

"Steve Schapel" wrote in message
...

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

"Albert D. Kallal" wrote in message
...
"Steve Schapel" wrote in message
...

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

Sorry for the confusion! Buttons on a form!

Steve


"Albert D. Kallal" wrote in message
...
"Steve Schapel" wrote in message
...

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