Populating word form fields from access (1 Viewer)

Jabba

New member
Local time
Today, 10:32
Joined
Jan 10, 2014
Messages
8
I have tried to search the forums for an answer to this but to no avail. Apologies if this is the wrong forum but here goes.

I have an Access database where on click of a command button it populates form fields in a word template doc. Up to now this has been working fine, but it's only on a one to one basis i.e. field 1 goes to formfield 1, field 2 goes to formfield 2 etc etc. Now I need to modify it so that it can cater for where a user may enter 2 or more records. As an example, say a form relates to an individual's personal data, and they need to provide details of their children. They may have 1,2, 3 or more children. Without knowing the number of children in advance I can't prepare the template accordingly. So, can I create form fields in a word doc from Access on the fly, depending on the number of records entered by the user?

Thanks in advance.
 

pr2-eugin

Super Moderator
Local time
Today, 10:32
Joined
Nov 30, 2011
Messages
8,494
Hello Jabba, Welcome to AWF :)

What you need might be a bit more complicated. The solution I can think of is to create a Query that will have all the Children information along with the parent. Something like.
Code:
parentName  /  parentAddress  /  child1Name  /  child2Name  /........./  child[COLOR=Red][B]X[/B][/COLOR]Name

NOTE: Do this in a Query, do not store data like this in the table.

Then use this Query as the Source to the Document. The Word document can have the Merge fields predfined. So it should not be a problem I guess.

Please also wait for other replies as they might be far better. Good Luck !
 

Jabba

New member
Local time
Today, 10:32
Joined
Jan 10, 2014
Messages
8
Thanks for your reply Paul. However, it's not the access side of things I'm not clear on. I have my recordset in Access (2003) that I'm using to get the data that I need to populate the form fields with; this is formed of 2 tables linked on a one-to-many relationship. The issue I'm having is that somteimes this may contain more than one record; if that is the case I need to create another set of form fields for each additional record in the word doc to accomodate this. I know I could create all the form fields in advance, but i) I don't know the maximum number of records that could be entered and ii) say I need to cater for max 10 records, I don't want to leave big gaps between sections of my document when only one record needs to be displayed. I hope that all makes sense.
 

gerry@docshop.ie

Registered User.
Local time
Today, 10:32
Joined
Jun 19, 2013
Messages
41
Hi Jabba,

I think you have two options here.
1. create a report which replicates your word form, use a sub report to return the variable number of associated sub records. The Report can be saved as a Word document if required.

2 Create an Access VBA Module which would create a word document based on the data. I have a database which creates word documents with variable numbers of records, it is not very difficult if you are moderately familiar with VBA.
 

Jabba

New member
Local time
Today, 10:32
Joined
Jan 10, 2014
Messages
8
Thanks Gerry.

I actually ended up going the route of your first option. The users need to email the resulting doc so they can simply 'Send to>Mail receipient as pdf'. However, I would be interested in your vba code to create word docs with variable no. of records, as this would also give me the option of automatically saving the doc to a pre-defined location, which may be of use to my users. If you can share a sample of code that would be great.
 

gerry@docshop.ie

Registered User.
Local time
Today, 10:32
Joined
Jun 19, 2013
Messages
41
Hi Jabba,

