Customizing menu bars

[at]

I would really like to create a "Reports List" on my custom menu bar. It
would list all the reports that are in the database. However, some of the
reports require some criteria (i.e. beginning and ending dates, specific
customer, etc.). So if they click on a report off the list, it could bring
up a form that they could use for entering the specific criteria, but how do
I "capture" what report they chose off the list? Maybe this isn't a very
practical way of displaying a reports list, let me know if you have any
better suggestions.

Thanks!!!

 

[Graham Mandeno]

Are you creating this list as a combo box or as a submenu?

If it's a combo box then you can have a single action procedure that checks
the selected item in the combo box and takes the appropriate action:

Select Case CommandBars("MyMenu").Controls("Print report:").Text
Case "Current Client List"
DoCmd.OpenReport "rptCurrentClientList", ...
Case "Selected Transactions"
DoCmd.OpenForm "frmPrintSelectedTransactions", , , , , acDialog
.... etc
End Select

If it's a submenu, then you can still have a single action procedure, but
pass a different argument to it for each menu button, and use Select Case in
the same way.

 

[at]

The list is on a custom menu bar. The user would click on one of the menu
options called "Reports List" and then they see a list of 5 different report
subcategories. Then when they go to a specific category, they see the list
of reports that pertain to that category. I'm not very good at VB so my
question is where do I put the select case code? - On the "On Action" of the
???? button? And to really show my ignorance, do I just create a function
sub() module and put the select case code in it? -Because that is giving me
an "Invaid procedure call or agrument" error. And lastly, is "My menu" the
name of the overall menu and "Print Report:" the name of the Reports list or
is it the name of the subcategory? Please forgive my ignorance and thank you
so much for your help!!

Are you creating this list as a combo box or as a submenu?

If it's a combo box then you can have a single action procedure that checks
the selected item in the combo box and takes the appropriate action:

Select Case CommandBars("MyMenu").Controls("Print report:").Text
Case "Current Client List"
DoCmd.OpenReport "rptCurrentClientList", ...
Case "Selected Transactions"
DoCmd.OpenForm "frmPrintSelectedTransactions", , , , , acDialog
.... etc
End Select

If it's a submenu, then you can still have a single action procedure, but
pass a different argument to it for each menu button, and use Select Case in
the same way.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I would really like to create a "Reports List" on my custom menu bar. It
would list all the reports that are in the database. However, some of the
reports require some criteria (i.e. beginning and ending dates, specific
customer, etc.). So if they click on a report off the list, it could
bring
up a form that they could use for entering the specific criteria, but how
do
I "capture" what report they chose off the list? Maybe this isn't a very
practical way of displaying a reports list, let me know if you have any
better suggestions.

Thanks!!!

 

[Graham Mandeno]

It sounds like you are doing this with submenus, not with a combo box
control.

If you right-click on any menu or toolbar, then click "Customize", you will
be in "command bar maintenance mode". If necessary, display your custom
menu by selecting its checkbox in the list of toolbars. Then you can click
on the "Reports List" item, then a subcategory, and then right-click on one
of your buttons to open a report and click "Properties". In the "On Action"
box, you can enter the name of a function to call when the button is clicked
(including arguments). For example:
=MenuPrintReport("NameOfReport")

Repeat this for all your menu buttons.

Now, open a standard module and paste the following code:

Public Function MenuPrintReport( strRptName as String)
Select Case strRptName
Case "Name of report that isn't filtered"
DoCmd.OpenReport "name of report", ...
Case "name of report printed via a dialog"
DoCmd.OpenForm "name of form", , , , , acDialog
.... etc
case Else
MsgBox "Unknown report: " & strRptName
End Select
End Function

This function will be called every time one of your menu buttons is clicked.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

The list is on a custom menu bar. The user would click on one of the menu
options called "Reports List" and then they see a list of 5 different
report
subcategories. Then when they go to a specific category, they see the
list
of reports that pertain to that category. I'm not very good at VB so my
question is where do I put the select case code? - On the "On Action" of
the
???? button? And to really show my ignorance, do I just create a function
sub() module and put the select case code in it? -Because that is giving
me
an "Invaid procedure call or agrument" error. And lastly, is "My menu"
the
name of the overall menu and "Print Report:" the name of the Reports list
or
is it the name of the subcategory? Please forgive my ignorance and thank
you
so much for your help!!

