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
16  2670 
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 pl*****************@msn.com
"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
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" <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.
"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
"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
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
"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
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.
"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
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!!
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!!
"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.
"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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Tim |
last post by:
I have one main form that I use to view data as well as add new records.
When I click the add new record button, many of the form's controls that
arn't needed get set to invisible. When I'm done...
|
by: Colin |
last post by:
I have a main form with a command button on it that when clicked,
opens a subform displaying records that relate to the main form.
I would like to disable the command button if the corresponding...
|
by: Mark R |
last post by:
Hi Guru's,
I have a frontend application where my boss wants to when people are
using the database.
I have a form which OnOpen, inserts the username, network login and
date/time it was opened to...
|
by: Daniel Walzenbach |
last post by:
Hi,
I created an ASP.NET Datagrid where a single row can be selected by clicking anywhere on the row (according to...
|
by: emc_cos |
last post by:
I have a very nice parent child relationship happening between a main
form and 8 subforms.
The main form is the data entry point for a table that holds textual
data like names, notes and the...
|
by: John Smith |
last post by:
I have a continuous form. there is a command button with the following code
for the OnClick event:
DoCmd.OpenForm "frmPlants", , , "PlantsID =" & Me!PlantsID
I click the button and frmPlants...
|
by: Imicola |
last post by:
Hi,
I have seen very similar questions which have not really helped me, so thought I'd better ask for some more help.
I have a form called fsubAnimalDetails(which holds data on animals...
|
by: jmarcrum |
last post by:
Hello all,
i have a continuous form that displays about 1000 records and opens when I click a button. When the form opens, the user has the option of checking a checkbox that is located beside...
|
by: alnino |
last post by:
Hi,
On a form I have a command button that allows a user to browse for a file (PDF, Word, etc…). This command button then stores a hyperlink to the file within an associated txtfield in the table....
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
| |
