Add/Remove Drop-Down Menu Options in Database

[Parmenides320]

I have a database I inherited - it's a requisition database. When an
end-user is using the database they add a requisition on a form then click a
button that says "Print" that opens the "actual requisition" which is a
report. What they are supposed to do is select "Print" from the File
drop-down menu when the report is open to print the report. All too often
someone selects "Print" from the drop-down menu while they are still in the
form before they open the report and pages and pages are printed. (I'll come
back to that.) Recently someone asked me if it is possible for the "actual
requisition" to be emailed. The "Send" option in the File drop-down menu is
not visible so that answer right now is "No."

What I would LIKE to do is remove the "Print" option from the File drop-down
menu while the end-user is in the form and add the "Send" option to the File
drop-down menu when the end-user is in the report. I'd settle for just
adding the send but I'd love to do both. What I found in help and online
that allows me to add and delete options from the drop-down menu adds and
deletes them from the application itself - something I don't want. I just
want it to be the way I described when the end-user is in the database as an
end-user. If I access the design of the database I'd still like all the
options to be available.

So...anyone out there know if I can do this and, if so, how? Any help would
be appreciated.

 

[Tom Wickerath]

Hi Parmenides,

You can create a custom menu (or ribbon in Access 2007), and provide both
the Send and Print capability on this menu, to replace the built-in menu.
Perhaps it would be easier to just disable the built-in menu on this form and
add two command buttons onto the form, one for printing and one for the send
functionality. To disable the built-in menu on a particular form, set the
form's Menu Bar property (on the Other tab for form properties) to zero:

Menu Bar. . . . . . =0

Here is a link that provides a short tutorial for creating the ability to
print just the current record on a form. The title may be a bit misleading,
but the method outlined will work for this requirement:

How to print only one page of a multipage report
http://www.access.qbuilt.com/html/reports.html#PrintOnePgOfRpt

For the Send capability, the two common formats would be to send as a
Snapshot file (*.snp), or to send in the Portable Document Format (*.pdf)
format. You can achieve this capability with a little VBA code behind a
command button as well. The Snapshot format is fairly easy, but requires that
the receipient have the Microsoft Access Snapshot Viewer utility installed:

Microsoft Snapshot Viewer is available in download center
http://support.microsoft.com/?id=175274

There are free and fairly cheap .pdf utilities available as well.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Parmenides320]

What great information. And so quick, too! However I am wondering (and
concerned) about a few things. ("Concerned" applies to your statement "...a
little VBA code..." I don't know VBA.)

At first reading I thought "cool!" (And I typed it.) Then I thought, I
don't want the Print and Send buttons on the form but rather on the report
and...I'm thinking one can't put command buttons on reports. So now I'm
leaning towards the drop-down menu option again. However, I realize there
might be another option.

Is it possible on a form to create a command button that will print NOT the
form but a corresponding report and a command button that will send NOT the
form but a corresponding report? I'm sure that there must be a way but then
we're probably looking at your "a little VBA code" statement (with fear)
again.

So, if this is possible, which would you suggest - adding two command
buttons to the form to Print and Send the form's corresponding report OR (I
guess) create a custom menu bar with just those two commands on it that would
be visible only when the report is open? (I've already "removed" the toolbar
from the form.)

I await your reply with great anticipation.

Linda

 

[Tom Wickerath]

Hi Linda,

and...I'm thinking one can't put command buttons on reports.

You can put a command button on a report, but you can't do anything with it,
since there are no events exposed. I'm not sure if Access 2007 allows events
for command buttons (?), but certainly Access 2003 and all prior versions do
not. So, with A2003, you'd just get an image of a command button, but it
would not be functional.

So now I'm leaning towards the drop-down menu option again.

I don't recommend that you invest the time, right now, to learn how to
create custom menus. For one thing, while such menus will work in Access
2007, they cannot be created in Access 2007, since menus have been replaced
with the *&^%$#@ ribbon (can you tell how much I love the new ribbon

Is it possible on a form to create a command button that will print NOT the
form but a corresponding report and a command button that will send NOT the
form but a corresponding report?