Are you creating this list as a combo box or as a submenu?

If it's a combo box then you can have a single action procedure that
checks
the selected item in the combo box and takes the appropriate action:

Select Case CommandBars("MyMenu").Controls("Print report:").Text
Case "Current Client List"
DoCmd.OpenReport "rptCurrentClientList", ...
Case "Selected Transactions"
DoCmd.OpenForm "frmPrintSelectedTransactions", , , , , acDialog
.... etc
End Select

If it's a submenu, then you can still have a single action procedure, but
pass a different argument to it for each menu button, and use Select Case
in
the same way.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I would really like to create a "Reports List" on my custom menu bar.
It
would list all the reports that are in the database. However, some of
the
reports require some criteria (i.e. beginning and ending dates,
specific
customer, etc.). So if they click on a report off the list, it could
bring
up a form that they could use for entering the specific criteria, but
how
do
I "capture" what report they chose off the list? Maybe this isn't a
very
practical way of displaying a reports list, let me know if you have any
better suggestions.

Thanks!!!

 

[at]

Thanks so much for your help. I have it all working until I get to the form
that has the criteria in it for the report. I have a "print" button and
this form. How do I know once it's gotten to this point, what button the
user picked back on the menu? I need to bring over the name of the report.
And I also need to take a VB class!!

at

P.S. Just out of curiosity, you've been referring to combo boxes, how do
you put a combo box on a custom menu?? And where do you learn this
stuff???!!!

It sounds like you are doing this with submenus, not with a combo box
control.

If you right-click on any menu or toolbar, then click "Customize", you will
be in "command bar maintenance mode". If necessary, display your custom
menu by selecting its checkbox in the list of toolbars. Then you can click
on the "Reports List" item, then a subcategory, and then right-click on one
of your buttons to open a report and click "Properties". In the "On Action"
box, you can enter the name of a function to call when the button is clicked
(including arguments). For example:
=MenuPrintReport("NameOfReport")

Repeat this for all your menu buttons.

Now, open a standard module and paste the following code:

Public Function MenuPrintReport( strRptName as String)
Select Case strRptName
Case "Name of report that isn't filtered"
DoCmd.OpenReport "name of report", ...
Case "name of report printed via a dialog"
DoCmd.OpenForm "name of form", , , , , acDialog
.... etc
case Else
MsgBox "Unknown report: " & strRptName
End Select
End Function

This function will be called every time one of your menu buttons is clicked.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

The list is on a custom menu bar. The user would click on one of the menu
options called "Reports List" and then they see a list of 5 different
report
subcategories. Then when they go to a specific category, they see the
list
of reports that pertain to that category. I'm not very good at VB so my
question is where do I put the select case code? - On the "On Action" of
the
???? button? And to really show my ignorance, do I just create a function
sub() module and put the select case code in it? -Because that is giving
me
an "Invaid procedure call or agrument" error. And lastly, is "My menu"
the
name of the overall menu and "Print Report:" the name of the Reports list
or
is it the name of the subcategory? Please forgive my ignorance and thank
you
so much for your help!!

Are you creating this list as a combo box or as a submenu?

If it's a combo box then you can have a single action procedure that
checks
the selected item in the combo box and takes the appropriate action:

Select Case CommandBars("MyMenu").Controls("Print report:").Text
Case "Current Client List"
DoCmd.OpenReport "rptCurrentClientList", ...
Case "Selected Transactions"
DoCmd.OpenForm "frmPrintSelectedTransactions", , , , , acDialog
.... etc
End Select

If it's a submenu, then you can still have a single action procedure, but
pass a different argument to it for each menu button, and use Select Case
in
the same way.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I would really like to create a "Reports List" on my custom menu bar.
It
would list all the reports that are in the database. However, some of
the
reports require some criteria (i.e. beginning and ending dates,
specific
customer, etc.). So if they click on a report off the list, it could
bring
up a form that they could use for entering the specific criteria, but
how
do
I "capture" what report they chose off the list? Maybe this isn't a
very
practical way of displaying a reports list, let me know if you have any
better suggestions.

