Creating Fancy Word Shipping Labels

In Chapter 6 you learned how to create basic mailing labels, with name and address information pulled from a table or query, using either the TypeText method or mail merge. A name and address is all you need to print a set of labels for a monthly mailing to a list of club members, or to a list of people who receive a regularly scheduled product shipment. But in the real world, often there are much more complex requirements for printing labels. Before shipping a product, you might also need to check the inventory for a product, the date the product is required, and the availability of shipping supplies, vehicles, and personnel to do the shipping.

i ^ i l"r,)" T ^' samP'e database for this chapter is Northwind plus.accdb.

You might also need to print more information on your shipping labels in addition to the address, such as the Order No., Product No., Product Name, Category, the case number in a sequence of cases, or other such data. To print labels with extra information, or to make decisions on whether a set of labels should be printed, you need more elaborate VBA code.

IN THIS CHAPTER

Creating Word shipping labels with information about shipments

Creating Excel PivotCharts filled with Access data

Emailing Access reports of shipping and reordering information

The sample Northwind Plus database contains tables from the Northwind.mdb database, with several extra fields in some tables, queries (used in the next section), and three new forms — two main forms (one for selecting records for shipping and one for reordering depleted inventory) plus a supplementary form for editing product amounts.

When opening Northwind Plus in Vista, you may get the security alert shown in Figure 12.1. To temporarily enable the database's code, click the "Enable Content" button and select the "Enable this content" option in the dialog. To prevent this security alert from appearing every time you open the database, sign the VBA code with a digital signature, as described in the sidebar.

FIGURE 12.1

A Vista security alert when opening a database with unsigned VBA code.

FIGURE 12.1

A Vista security alert when opening a database with unsigned VBA code.

Creating a Digital Signature for Signing Your Access VBA Code

1. On the Windows Vista Start menu, select All Programs.

2. Select the Microsoft Office folder.

3. Select the Microsoft Office Tools folder.

4. Select the Digital Certificate for VBA projects item:

; Creative I Dell L,|j Dell Accessories [¿J Extias and Upgrades f . Games

Intel Netwoik Adapteis [¿I Maintenante [¿I Microsoft Office

[j»j Microsoft Office Access 2007 I Beta) L^j Mkiosoft Office Excel 2007 iBeta) (H] Microsoft Office LifoPath 2007 (BetaI liftj Mkiosoft Office OneNote 2007 (Beta [q] Mkrosoft Office Outlook 2007 iBetaJ ¡jjl Mkiosoft Office PowerPoint 2007 (Bi [R] Mkiosoft Office Publishei 2007 <Bet,i Q Microsoft Office SliarePoint Designei \w\ Mkiosoft Office Woid 2007 iBetal 1 Mkiosoft Office Tools

1 Digital Certificate foi VBA Piojed

Miciosoft Clip Oiyaniiei » Miciosoft Office 2007 Language * H Miciosoft Office Diagnostics

Miciosoft Office Document Imagi Is Miciosoft Office Document Scanr ¡¿j] Miciosoft Office Pictuie Manager ▼ |

Helen Feddema

Documents

Recent Hems

Coirtiol Panel

Default Piogiams

Help and Support

Selecting the Digital Certificate for VBA Project tool.

5. Enter your name and click OK:

CjJCreate Digital Certificate _

This program creates a self-signed digital certificate that bears the name you type below. This type of certificate does not verify your identity.

Since a self-signed digital certificate might be a forgery, users will receive a security warning when they open a file that contains a macro project with a self-signed signature,

Office will only allow you to trust a self-signed certificate on the machine on which it was created.

A self-signed certificate is only for personal use, If you need an authenticated code signing certificate for signing commercial or broadly distributed macros; you will need to contact a certification authority.

Click here for a list of commercial certificate authorities

Your certificate's name:

Entering a name for the digital certificate.

continued continued

6. You should get a success message:

SelfCeit Success

Successfully created a new certificate for Jane Doe.

Success message after creating a digital certificate.

7. Click the Office button, select the Publish command, and then the Package and Sign command.

New

Open

W

Save

Save As ►

JTifJ

Print ►

Manage ►

E-mail

îJ

Publish ►

m

Close Database

Publish the database for others

î I Docu ment Management Server

I^JJ Share the database by saving it to a document management server.

Publish the database for others

î I Docu ment Management Server

I^JJ Share the database by saving it to a document management server.

Package and Stgrt i\

Package the database arfa apply a digital signature.

I Access Qptjons |X Exit Access

8. Select the certificate to use on the Select Certificate dialog:

Select Certificate

Select the certificate you want to use, d to Issued by Intended P,.. Friendly name Expiration ,..

¿-'.Helen Fe.,. Helen Fedd... Code Signing None d to Issued by Intended P,.. Friendly name Expiration ,..

OK I I Cancel ¡ | View Certificate

OK I I Cancel ¡ | View Certificate

9. Click Create to save the package file to a location of your choice:

Create Microsoft Office Access Signed Package

\ J X~ j I K Office Book ► Sample Databases ►

T I If Search

I

I ^ Organize ▼ Views ■*■ New'Fofder

Favorite Links Jj Documents

Microsoft Office 2007 Recently Changed Jd Addlns

JJ Custom Templates ^ Default Temptates

Development Jd Download More »

Name E>ate mod ffied Type

Access Ribbon Databases and Add-ins Backups COM Add-in Scripts

VB 6 COM Add-in VB 6 COM Add in (Ribbon) VB 2005 Express COM Add-in VB 2005 Shared Add-in (Ribbon) £ j Northwind.accdc

Folders

Filename: Northwirid Plus.accdc Save as type: f Microsoft Office Access Signed Package f.accdc)

10. If this is the first time you have used this certificate, the Microsoft Office Access Security Notice dialog opens:

Microsoft Office Access Security Notice

^J A potential security concern has been identified.

Mote: The digital signature is valid, but the signature is from a publisher whom you have not yet chosen to trust.

Rle Path: G:\., ,e Book^ample DatabasesWorthwind Plus.accdc

This file may not be safe if it contains code that was intended to harm your computer. Do you want to open this tile?

Show Signature Details

continued continued

11. You can click the Show Signature Details to view the available information about the selected certificate:

Digital Signature Details

General | Advanced |

Digital Signature Information a certificate chain processed, but terminated in a root certificate which is not trusted by the trust provider.

Signer information

E-mail!

Signing time:

|NoI available

|~Not available

View Certificate

Countersignatures

Name of signer: E-mail address:

12. After clicking the "Trust all from publisher" button on the Microsoft Office Access Security Notice dialog, you can work on the database.

13. In future, if you open the package file, you will get a dialog offering to extract the database:

Extract Database To

Helen Feddema , Documents

Favorite Links [j Documents

Ji Microsoft Office 2007

(¿J Recently Changed

Jl Addlns

JJ Custom Templates

£ Defautt Templates

1. Development

Ji Download

Name Date modified

Backups [5] My Data Sources

Offers ^ Share Point Drafts

SQL 5erver Management Studio Up dater 5 . Visual Studio 2005 iij Databasel.accdb

Database3.accdb Data b ase4. accd b

Folders

Northwind Plus.accdb

Save as type: [ Microsoft Office Access ¿007 Databases (*,accdb;*.accde) Hrde Folders

14. On clicking OK, the extracted database opens as a normal Access 2007 database.

Figure 12.2 shows the main menu of the Northwind Plus sample database, with buttons for opening the two main forms, and buttons to select the Documents and Templates folders. The folder selection buttons function like the similar controls in several other chapters, using a Folder Picker dialog created by the Office FileDialog object.

0 0

Post a comment