Software

How do I... Fill Word form fields with Access data?

When you need to bring your Access data into a Word form, a little VBA can expedite the task. Susan Harkins walks through an example to demonstrate the process.

Access reports provide a flexible and easy-to-use tool for sharing data, but sometimes, it's the wrong tool. For instance, you may find that you can't exactly reproduce an existing paper form in Access, whereas you can in Word. Now, you might not care what the report (paper form) looks like, but perhaps management does. When this is the case, you may find it necessary to transfer Access data to Word forms. The good news is that Word and Access play well together.

Note:This information is also available as a download, which includes text files containing the VBA code from Listing A and Listing B.

The automated solution requires three pieces:

  • A method of identifying the Access data you want to transfer
  • A bit of VBA code to automate the process
  • A Word document that represents the form you need to fill.
We'll work with existing data in the Northwind database (the sample database that comes with Access). Specifically, the example will transfer data from the Customers table, via a form, to the Word document shown in Figure A.

Figure A: The highlighted areas are text fields.

Creating the Word form

The Word form isn't as difficult to create as it might look. The example form consists of five simple tables that contain form fields. In this context, a form is a document that contains fill-in blanks or form fields, in which you enter information. A field is a predefined cell for entering and storing information.

The form in Figure A uses five two-row tables to simulate a form. You don't need the tables, but they help organize and manage the data. To insert a table, choose Insert from the Table menu and then choose Table. In the resulting Insert Table dialog box, enter the appropriate number of columns and rows (see Figure B). To create this form, you'll need five tables as follows:

Columns Rows
2 2
2 2
1 2
4 2
2 2

Figure B: Identify the number of rows and columns in each table.

The example form also uses shading and text in the first row to distinguish between the heading and actual information. You can skip the formatting if you like; it isn't critical to the technique. Alter the width of each cell column, using Figure A as a guide, if you like.

The example Word document contains a matching field for each column in the Northwind's Customers table. You don't have to accommodate each field; transfer just the data you need. However, you must associate a Word field with each underlying Access field that you want to copy to the Word form. To add the first field, complete the following steps in the Word form:

  1. Display the Forms toolbar by choosing Toolbars from the View menu and selecting Forms.
  2. In the document, click the table cell beneath the Customer Number heading.
  3. Click the Text Form Field button on the Forms toolbar.
  4. Double-click the field to display the Text Form Field Options dialog box.
  5. Rename the field fldCustomerID and click OK.

Repeat steps 2 through 5 to add a text field for each heading, using the following list to name each field:

Word field heading Field name Corresponding field in Access table
Customer Number fldCustomerID CustomerID
Customer fldCompanyName CompanyName
Contact fldContactName ContactName
Title fldContactTitle ContactTitle
Street Address fldAddress Address
City fldCity City
Region fldRegion Region
ZIP Code fldPostalCode PostalCode
Country fldCountry Country
Phone Number fldPhone Phone
Fax Number fldFax Fax

The field heading doesn't have to match the field names. However, notice that the Word field names match the Access field names, with an additional fld prefix. It isn't necessary to name the Word fields similarly to their corresponding Access fields, but doing so is self-documenting and simplifies your work.

After adding all the fields, click the Protect Form button. Doing so disables a number of features, which will keep casual users from altering your form. Then, save the form as CustomerSlip.doc and close it.

Identifying the Access data

The most common method you'll probably use to identify a specific record is an Access form. To simply the example, use the existing Customers form in Northwind. If you'd rather not alter the actual object, use a copy or use the AutoForm wizard to generate a quick form based on the Customers table. When you have a form bound to the Customers table, add a command button to the form and name it cmdPrint, as shown in Figure C.

Figure C: The Print Customer Slip command button executes the VBA code that will pass Access data to the Word form.

With the form still in Design view, click the Code button to launch the form's module. Add the code in Listing A to the module. The cmdPrint button's Print event does all the work of copying data for the current customer in the Customers form to the Word document (CustomerSlip.doc).

Listing A

Private Sub cmdPrint_Click()

'Print customer slip for current customer.

Dim appWord As Word.Application

Dim doc As Word.Document

'Avoid error 429, when Word isn't open.

On Error Resume Next

Err.Clear

'Set appWord object variable to running instance of Word.

Set appWord = GetObject(, "Word.Application")