Thanks!!!

 

[Graham Mandeno]

Ahhhh... I see... you have a single criteria form for several reports?

OK, then pass on the strRptName value to the form via the OpenArgs property.
OpenArgs is the 7th argument for the OpenForm method (right after WindowMode
for which you are passing acDialog)

DoCmd.OpenForm "name of form", , , , , acDialog, strRptName

Or, if you prefer, use named arguments which are easier to read:

DoCmd.OpenForm "name of form", WindowMode:=acDialog,
OpenArgs:=strRptName

Then, inside your form module you can refer to Me.OpenArgs:

Dim strReport as String
strReport = Me.OpenArgs & ""

(the reason for the & "" is in case your form has been opened from elsewhere
and has a Null for OpenArgs)
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks so much for your help. I have it all working until I get to the
form
that has the criteria in it for the report. I have a "print" button and
this form. How do I know once it's gotten to this point, what button the
user picked back on the menu? I need to bring over the name of the
report.
And I also need to take a VB class!!

at

P.S. Just out of curiosity, you've been referring to combo boxes, how do
you put a combo box on a custom menu?? And where do you learn this
stuff???!!!

It sounds like you are doing this with submenus, not with a combo box
control.

If you right-click on any menu or toolbar, then click "Customize", you
will
be in "command bar maintenance mode". If necessary, display your custom
menu by selecting its checkbox in the list of toolbars. Then you can
click
on the "Reports List" item, then a subcategory, and then right-click on
one
of your buttons to open a report and click "Properties". In the "On
Action"
box, you can enter the name of a function to call when the button is
clicked
(including arguments). For example:
=MenuPrintReport("NameOfReport")

Repeat this for all your menu buttons.

Now, open a standard module and paste the following code:

Public Function MenuPrintReport( strRptName as String)
Select Case strRptName
Case "Name of report that isn't filtered"
DoCmd.OpenReport "name of report", ...
Case "name of report printed via a dialog"
DoCmd.OpenForm "name of form", , , , , acDialog
.... etc
case Else
MsgBox "Unknown report: " & strRptName
End Select
End Function

This function will be called every time one of your menu buttons is
clicked.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

The list is on a custom menu bar. The user would click on one of the
menu
options called "Reports List" and then they see a list of 5 different
report
subcategories. Then when they go to a specific category, they see the
list
of reports that pertain to that category. I'm not very good at VB so
my
question is where do I put the select case code? - On the "On Action"
of
the
???? button? And to really show my ignorance, do I just create a
function
sub() module and put the select case code in it? -Because that is
giving
me
an "Invaid procedure call or agrument" error. And lastly, is "My menu"
the
name of the overall menu and "Print Report:" the name of the Reports
list
or
is it the name of the subcategory? Please forgive my ignorance and
thank
you
so much for your help!!

:

Are you creating this list as a combo box or as a submenu?

If it's a combo box then you can have a single action procedure that
checks
the selected item in the combo box and takes the appropriate action:

Select Case CommandBars("MyMenu").Controls("Print report:").Text
Case "Current Client List"
DoCmd.OpenReport "rptCurrentClientList", ...
Case "Selected Transactions"
DoCmd.OpenForm "frmPrintSelectedTransactions", , , , ,
acDialog
.... etc
End Select

If it's a submenu, then you can still have a single action procedure,
but
pass a different argument to it for each menu button, and use Select
Case
in
the same way.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I would really like to create a "Reports List" on my custom menu bar.
It
would list all the reports that are in the database. However, some
of
the
reports require some criteria (i.e. beginning and ending dates,
specific
customer, etc.). So if they click on a report off the list, it
could
bring
up a form that they could use for entering the specific criteria,
but
how
do
I "capture" what report they chose off the list? Maybe this isn't a
very
practical way of displaying a reports list, let me know if you have
any
better suggestions.

