Automating Word from Access

[Stephen Glynn]

I've used the code at described in 'How to send the current record to
Word 2000 with automation'
(http://support.microsoft.com/Default.aspx?kbid=210271}
to do what it says on the can. It works by opening a Word document
containing bookmarks, setting the value of Word bookmarks to those of
fields in the Access form, saving the results as a new document and
closing the old one unsaved, thus retaining it for future use.

I far prefer this to the alternative technique of opening a new document
from a template that uses an Access query as the datasource since it's
a lot faster. However, there's one drawback.

If you merge a Word document, Word takes out any blank lines in the
address and adjusts the spacing accordingly. This doesn't, though,
work with bookmarks, so if I've got fields in my query (and bookmarks
ready to receive their contents) AddressLine1, AddressLine2 and City,
and the particular record doesn't have an entry for AddressLine2, the
resulting Word document has an unsightly gap between the first (and
only, in this case) line of the address and the city.

Is there a way to address this problem at the Access end, possibly by
having something in the code to delete the bookmark AddressLine2 and the
carriage return if MyAccessQuery.AddressLine2 is null? How would I do
this?

Steve

 

[John Nurick]

Hi Stephen,

I do this by using a single bookmark for the address, rather than a
bookmark for each field that makes up the address. Let's call it
FullAddress. Then in the Access code I concatenate the fields into a
single string, using the + concatenation operator to handle the optional
lines, e.g. this:

strFullAddress = Me.Address1 & vbCrLf & (Me.Address2 + vbCrLF) _
& (Me.PostTown & " " & Me.PostCode)

Or you can do it more explicitly using IIF, e.g.

strFullAddress = Me.Address1 & vbCrLf _
IIf(IsNull(Me.Address2), vbNullStr, Me.Address2 & vbCrLf) _
& (Me.PostTown & " " & Me.PostCode)

 

[Stephen Glynn]

Thanks. Works a treat.

Steve

Hi Stephen,

I do this by using a single bookmark for the address, rather than a
bookmark for each field that makes up the address. Let's call it
FullAddress. Then in the Access code I concatenate the fields into a
single string, using the + concatenation operator to handle the optional
lines, e.g. this:

strFullAddress = Me.Address1 & vbCrLf & (Me.Address2 + vbCrLF) _
& (Me.PostTown & " " & Me.PostCode)

Or you can do it more explicitly using IIF, e.g.

strFullAddress = Me.Address1 & vbCrLf _
IIf(IsNull(Me.Address2), vbNullStr, Me.Address2 & vbCrLf) _
& (Me.PostTown & " " & Me.PostCode)