My Access DB creates weekly Checklists, my customer has a variable number of items in a daily, and weekly section and has an optional table at the bottom of the document. My program inserts a header and footer and then uses the entire remaining space with tables which contain checklist items. Inserts my branding and customer branding if available in the customer file. It used to take me 40 mins after entering the data to have my final document if the customer made a change (minor meltdown as another 30mins to fix - it now takes the computer under 3 mins to generate or regenerate and I can go and pick up a coffee while the computer is busy doing the work.

I have quickly gone through my code to get the main points, this is not a working copy but if you are in any way competent with VB in Access then you should be able to get operational very quickly. Below I have the elements of code to create a reference to MS Word and open a new blank document. How to modify a Style and Create a new Style, save the file, export to PDF, set page size and margins. If you have a specific how to question please feel free to get back to me.

To get the code as to how to perform a specific task I went back to my Manual documents, use the ‘Record a Macro’ and then manually do the individual tasks. Look at the resulting macro in word and edit to my needs. You need to put the ‘Wordapp.’ In front of the word macro lines where you see ‘SELECTION’ or ‘ACTIVEDOCUMENT’ when putting them into VBcode in access.

I have references to
Microsoft Word xx.x Object Library
Microsoft Office xx.x Object Library
Microsoft Activex Data objetcs x.x library

You also need to create and connect to your recordset which are accessed normally

‘Reference Word
Dim wordApp As Word.Application
Dim MyDoc As Word.Document
Dim mySection As Word.Section

Set con = Application.CurrentProject.Connection
Set wordApp = CreateObject("Word.application")
Set MyDoc = wordApp.Documents.Add

With MyDoc
.EmbedTrueTypeFonts = True
wordApp.Visible = True

‘if the resultant documents are very large then having these setting turned off improves performance, set them to true again before closing your program.

With wordApp.Options
.CheckGrammarAsYouType = False
.CheckGrammarWithSpelling = False
.CheckSpellingAsYouType = False
.SaveInterval = 0
End With

End With
‘PgOrientation comes from my database
With wordApp.ActiveDocument
With .PageSetup
.PaperSize = wdPaperA4
'create the landscape page
If PgOrientation = "L" Then
.Orientation = wdOrientLandscape
.TopMargin = 20 ‘These measurements are all in Points 1 cm = 28.346456693 points
.LeftMargin = CentimetersToPoints (.7) ‘7mm = 20 points approx
.HeaderDistance = 34
Else
'Create the portrait page
.Orientation = wdOrientPortrait
.TopMargin = 20
.LeftMargin = 34
.HeaderDistance = 20
End If

.SectionStart = wdSectionNewPage
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.VerticalAlignment = wdAlignVerticalTop
.BottomMargin = 20
.RightMargin = 20
.FooterDistance = 20

End With
‘Edit an existing style
'Modify Normal Style - regular document text
With .Styles("Normal")
With .Font
.Name = "Arial Narrow"
.Size = 10
.Underline = wdUnderlineNone
.UnderlineColor = wdColorAutomatic
.Color = wdColorAutomatic
End With

With .ParagraphFormat
.SpaceBefore = 0
.SpaceAfter = 0
.LineSpacingRule = wdLineSpaceSingle
.Alignment = wdAlignParagraphLeft
.FirstLineIndent = 0
.OutlineLevel = wdOutlineLevelBodyText
.LineUnitBefore = 0
.LineUnitAfter = 0
End With
End With

'Create a new style
With .Styles.Add("Normal Indent1").Font
.Name = "Arial Narrow"
.Size = 10
.Underline = wdUnderlineNone
.UnderlineColor = wdColorAutomatic
.Color = wdColorAutomatic
End With

With .Styles("Normal Indent1").ParagraphFormat
.LeftIndent = 85
.SpaceBefore = 0
.SpaceAfter = 2
.LineSpacingRule = wdLineSpaceSingle
.Alignment = wdAlignParagraphLeft
.FirstLineIndent = -85
.OutlineLevel = wdOutlineLevelBodyText
.LineUnitBefore = 0
.LineUnitAfter = 0
End With

End With

‘this line types my customers address on a single line
wordapp.Selection.TypeText Text:=rs2!CustName & (", " + rs2!CustAddress1) & (", " + rs2!CustAddress2) & (", " + rs2!CustTown) & (", " + DLookup("County", "Counties", "Countyid = " & rs2!CountyId))

wordapp.Selection.TypeParagraph ‘create a new para

‘Apply a style - Always type the text first and apply the style after
wordapp.Selection.Style = ("Heading 1")

‘insert a Word Date field (Insert Date/Time from the word menus)
wordapp.Selection.Fields.Add Range:=wordApp.Selection.Range, Type:=-1, Text:="DATE \@ ""yyyy"" ", PreserveFormatting:=True

‘Save the file
wordApp.ActiveDocument.SaveAs2 FileName:= “My file Path\My New File Name.docx”

‘Export to PDF
wordApp.ActiveDocument.ExportAsFixedFormat OutputFileName:='Path\fileame', ExportFormat:=wdExportFormatPDF, OpenAfterExport:=False, OptimizeFor:= _
wdExportOptimizeForPrint, Range:=wdExportAllDocument, FROM:=1, To:=1, Item:=wdExportDocumentContent, IncludeDocProps:=False, KeepIRM:=False, _
CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, BitmapMissingFonts:=False, UseISO19005_1:=False


'Exit and close and clean up
wordapp.activedocument.close 'options to save changes or blank for user prompts
wordapp.quit
set wordapp = nothing
 

Jabba

New member
Local time
Today, 10:32
Joined
Jan 10, 2014
Messages
8
Thanks Gerry. That is very comprehensive and very helpful. Cheers!:)
 