If Err.Number <> 0 Then

'If Word isn't open, create a new instance of Word.

Set appWord = New Word.Application

End If

Set doc = appWord.Documents.Open("C:\WordForms\CustomerSlip.doc", , True)

With doc

.FormFields("fldCustomerID").Result = Me!CustomerID

.FormFields("fldCompanyName").Result = Me!CompanyName

.FormFields("fldContactName").Result = Me!ContactName

.FormFields("fldContactTitle").Result = Me!ContactTitle

.FormFields("fldAddress").Result = Me!Address

.FormFields("fldCity").Result = Me!City

.FormFields("fldRegion").Result = Me!Region

.FormFields("fldPostalCode").Result = Me!PostalCode

.FormFields("fldCountry").Result = Me!Country

.FormFields("fldPhone").Result = Me!Phone

.FormFields("fldFax").Result = Me!Fax

.Visible = True

.Activate

End With

Set doc = Nothing

Set appWord = Nothing

Exit Sub

errHandler:

MsgBox Err.Number & ": " & Err.Description

End Sub

How it works

First, the code creates an instance of Word. If Word is already open, it uses the currently running instance. The current code uses the expression Err.Number <> 0 because Microsoft is notorious for changing error numbers from version to version. You might want to be more specific by changing 0 to 429 (or the appropriate error number for your version if different).

Next, the Open method identifies the Word form (document). In this case, that's CustomerSlip.doc in the WordForms folder on the C:\ drive. The optional True value opens the file as read-only. Update the path to accommodate your system. In fact, you might consider moving the filename and path to constants in the General Declarations section. That way, you can more easily update the code if you change the filename or move it.

The With block copies the data from each Access field to its corresponding Word field. After copying all the data, the Visible and Activate methods display and then select the filled in Word form, as shown in Figure D.

Figure D: The form will look like this after all the data is copied and filled.

The error handling is minimal. Be sure to thoroughly test your production solution and accommodate all possible errors. The most common errors you'll run into are a missing Word file, an incorrect path, or mismatched field names.

Filling multiple Word forms

Currently, the example works with only one record, the form's current record. Sometimes, you'll want to pass multiple records. Fortunately, it isn't difficult to enhance the existing code to handle multiple records and forms, as shown in Listing B.

Listing B

Private Sub cmdPrint_Click()

'Print customer slip for current customer.

Dim appWord As Word.Application

Dim doc As Word.Document

Dim rst As ADODB.Recordset

'Avoid error 429, when Word isn't open.

On Error Resume Next

Err.Clear

'Set appWord object variable to running instance of Word.

Set appWord = GetObject(, "Word.Application")

If Err.Number <> 0 Then

'If Word isn't open, create a new instance of Word.

Set appWord = New Word.Application

End If

'Populate recordset object.

Set rst = New ADODB.Recordset

rst.Open Me.RecordSource, CurrentProject.Connection

'Cycle through records to fill Word form fields.

Do While Not rst.EOF

Set doc = appWord.Documents.Open("C:\WordForms\CustomerSlip.doc", , True)

With doc

.FormFields("fldCustomerID").Result = rst!CustomerID

.FormFields("fldCompanyName").Result = rst!CompanyName

.FormFields("fldContactName").Result = rst!ContactName

.FormFields("fldContactTitle").Result = rst!ContactTitle

.FormFields("fldAddress").Result = rst!Address

.FormFields("fldCity").Result = rst!City

.FormFields("fldRegion").Result = rst!Region

.FormFields("fldPostalCode").Result = rst!PostalCode

.FormFields("fldCountry").Result = rst!Country

.FormFields("fldPhone").Result = rst!Phone

.FormFields("fldFax").Result = rst!Fax

.Visible = True

.Activate

'.PrintOut

'.SaveAs "'" & rst!CustomerID & "'"

rst.MoveNext

End With

Loop

Set doc = Nothing

Set appWord = Nothing

Exit Sub

errHandler:

MsgBox Err.Number & ": " & Err.Description

End Sub

Instead of retrieving data from the actual form (using the Me identifier), this procedure uses a Recordset object to pass data from multiple records. Be sure to update the .FieldForm statements by referring to the recordset (rst!) instead of the Access form (Me!), as the previous procedure did.

