Getting started: create documents in MS Access

Here we give an overview of how to create documents in MS Access using our software. If you have not yet done so, download it here.

Table of contents

Introducing the four demo databases

As part of the installation four MS Access demo databases are installed in the Documents\4TOPS Document Creation Demo subfolder. The first two demo databases display what typically can be achieved. Note that these are compiled databases (.accde) meaning you cannot access the VBA code or create new processes and consequently the ribbon is reduced.

Before you can use the demo databases, you need to unblock them: right-click the accdb/accde file and go to Properties followed by selecting the Unblock checkbox.

  • DemoFeatures.accde shows what elements (data, lists, tables, images, etcetera) that can be controlled in a particular document type – Word document, Spreadsheet, presentation or email.
  • DemoApplication.accde shows document creation applies to a single and multiple records, automatic saving of the documents and recording and retrieval of created documents.

The third and fourth are fully open for you to get acquainted with the software. They are limited versions of the first two: they only show a Word process for creating a single document without automatic saving.

  • TryIt-Simple.accdb simplified version of DemoApplication. Here you see how an invoice document, with a characteristic invoice lines table, is filled from the database.
  • TryIt-Advanced.accdb is an open version of DemoFeatures. Here you see how the the special features are done: images, lists, checkboxes, hyperlinks and so on.

As the name suggests TryIt-Simple.accdb is the best place to start your first trying out of the document creation software. Alternatively, if you are feeling lucky, you can start directly on a database you want to use the document creation software with. Make a copy of your database first – just in case!

The rest of the current page will be dedicated to creating a simple Word document creation process using it.

Using the TryIt-Simple.accdb database

The TryIt-Simple.accdb database lets you experience how to create a document from an access database yourself. Being in .accdb format, you have full access to the code – but it contains only a subset of what is implemented in the Demo database. Like with all non-compiled database versions, this database shows the full Document Creation ribbon tab. Here you can get acquainted with creating new document creation processes using the wizard and the template designer.

Note that without a license you can only limited to making a Word Document Creation process, and that this can only create a single document at a time, and automated saving is not included.

document creation ribbon

Open the TryIt-Simple.accdb database

When you open the TryIt-Simple.accdb database the autoexec macro opens the main demo form Invoice. Additionally, a message pops up pointing to the extra ribbon tab Document Creation (image above) which is made available by the 4TOPS add-on you installed.

Look at the example document creation process

On the ribbon tab Document Creation, press the Create Documents button. In the dialog that opens, select ‘Simple Invoice for current record’ and press OK.

The document creation process is started. When ready a new Word document window opens showing the newly created Word document.

It is important to notice here that the content in the created document reflects the form – the source of the data. Further, the document table contains the data from the InvoiceLine subform.

Now make a document creation process yourself

Here we will make a Word invoice. Again we start with the Invoice form open.

The most fundamental feature you require is the transfer of data in your Access database into a template at specified locations. Let’s try:

Define the Template

  1. On the ribbon tab Document Creation, press the Word Wizard button. This opens the wizard dialog on the Template page.
  2. Give a descriptive name, select a sample document which you intend to automate and select a suitable template folder.
  3. Now press the ‘Create’ button. This will open the Word Template Designer (image below) where you drag the fields into the appropriate locations in your new template. When ready, close the Designer.
  1. Now the Preview button has become enabled. Pressing it will create a document filled with data from the current record of your data source. You will use this to see if what you did so far is sufficiently well, or whether you need to further Edit it. When satisfied, press Next.

Use default: process single record

Now the question is posed if you want to create processes for a single selected and/or all records. We will ignore this and just go for the default: Create a document for a single record.

Name the document creation process

The document creation wizard adds a VBA procedure to your database that produces documents. You obviously can call the procedure from your own code, e.g. using a button on a form. Alternatively, you can use the Create Documents dialog started from the ribbon – same as before. Also, you can choose to have a macro created (checkbox) which calls the procedure as yet another way to start the procedure.

The minimum specification now is in place. We press Next a few time (ignoring the possibility to specify the required automatic saving of documents – only available to licensed users) and Finish. The wizard does the required background changes to your database such as setting references to the Word library and adding VBA code.

Run the newly created process

You just finished creating your first document creation process. Running it (ribbon, Create documents) should get you roughly the same result as before.

Make changes to the template

In many cases, sooner or later, you will decide that your template needs revision.

  1. From the ribbon, press the Manage button. This opens the Manage Documents Creation processes dialog.
  2. Select the process you want to change the template from.
  3. Press Edit – this opens the Template Designer.

After changes have been made the data transfer VBA code is automatically updated!

Manage processes.

What’s next?

Having completed the creation of your first Word document creation process, including preview and revisions, there are several places to go next, depending on your interest.

  • If you want to see other Office applications (Excel, Outlook, PowerPoint) being created, open DemoApplication.accde. Here also the possibilities to process multiple records and different ways to integrate the document creation processes in your application in display- see the next section.
  • If you are specifically interested in advanced features available for Word document creation visit TryIt-Advanced.accdb. Here you can create a Word document showing the many features the Word filling process supports (Table, list, pictures, text fragment insertion, checkboxes, highlighting and many more). In addition you can see tricks such as the use of the Tag property on form controls to signal the Template Designer what is the appropriate use of a piece of data, e.g. Picture to indicate an image file.
  • If you want an overview of the document filling features for all Office applications, DemoFeatures.accde is the place to find out. Here you can move to the record for a selected Office application and using the button Create document instantly see what features apply – and verify they actually work.
  • And, of course, try it on your own database application!

DemoFeatures.accde: Office, automation and integration

Open DemoFeatures.accde to see what can be achieved, both in terms of the different Office documents (Word, Excel, PowerPoint) it creates, the many process features such as which records to process and automatic saving of documents and the level of integration that can be achieved. This demo database is in .accde format, meaning you can’t access the underlying code nor add new processes.

Users with a license can download the DemoFeatures.accdb version to see how things are implemented – notably the integration features. (In addition, the database shows some useful Access techniques that may be useful to you: using a tab control to make the form design better organized, using the split form to combine access to the form details with navigation from an overview datasheet and using form events and queries to update aggregate values in a parent table.)

ms access demo database

Extra features for licensed users

In the following I will give a short introduction to the additional features. The best way to get further acquainted is by continuing in the wizard. Explanation is available when you first select the choices that match your requirements and then on the appropriate wizard page click the ? button top right or press F1 which will get to the online page which elaborates on it. As said, for the ultimate joy of having the document creation process added to your project you will need a license.

Create multiple documents at a time

A common requirement is to create a document for each of multiple records in one go. The normal case is that you create documents for all records – possibly after having applied a filter. Additionally, the document creation software allows you to create documents for a subset consisting of records selected using Access record selector.

create documents in MS Access - single, for selected or all records

Automatically save documents

Checking the ‘Save created documents’ box on the above wizard saving options page gives you a plethora of options. Possibly the most important is that you can automatically save documents in a folder under a name based on field values as in the image below.

To make the document creation process produce a Pdf file, check ‘Specify save as type’ on the saving options page. This will add an extra page that lets you select from the supported types.

Another important question is what to do when documents with the same name are created. You can choose to automatically overwrite, but the wizard supports other solutions such as adding a suffix.

under what name to save the documents

Record documents in a table

Having the created documents easily accessible can be achieved by checking Record documents in the Save page of the wizard. This adds a table Documents to the database as a default location to store the typical information, on who created the document and when, and also a hyperlink giving direct access to the document. This uses the ‘RecordDocument’ procedure in the added ‘DocumentsTable’ module, which you are free to adapt as you like.