By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,493 Members | 3,101 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,493 IT Pros & Developers. It's quick & easy.

Determine What Command Button Opens A Form

P: n/a
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
Sep 21 '08 #1
Share this Question
Share on Google+
16 Replies


P: n/a
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.
Sep 21 '08 #2

P: n/a
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
pl*****************@msn.com
Sep 21 '08 #3

P: n/a
"Salad" <oi*@vinegar.comwrote in message
news:wu******************************@earthlink.co 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
pl*****************@msn.com

Sep 21 '08 #4

P: n/a
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]
Sep 21 '08 #5

P: n/a
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.

Sep 21 '08 #6

P: n/a
Albert D. Kallal wrote:
"Salad" <oi*@vinegar.comwrote in message
news:wu******************************@earthlink.co 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.

Sep 21 '08 #7

P: n/a
"Steve Schapel" <sc*****@mvps.org.nswrote in message
news:%2****************@TK2MSFTNGP03.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
pl*****************@msn.com
Sep 21 '08 #8

P: n/a
"Albert D. Kallal" <Pl*******************@msn.comwrote in message
news:O9****************@TK2MSFTNGP02.phx.gbl...
"Steve Schapel" <sc*****@mvps.org.nswrote in message
news:%2****************@TK2MSFTNGP03.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
pl*****************@msn.com
Sep 21 '08 #9

P: n/a
Sorry for the confusion! Buttons on a form!

Steve
"Albert D. Kallal" <Pl*******************@msn.comwrote in message
news:O9****************@TK2MSFTNGP02.phx.gbl...
"Steve Schapel" <sc*****@mvps.org.nswrote in message
news:%2****************@TK2MSFTNGP03.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
pl*****************@msn.com

Sep 21 '08 #10

P: n/a
"Salad" <oi*@vinegar.comwrote in message
news:h8******************************@earthlink.co m...
Albert D. Kallal wrote:
>"Salad" <oi*@vinegar.comwrote in message
news:wu******************************@earthlink.c om...
>>>>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
pl*****************@msn.com
Sep 21 '08 #11

P: n/a
Yes, Yes, Yes - You have interpreted my question correctly!!

Steve
"Steve Schapel" <sc*****@mvps.org.nswrote in message
news:%2****************@TK2MSFTNGP03.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 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.

Sep 21 '08 #12

P: n/a
"Steve" <no******@nomsense.comwrote in message
news:Kq******************************@earthlink.co m...
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
pl*****************@msn.com

Sep 21 '08 #13

P: n/a
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!!
Sep 21 '08 #14

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

John... Visio MVP

"Steve Schapel" <sc*****@mvps.org.nswrote in message
news:O$**************@TK2MSFTNGP05.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!!

Sep 22 '08 #15

P: n/a
"John... Visio MVP" <la******@stonehenge.cawrote in message
news:1A**********************************@microsof t.com...
>I would send him to http://pcdatasheet.com/ to check out the Access help on
the website.
LOL you're on top form sir (no pun intended). ;-)

Keith.

Sep 23 '08 #16

P: n/a
"Steve" <no******@nomsense.comwrote in message
news:Kq******************************@earthlink.co m...
Sorry for the confusion! Buttons on a form!
I will provide a solution for a modest fee.

Sep 23 '08 #17

This discussion thread is closed

Replies have been disabled for this discussion.