Download Sample Access File: MS Access Vs MS Word

MS Access is a database based program, but MS Word is a graphical word processing program that users can type with. The purpose of the MS Word is to allow the users to type and save documents. MS Access can be programmed to work with MS Word by sending data from Access to Word documents instead of typing and also to save to word or PDF then send to another user via email. The communication between these two programs can be done by using VBA and bookmarks.

Highlight functions or features in this sample file include:

1. Fill in Check Box in Word. The option from combo box in Access can be filled in with the check box in Word. We need to create a check box in Word corresponding to the list/value in combo box in Access. For instance below, we have two options for Gender in combo box and we create two check boxes in Word. If a customer is a male the when we fill in word, only the male check box is marked.

2. Search and Fill in Word. This form will also provide a search by keyword function and fill in the information for each customer in a word template form. The information will be filled in word with the Text Form Fields method. For example, we fill a customer name into a text form field name txtcustomerName with VB code like: “.formfields(“txtcustomerName”).result = [CustomerName]”. This form also shows a filling a value of combo box that need to look up the selected value of combo box from another table. For instance, the value of Individual customer type in this form is 1 as it stores only the primary key of customer type from a customer type table. However, the customer type is Individual for customer type ID 1. If we put “.formfields(“txtcustomerType”).result = [Customer_Type_ID]”, the result in word will be 1. So we have to use Dlookup the customer Type from the customer type table and replace it with Individual word to be understandable.

3. Fill in Word and Convert to PDF file. It will provide the example VB code of how to fill data in word then convert this word file to PDF file. It also shows the example of saving a PDF file name as a customer name shown on the current form.

4. Fill in Word and Convert to PDF then Attach PDF file to Email. Other than filling in word and converting to PDF file, this form is providing the function of emailing the PDF file to the current customer if this customer has an email address. Also you can be delete the PDF file after emailing if you want with the VB code “Kill” provided in this function.

5. Create Table List in New Word Document. This form provides a function of creating a table in a new word document with the data from the Access form. We can specify which field of data in Access that we want to send to the table in Word by using SQL Select Statement. For example below, we select a customer type “Individual” from the drop-down combo box then click on Fill word button. The list of customer with individual customer type will be inserted into a table in a new word document.

6. Fill in Table after Bookmark in Word. Normally, when we create a table in Word with VBA the table will be created at a top or first line in word document. We can program it to have the table created after a certain point in Word document called “Bookmarks.” That means we can insert a table with data from Access to Word after the certain bookmarks in word document. For example below, I set a bookmark names “customerList” the next line after word Customer List. The table of customer list will be inserted after the Customer List:

7. List Customers After Bookmark in Word. This form will provide the example of inserting a list of customers after the bookmark in word. We can insert a list of many customers under one bookmark by using Loop and Recordset. We can make a space or comma for each field of data using tab or (,) in the VB code.

8. Fill in Word with Format for Date, Currency($), Percent(%) and Calculated Value. There are two ways to format field in word when the data is sent from Access. We can pre-format on the Fieldform on word by double click on the field name to bring up the Text Form Field Options then format the text type and follow by the format of that text type.

Another way is formatting in the VBA with the format function before sending to the field form in MS word like “ .formfields(“txtDateLong”).result = Format(DOB, “long date“)” There are different types of formatting in the picture below that formatted by VBA.

9. Save as a New Word Document. This sample file also provides a sample of saving the word template as a new Word document with a name of customer. After clicking on the Save As New Doc button then a new Doc file names Bob White is created in the same folder of the sample file. You can save to a new location if you want to by changing the location of new file.