The method I pointed you to does *not* print the form. It allows one to
print a report, filtered to just the active record displayed on the form. The
same is true with the Send functionality; we send a filtered report, using
either the Microsoft Access Snapshot (*.snp) format, or the .pdf format.

So, if this is possible, which would you suggest - adding two command
buttons to the form to Print and Send the form's corresponding report OR (I
guess) create a custom menu bar with just those two commands on it that would
be visible only when the report is open?

In my humble opinion, the first option of adding two command buttons to the
form will be much easier to achieve.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Parmenides320]

Thanks again for all the info. Got the Print command button to print the
corresponding report working on the form but the Send command button is not
happy. It doesn't like WhereCondition. What I wound up doing is looking at
the Print button that was already on the form. (Again, not my design. I was
hoping it would help me a little with the syntax. I was still a little fuzzy
after reading your article.) When you clicked on the Print button it opened
the corresponding report then one selects Print from the drop-down menu. I
looked at the "event procedure" for the old print button and found this:

DoCmd.OpenReport ReportName:="rptPO", View:=acViewPreview,
WhereCondition:="IDNumber = " & Me.IDNumber


I should back up. I created a new Print command button and a Send command
button but both printed/sent ALL reports. So I looked at the event for the
original print button then looked at the event for the newly created print
button. I could see they were basically the same except the original Print
command had the WhereCondition (like you described in your article) so I
simply added the , WhereCondition and everything after it to the new Print
command event now it looks like this:

Dim stDocName As String

stDocName = "rptPO"
DoCmd.OpenReport stDocName, acNormal, WhereCondition:="IDNumber = " &
Me.IDNumber

And it works. I then added the ", WhereCondition:="IDNumber = " &
Me.IDNumber" to the Send command event

stDocName = "rptPO"
DoCmd.SendObject acReport, stDocName, WhereCondition:="IDNumber = " &
Me.IDNumber


and I get an error: Complile Error: Named Argument Not Found and it has
opened up that Event "stuff" and has highlighted WhereCondition:= so I'm
thinking WhereCondition is not valid for SendObject.

Do you have one of your nifty articles that tells how to send one page?

By the by, I almost feel your pain on 2007. I am the Computer Service
Manager for a non-profit with an amazingly high number of people who are
basically...computer illiterate. About 4 month ago I purchased 50 licenses
of MS Office 2003 Pro to upgrade the "administrative" personnel from 2000 and
XP. At that time I had the option of purchasing 2007 instead. Took one look
at that ribbon and thought what a nightmare it would be with 50 people
calling me every second of the day asking me how to do things. I stuck with
2003 because it looks about the same as it's predecessors and I already get
enough "how to" calls as it is.

 

[Tom Wickerath]

Hi Linda,

(Again, not my design. I was hoping it would help me a little with the syntax.
I was still a little fuzzy after reading your article.)

If you have a suggestion for how I might restate something, I'm certainly
interested. We can take that part off-line. I invite you to send me a private
e-mail regarding this. My e-mail address is available at the bottom of the
contributor's page indicated in my signature. Please do not post my e-mail
address (or yours) to a newsgroup reply. Doing so will attract the unwanted
attention of spammers.

[Comment regarding Print Command button]

And it works.

Great! I'm happy to read of your success!

[Comment regarding SendObject method]

so I'm thinking WhereCondition is not valid for SendObject.

Correct. If you have your mouse cursor blinking anywhere within this word,
and you press the F1 button, you should open context-sensitive help on this
topic. Look at posts # 5 and 6 in this thread:

http://groups.google.com/group/micr..._frm/thread/20a95054dd0cdcf7/f8aca5cb5dd3b164

About half way down post # 5, I show how another method that will allow a
filtered report to be sent using DoCmd.SendObject. Please let me know if this
posting helps.

Took one look at that ribbon and thought what a nightmare it would be
with 50 people....

Too bad you didn't post this comment earlier, on Clint's blog, before he
closed comments on a recent posting:

http://blogs.msdn.com/clintcovington/archive/2007/06/07/do-you-like-the-new-navigation-pane.aspx

Although the subject asked about the new Navigation Pane, there were plenty
of comments regarding the ribbon as well.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________