Our example uses the form's RecordSource property to retrieve data from the Access form's underlying data source (the Customers table). Consequently, the form isn't synchronized to the procedure, so the form's current record never changes. We're just using the form's recordset and print button to keep the example simple. When applying this technique to your database, you can use any valid recordset source.

Notice that the code has two commented statements toward the end of the With block. Those are actions you might possibly want to take for each Word form. The first prints the completed form; the second saves the form to the current directory using the current record's primary key value as the form's name. You could use either or both. If you don't save the current record, Word dumps it when the Do While loop processes the next record.

The downside is performance. If you're working with thousands of records, this process will take a few minutes to complete.

Shortcut to Word forms

Filling a Word form with Access data can be a useful when your data is in Access and you must use Word's formatting features to create just the right display. Fortunately, the process is simple, whether sharing one record or thousands.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

74 comments
xczar0000
xczar0000

Well this isn't working at all - besides it's not Me! but Me.

bywhdcw
bywhdcw

I created my vba with this code from example A and it works great.  I am getting the data from a report in which I have grouped data.  My question is how do I get all the comments that are under a group heading of Instructor.  Using the code, I only get the first comment, I don't see how I can identify each comment. I see in Example B how to do that from a dataset or a recordset, but not from a report

Sledge489
Sledge489

I have an Access database set up for a scholarship fundraise. Currently when we receive a pledge the persons record is either called up or created, the amount of the pledge is entered, and then a button in the form is hit to generate a letter to the person using a report we have created. The form is very difficult to modify or manipulate and I would like to be able to send the pertinent data directly to a Word document.

What you have described sounds similar to what I am wanting to do with the exception that I was wanting to be able to populate a Word document with data from an open record in my Access database using mail merge fields set up in the document. The fields are Name, Add, Cty, State, Zip and pledge amt.

Is it possible to use the mail merge fields rather than create field as you describe?

I realize that I can simply open the word document within Word and then do a mail merge, but I need to print just one record and letter at a time as quickly as possible so I can go on to the next record and enter the new pledge into that record and print it as well.

I know basically nothing about creating VB, but I can rename fields or document names as needed and I can cut & paste code as long as I know where to paste it.

If this is possible, I would greatly appreciate the help.

CeannC
CeannC

In 2010 versions:

You can use bookmarks to replace the text.

.ActiveDocument.Bookmarks("Customer_Name").Select
.Selection.Text = Forms!FrmCreate!Customer_Name.Value

fishmanm
fishmanm

Helo! Im using Word 2010 and Access 2010 and can not seem to make the code work. the Word document is opening but that is it! Does anyon has an answer?

rankhornjp
rankhornjp

Any ideas on why I would have to double click the button to get the Word document to open? Script works GREAT, except for that! Thanks

statca
statca

Hi all, New to access and VB, I used the first script in the article to fill a word form with access data. Works great until I want data from a second table. I am sure it is just a minute problem but it keeps me far away from my goal. I would be very thankful for some help!!

bestleonard
bestleonard

It looks like there is a document written for each record. I need all records written to one document, obviously with a page break between records.

jeffoiler
jeffoiler

my Word database field is pulling it's data from Excel. The data pulls correctly when it's NOT being loaded into a Word table, but when I try to load that same data in a Word table, it gives the error 'Error! Not a valid result for table.' Any ideas how to get data to load within a Word table? BTW, the data I'm loading is only a 1 cell number.

warroyave
warroyave

I have followed the instructions in the article by Susan Harkins, however I am having trouble with Memo Fields. If the field contains less thatn 255 characters, it fills in the word bookmark fine, if it contains more than that, it fails to fill in. Any solutions?

jayke001
jayke001

I think this is a great article/blog. I'm happy and glad that Techrepublic makes these past articles available/republishes them for us. I am using Windows XP, Word and Access 2003. References include: Microsoft Word 12.0 Object Library and Microsoft Office 12.0 Object Library. My problem was not in seeing the Word document after running the code. The solution was a line of code, the new line has the astericks at the end of it: Set appWord = GetObject(, "Word.Application") If Err.Number 0 Then 'If Word isn?t open, create a new instance of Word. Set appWord = New Word.Application End If appWord.Visible = True '*** Set doc = appWord.Documents.Open("C:\Documents and Settings\user\Desktop\AccessTransferTest.doc", , True)