Jabba

New member
Local time
Today, 10:32
Joined
Jan 10, 2014
Messages
8
Hi Gerry,

I finally got some spare time to take a look at your code. Whilst it's great if I want to create a new doc from scratch, the problem I have is that I have to use a template that I have been given which is 30-40 pages long. The doc is an insurance policy so the vast majority is just wording/jargon, with one or more pages required for each subject being covered under the policy - starting at page 2. All the data for each subject is contained within the database, but I'm struggling to work out how I can loop through the records and insert a page for each record required at the right point of the document i.e. page 2. I did try the Access report route but the document contains a lot of formatting that access can't handle, so I'm having to "fudge" my way through it and it's taking ages :banghead:- hence why I'm revisiting this. Is it possible?
Thanks
 

gerry@docshop.ie

Registered User.
Local time
Today, 10:32
Joined
Jun 19, 2013
Messages
41
The document opened can refer to being a new document based on any template. Set MyDoc = wordApp.Documents.Add("Templatepath\Filename.dotx")

My document is also made up of optional additional pages and these are easily inserted at the cursor location

wordApp.Selection.InsertFile FileName:="InsertedFileName", Range:="", ConfirmConversions:=False, Link:=False, Attachment:=False

In your case I would suggest creating bookmarks in the Template to set the locations in your document where you wish to place the variable data. then use

wordapp.Selection.GoTo What:=wdGoToBookmark, Name:="MyMookmarkName"

to position the cursor in the correct location and then insert data from your recordset.
I hope this helps. I may be of more help if you could send me a copy of the master document and insert some random text in colour where variable data is required.
 

Jabba

New member
Local time
Today, 10:32
Joined
Jan 10, 2014
Messages
8
Thanks Gerry. I use vba a lot but I wouldn't say I'm an expert, especially when it comes to interacting with other Office apps.

I'm coming up against an issue when I try to insert the file (breaks on code in red below). I get an error message saying "This method or property is not available because the doc is locked for editing".
My code can be summarised like this:

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

With appWord
Set doc = .Documents.Open(DOC_PATH & strDocTemplate, , True)

strFileName = Me!PolicyNo & " - Policy"
strPolFile = strAttachDocPath & strFileName

With doc
.FormFields("PolicyNo").Result = Me!PolicyNo
etc etc......
.Fields.Unlink
.Protect wdAllowOnlyReading
.SaveAs strPolFile
End With

appWord.Selection.GoTo What:=wdGoToBookmark, Name:="BalloonFiche"
For i = 1 To recCount
appWord.Selection.InsertFile Filename:=strFileName & i, Range:="", ConfirmConversions:=False, Link:=False, attachment:=False
Next i


I'm sure it's something simple that I'm doing wrong or have left out but any help you can give would be much appreciated.

Thanks
 

Jabba

New member
Local time
Today, 10:32
Joined
Jan 10, 2014
Messages
8
Don't worry, spotted the stupid mistake - leaving the ".Protect wdAllowOnlyReading" copied from another bit of code.
 

Users who are viewing this thread

Top Bottom