Determine What Command Button Opens A Form

S

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
 
S

Salad

Steve said:
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
Click to expand...
No.

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

Albert D. Kallal

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.
 
A

Albert D. Kallal

Salad said:
No.

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

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...
 
M

Marshall Barton

Steve said:
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
Click to expand...

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
 
S

Steve Schapel

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.
 
S

Salad

Albert said:
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...
Click to expand...
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.
 
A

Albert D. Kallal

Albert,

Excuse me if I have missed the meaning here too.
Click to expand...

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
Click to expand...

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?
 
A

Albert D. Kallal

Looking at the post we see:
I am working on a database that has a main menu, many sub-menus and some
Click to expand...
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....
 
A

Albert D. Kallal

Salad said:
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.
Click to expand...

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......
 
A

Albert D. Kallal

Steve said:
Sorry for the confusion! Buttons on a form!

Steve
Click to expand...


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...
 
S

Steve Schapel

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.
 
B

Beetle

I know. And his Access tips page has been "coming soon" for what, a couple of
years now? That's why your post made me laugh. You may be right
that it's more tragic than funny, but thanks for the laugh anyway.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Dynamic Naming of Form Button 3
Dumb question alert! What program to use? 0
Help creating a search form in Access XP 0
Program buttons to create a word document 0
Creating a Search using a combo box and then creating a report. 4
Clearing combo boxes and a text box 2
Active Content in this file is blocked issue 1
Trying to create a report that will list unmatched records by Ticket Primary Key 0

Top