ddejarnett
ddejarnett

I followed Susan's instructions exactly, but I cannot get data to populate my Word form. Word opens, but no data appears. Can anyone point me in the right direction for correcting my issue? Thanks!

carlbjames01
carlbjames01

This is working for me perfectly. Really appreciate the article. I need the exact same process for an EXCEL form. Is there similar code out there I could reference? I've looked around, but can't find it.

wilfredo.molina
wilfredo.molina

Howdy. I need to apply this code to print different word documents using check box.

wilfredo.molina
wilfredo.molina

I need to apply this code to print differente word documents.

Tink!
Tink!

I want to fill in multiple records on the same Word document? Such as a sheet of labels using records from the Access DB. Or a custom report that takes data from Access and lines it up in the Word document.

mlevit
mlevit

Hi, I've been trying to get it to work with Office 2007 but it doesn't seem to want to. The Word document opens but none of the form fields are filled in from the Access database. Any ideas? Thanks

dave.lawson
dave.lawson

This looks great, but I get a compile error when I try to run the code at .......... Dim appWord As Word.Application Dave

skeelie
skeelie

I have tried this with Word 2000 and vb.net 2003 and it doesn't work. My code variation that sort of works is: .FormFields.Item("ProjectN").Result = rs.Fields("ProjectN").Value I have searched for over a month trying to figure this out. I only have 8 fields, and about 20 REF's, In the final doc, the data from the table fields don't match up with the result of the doc fields, My Refs never update(I do have calculate on exit checked). And, Word Object model in vb.net insists I use a .DocumentClass with the Formfields but there is no Formfield I can use during coding. I'm sure it can be done with Word 2000 , (?) finding documentation and examples for word2000 programming is not easily found. Can someone Please, please help me! Thanks

msigsworth
msigsworth

How would I modify this code to allow for looping of records within a single form, rather than creating a new Word form for each record? For example, assume multiple but unknown number of phone numbers for each client.

icharalampidis
icharalampidis

Hi I used the above code, but my MSword application is not opening. What am I doing wrong?

strannik
strannik

Ok, I am trying to figure out the syntax for a multiple table SQL nested JOIN, and I am either getting syntax errors or the latest: "Too many fields" error. I cannot find what is wrong. Here is the query I am trying to get right: SELECT EE_REPORT_DATA1.*, EE_REPORT_DATA2.*, EE_REPORT_DATA3.* FROM ((EE_REPORT_DATA1 INNER JOIN EE_REPORT_DATA2 ON EE_REPORT_DATA1.ID = EE_REPORT_DATA2.CHILDID2) INNER JOIN EE_REPORT_DATA3 ON EE_REPORT_DATA3.CHILDID3 = EE_REPORT_DATA1.ID) WHERE EE_REPORT_DATA1.ID = vRPT_ID; Any clues? Thanks, John

strannik
strannik

