Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

MS-Access and Mail Merge

Introduction

Address Labels

When we talk about Address Labels, Form Letters, Mail Merge and so on, the name that comes into our mind is MS-Word, loaded with plenty of functions for the above tasks. It needs a database to provide source data for address labels, Form Letters etc. We can prepare a Table in Word Document and use it for Addresses or attach one from Microsoft Access or other database sources.

Here, we are not going to use MS-Word for Address Labels, Form Letters and Mail Merge operations, we will do it in MS-Access. Yes, we will try Mail Merge too.

We may not be able to do fancy paragraph formatting as we do in Word but preparing Form Letters in Access is important in situations like reporting of Agency Agreement Renewals, Bank Guarantee Renewal Reminders or Department-wise Monthly Stationery Expenses or Telephone Expenses for review etc. and needs only one or two standard paragraphs combined with the actual statement, which we can quickly put on the print within no time.


Designing Address Labels

We will start with the simple task, Designing Address Labels. MS-Access has a built-in Label Wizard, which we can use for quick designing of Address Labels of various sizes and shapes. MS-Access has plenty of predefined Label Sizes of different Manufacturers to select. This is good when hundreds or thousands of labels are needed on continuous stationery of predefined sizes.

But, ordinary people like me who would like to print on plain paper, cut and paste on envelopes can try the manual method.

A sample image of the output created from a manually designed Address Label is shown below and we will go through the procedure that needs to prepare them:


The Design Task

  1. Import the Employees Table from Northwind.mdb sample database, if you have not done so far for our earlier sessions. Refer my earlier post Saving Data on Forms not in Table for the exact location reference of the sample file.
  2. Select the Employees Table and select Report from Insert Menu and select Design View.
  3. Go to View menu and remove the check mark from Page Header / Footer and Report Header / Footer options to remove them from the report design, if they are visible.
  4. Draw a Text Box approximately 3.25"' wide at the top and left area of the Detail Section leaving enough space for border as shown in the design given below:
  5. Display the Property Sheet of the Text Box from View menu and set the Special Effect Property = Chiseled. If you don"t like the underlined design, stay with the Flat property setting.
  6. Write the expression as shown above in the Control Source Property. The partially visible field is [LastName]); don"t forget the closing brackets.
  7. Copy the same Text Box and Paste it four times down and write the expression as shown above.
  8. Select all the five Text Boxes together. Select Vertical Spacing - - > Decrease from Format Menu. Repeat the process till all the Text Boxes comes close together.
  9. Draw a Rectangle around the Text Boxes. If the Rectangle hides the Text Boxes then select Transparent from the Fill/Back Color Tool Bar Button. When the Label is cut from plain paper the Border will give the label a proper shape even if the cutting is not.
  10. Now, we must go for the final settings on Page Setup Control. Select Page Setup from File Menu.
  11. Select the Columns Tab.
  12. Change Number of Columns to 2.
  13. Column Spacing change to 0.15"
  14. In Column Size control put check mark in Same as Detail, if it is not already there.
  15. Column Layout: Across, then Down.
  16. Click OK and save the Report with the Name: AddressLabels.
  17. Open the Report in Print Preview, it should look like the sample given at the top of this Page.

Next we will look into setting up and Printing Form Letters.

Share:

2 comments:

  1. Hi there,

    Nice stuff you got, very interesting to read.
    Well, I do have also in my sleeves, if you have time don't forget to visit

    bookoftips.blogspot.com

    Many thanks

    ReplyDelete
  2. Would someone please tell me how to get Access 2007 to print a few hundred address labels on non-Avery continuous (fanfold) stationery? The sheet is 210mmx305mm, the labels are 89mmx36mm, 1 label across, 7 down. Single labels print perfectly; any more, and Access just chucks one out now & then, each a few pages apart!

    ReplyDelete

Comments are subject to moderation before publishing.

Translate



PageRank
Subscribe in a reader
Your email address:

Delivered by FeedBurner

http://clicky.com/101199826
Blog Directory Visit blogadda.com to discover Indian blogs

Search

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email

Labels

Forms Functions How Tos MS-Access Security Reports Class Module msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Array External Links Queries msaccess reports msaccess tips Accesstips Downloads Objects Property Collection Object Event Menus and Toolbars Controls MsaccessLinks Process Controls WithEvents Art Work msaccess How Tos Combo Boxes Graph Charts List Boxes VBA msaccessQuery Command Buttons Dictionary Object Form Report Calculation Command Button Data Emails and Alerts Query RaiseEvent Custom Functions Custom Wizards DOS Commands Data Type Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Item msaccessprocess security advanced Access Security Add Auto-Number Field Type Fields Form Instances Key Macros Menus SubForm Top Values Variables msaccess email msaccess menus progressmeter Access2007 Copy Excel Expression Join Methods Microsoft Numbering System Records Recordset Security Split Table Time Difference Utility Workgroup Wrapper Classes database function ms-access msaccess wizards reference text tutorial vba code Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Conditional Formatting Data Filtering Defining Pages Diagram Disk Dynamic Lookup Error Handler Export External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Tables Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif msaccess msaccess alerts pdf files restore switch toolbar updating upload

Featured Post

Opening Access Objects from Desktop

Frequently Used Methods. Set the Form Name in Display Form Option of Current Database in Access Options . BIG DEAL!, this is the first t...

Labels

Blog Archive

Recent Posts