Thanks!!!

 

[at]

That worked great! Thanks so much for your help!!

If you're bored, would you want to take a look at my other post under
"reports" called "Subreport Totals". I put it out there a couple of times,
but I think I've stumped everyone!!

Thanks again!
at

Ahhhh... I see... you have a single criteria form for several reports?

OK, then pass on the strRptName value to the form via the OpenArgs property.
OpenArgs is the 7th argument for the OpenForm method (right after WindowMode
for which you are passing acDialog)

DoCmd.OpenForm "name of form", , , , , acDialog, strRptName

Or, if you prefer, use named arguments which are easier to read:

DoCmd.OpenForm "name of form", WindowMode:=acDialog,
OpenArgs:=strRptName

Then, inside your form module you can refer to Me.OpenArgs:

Dim strReport as String
strReport = Me.OpenArgs & ""

(the reason for the & "" is in case your form has been opened from elsewhere
and has a Null for OpenArgs)
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks so much for your help. I have it all working until I get to the
form
that has the criteria in it for the report. I have a "print" button and
this form. How do I know once it's gotten to this point, what button the
user picked back on the menu? I need to bring over the name of the
report.
And I also need to take a VB class!!

at

P.S. Just out of curiosity, you've been referring to combo boxes, how do
you put a combo box on a custom menu?? And where do you learn this
stuff???!!!

It sounds like you are doing this with submenus, not with a combo box
control.

If you right-click on any menu or toolbar, then click "Customize", you
will
be in "command bar maintenance mode". If necessary, display your custom
menu by selecting its checkbox in the list of toolbars. Then you can
click
on the "Reports List" item, then a subcategory, and then right-click on
one
of your buttons to open a report and click "Properties". In the "On
Action"
box, you can enter the name of a function to call when the button is
clicked
(including arguments). For example:
=MenuPrintReport("NameOfReport")

Repeat this for all your menu buttons.

Now, open a standard module and paste the following code:

Public Function MenuPrintReport( strRptName as String)
Select Case strRptName
Case "Name of report that isn't filtered"
DoCmd.OpenReport "name of report", ...
Case "name of report printed via a dialog"
DoCmd.OpenForm "name of form", , , , , acDialog
.... etc
case Else
MsgBox "Unknown report: " & strRptName
End Select
End Function

This function will be called every time one of your menu buttons is
clicked.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


The list is on a custom menu bar. The user would click on one of the
menu
options called "Reports List" and then they see a list of 5 different
report
subcategories. Then when they go to a specific category, they see the
list
of reports that pertain to that category. I'm not very good at VB so
my
question is where do I put the select case code? - On the "On Action"
of
the
???? button? And to really show my ignorance, do I just create a
function
sub() module and put the select case code in it? -Because that is
giving
me
an "Invaid procedure call or agrument" error. And lastly, is "My menu"
the
name of the overall menu and "Print Report:" the name of the Reports
list
or
is it the name of the subcategory? Please forgive my ignorance and
thank
you
so much for your help!!

:

Are you creating this list as a combo box or as a submenu?

If it's a combo box then you can have a single action procedure that
checks
the selected item in the combo box and takes the appropriate action:

Select Case CommandBars("MyMenu").Controls("Print report:").Text
Case "Current Client List"
DoCmd.OpenReport "rptCurrentClientList", ...
Case "Selected Transactions"
DoCmd.OpenForm "frmPrintSelectedTransactions", , , , ,
acDialog
.... etc
End Select

If it's a submenu, then you can still have a single action procedure,
but
pass a different argument to it for each menu button, and use Select
Case
in
the same way.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I would really like to create a "Reports List" on my custom menu bar.
It
would list all the reports that are in the database. However, some
of
the
reports require some criteria (i.e. beginning and ending dates,
specific
customer, etc.). So if they click on a report off the list, it
could
bring
up a form that they could use for entering the specific criteria,
but
how
do
I "capture" what report they chose off the list? Maybe this isn't a
very
practical way of displaying a reports list, let me know if you have
any
better suggestions.

Thanks!!!