I have a word form with about 300 fields (so it won't fit in 1 table - I use 3 instead). Currently I have it working where on submission, it opens 3 recordsets in a row, and fills in fields with part of the form in each case. The primary key in table 1 (replication id) is duplicated in a "child_id#" field in the other two tables. When I try to use a similar method to pull the data back into the form, the first part of the form fills in OK, but it can't find the 2nd and 3rd part. I am using the following method to get the data: 'enter the id to fill in the form: vRPT_ID = ActiveDocument.FormFields("RPT_ID").Result vGetSql = "SELECT EE_REPORT_DATA1.* " & _ "FROM EE_REPORT_DATA1 " & _ "WHERE EE_REPORT_DATA1.[ID]='" & vRPT_ID & "';" vRecordSet.Open vGetSql, vConnection, adOpenKeyset, adLockOptimistic With vRecordSet If Not .EOF Then 'Yes then fill in the form to the database stored values If vRecordSet("ID") "" Then vRPT_ID = vRecordSet("ID") If vRecordSet("Date") "" Then vText1date = vRecordSet("Date") If vRecordSet("Report") "" Then vText2Report = vRecordSet("Report") 'etc. etc. 'Fill form field's bookmarks ActiveDocument.FormFields("RPT_ID").Result = vRPT_ID ActiveDocument.FormFields("Text1date").Result = vText1date ActiveDocument.FormFields("Text2Report").Result = vText2Report ActiveDocument.FormFields("Text3Toolname").Result = vText3Toolname 'all going well so far Else MsgBox "Sorry, No matching records found!" End If End With vRecordSet.Close vConnection.Close Set vRecordSet = Nothing Set vConnection = Nothing 'DB SECTION 2 'Make a connection to the database (copy & paste) vConnection.ConnectionString = _ "data source=" & vPath & "\" & vFileName & ";" & _ "Provider=Microsoft.Jet.OLEDB.4.0;" 'Open the connection for data exchange (copy n paste) vConnection.Open 'Test Connection and let user knows it is working. ? 'Change if this message is annoying vConnectionState = vConnection.State If vConnectionState 1 Then MsgBox "Cannot connect to the database!", vbCritical End If vGetSql2 = "SELECT EE_REPORT_DATA2.* " & _ "FROM EE_REPORT_DATA2 " & _ "WHERE EE_REPORT_DATA2.[CHILDID2]='" & vRPT_ID & "';" vRecordSet.Open vGetSql2, vConnection, adOpenKeyset, adLockOptimistic 'if a match is not found then show it With vRecordSet If Not .EOF Then 'fill in the form to the database stored values If vRecordSet("CHILDID2") "" Then vID_PROP = vRecordSet("CHILDID2") If vRecordSet("C1") "" Then vDropdown1 = vRecordSet("C1") etc. etc. The first part of the form fills in fine, and then it can't find the 2nd table stuff. The CHILDID2 is identical to the vRPT_ID. I know there has to be a better way to do this than sequential access to tables, but if I can get it to work this way it would be better for now - any ideas? Thanks, John

Rahul_Bharaktiya
Rahul_Bharaktiya

after putting all the information the Word form is not merging the form value in to the word document,it is just opening the word form nothing else

tfos
tfos

Help I can get the VB to compile but the form comes up with the field names, not the data. Maybe the form is wrong or I need another Reference Library? Here are my VB references: Visual Basic for Applications Microsoft Access 11.0 Object Library OLE Automation Microsoft DAO 3.6 Object Library Microsoft Word 11.0 Object Library I have the form - with the "type" as text and the put the "default text" as fldCustomerID on the form. Is this what you meant by "renaming the field fldCustomerID?" There was no picture once I followed the Form properties. Thank you

ryanjhofman
ryanjhofman

Great !! but how do you get checkbox's to check- I have an access Form with a checkbox and when the box is checked, i get the value to check the box and the box on the form field does not check - can anyone "check" this out for me please :) thank you!

eva
eva

I too tried the methods mentioned above and was unsuccessful. When I used the Listing A code, I get a Microsoft Visual Basic error "Compile Error: User-defined type not defined" and the code "Dim appWord As Word.Application" is highlighted. I get this error after I click on the print button I created in Access. When I tried using Listing B code nothing happens. No error, but nothing gets populated in my Word document. I'm currently running Office 2003. Please help. Thank you in advance. Aloha!!!

Renemote
Renemote

Is this code actually for Office 2003, I followed the instruction for List B a few times now and no go. Could the code be related to an older version of Office/window than XP SP2 and Office 2003?

h.lancaster
h.lancaster

Can subform data populate a Word form, too? I'm setting up my Word form with matching fields from an Access form, but I would really like the sub-form fields to be included as well. Is this possible, please?

Renemote
Renemote

I've been using the script below with Office 2003 Professional to send data from access 2003 to a Word 2003 doc, with out success. All I done is change the name of the word fields. Can anyone offer a solution? Dim appWord As Word.Application Dim Doc As Word.Document On Error Resume Next Err.Clear Set appWord = GetObject(, "Word.Application") If Err.Number 0 Then Set appWord = New Word.Application End If Set Doc = appWord.Documents.Open("H:\SafeCheck\Client file\Customerslip.doc", , True) With Doc .FormFields("FldSelExCM").Result = Me!SelExCM .FormFields("FldSelAddCM").Result = Me!SelAddCM .Visible = True .Activate End With Set Doc = Nothing Set appWord = Nothing Exit Sub Errhandler: MsgBox Err.Number & ":" & Err.Description

charles.levea
charles.levea

Is it possible to create an Access database from data entered into word form fields?

Editor's Picks