By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,165 Members | 1,928 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,165 IT Pros & Developers. It's quick & easy.

Output access to Word merge

P: n/a
Hello

On my Access database form I have a command button which opens a Word mail
merge document in which I have created a number of fields (Title, FirstName,
LastName, Address1 etc.)

I would like to be able to populate the Word document with the contents of
the currently selected Access record so that when I press the button in
Access, Word opens with the appropriate fields populated and ready to print.
It is a "one off" event which I will use, typically, to print a single
letter or invoice which is more easily edited in Word.

My code at the moment is:
-------
Private Sub StartWord_Click()
On Error GoTo Err_StartWord_Click

Dim LWordDoc As String
Dim oApp As Object

'Path to the word document
LWordDoc = "m:\database\poolshop\Invoice new.doc"

If Dir(LWordDoc) = "" Then
MsgBox "Document not found."

Else
'Create an instance of MS Word

Set oApp = CreateObject("Word.Application")
oApp.Visible = True

'Open the Document
oApp.Documents.Open FileName:=LWordDoc
End If

Exit_StartWord_Click:
Exit Sub

Err_StartWord_Click:
MsgBox Err.Description
Resume Exit_StartWord_Click

End Sub
-------

But at the moment it does not automatically populate Word with anything, or
at least, by default, I see the contents of the first record. What I want is
to see, in my Word fields, the data from the current record. I can use the
normal mail merge search facility in Word to do a query and find the right
record but that is clumsy and I know you can do better.

I do not want to sound arrogant (be kind to me) but I do quite like the bit
of code that I have used (copied from elsewhere) and rather hope that all I
need to do is add a "current.record" type of command to it somewhere to get
the result that I need.

Thank you for your help, keep it simple.

Les
Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
On Sat, 12 Feb 2005 20:55:49 GMT, Dadio <da***@fred.net> wrote:
On my Access database form I have a command button which opens a Word
mail merge document in which I have created a number of fields (Title,
FirstName, LastName, Address1 etc.)

I would like to be able to populate the Word document with the contents
of the currently selected Access record so that when I press the button
in
Access, Word opens with the appropriate fields populated and ready to
print. It is a "one off" event which I will use, typically, to print a
single
letter or invoice which is more easily edited in Word.


I am working on this subject myself. Below is some code I have modified
based on the method espoused by Albert Kallal. It works, but is not
finished. You will need a document set up with mail merge fields that
correspond to a query:

Dim mWord As Object
Set mWord = CreateObject("Word.Application")

DoCmd.TransferText acExportDelim, , "LogSheetQuery", "C:\log_sheet.txt",
True

mWord.Documents.Open "c:\LogSheet.doc"
mWord.Application.Visible = True
mWord.ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
mWord.ActiveDocument.MailMerge.OpenDataSource Name:="C:\log_sheet.txt", _
AddToRecentFiles:=False, Format:=0, Connection:="", SQLStatement:="",
SQLStatement1:=""
mWord.ActiveDocument.MailMerge.Execute
Set mWord = Nothing

Darryl Kerkeslager
Nov 13 '05 #2

P: n/a
For a single output like that, I've had good success using a Word Template
with Bookmarks instead of a Mail Merge document.
Open a document based on the template (wrd.documents.add), and loop through
the bookmarks collection.
If you name the bookmarks the same as the controls on your Access form, it's
especially easy to write the code.

HTH
- Turtle

"Dadio" <da***@fred.net> wrote in message
news:p3*****************@newsfe2-win.ntli.net...
Hello

On my Access database form I have a command button which opens a Word mail
merge document in which I have created a number of fields (Title, FirstName, LastName, Address1 etc.)

I would like to be able to populate the Word document with the contents of
the currently selected Access record so that when I press the button in
Access, Word opens with the appropriate fields populated and ready to print. It is a "one off" event which I will use, typically, to print a single
letter or invoice which is more easily edited in Word.

My code at the moment is:
-------
Private Sub StartWord_Click()
On Error GoTo Err_StartWord_Click

Dim LWordDoc As String
Dim oApp As Object

'Path to the word document
LWordDoc = "m:\database\poolshop\Invoice new.doc"

If Dir(LWordDoc) = "" Then
MsgBox "Document not found."

Else
'Create an instance of MS Word

Set oApp = CreateObject("Word.Application")
oApp.Visible = True

'Open the Document
oApp.Documents.Open FileName:=LWordDoc
End If

Exit_StartWord_Click:
Exit Sub

Err_StartWord_Click:
MsgBox Err.Description
Resume Exit_StartWord_Click

End Sub
-------

But at the moment it does not automatically populate Word with anything, or at least, by default, I see the contents of the first record. What I want is to see, in my Word fields, the data from the current record. I can use the
normal mail merge search facility in Word to do a query and find the right
record but that is clumsy and I know you can do better.

I do not want to sound arrogant (be kind to me) but I do quite like the bit of code that I have used (copied from elsewhere) and rather hope that all I need to do is add a "current.record" type of command to it somewhere to get the result that I need.

Thank you for your help, keep it simple.

Les

Nov 13 '05 #3

P: n/a
You might find my example code does what you want, and then you don't have
to do anything!!

Give my sample word merge a try here:

http://www.members.shaw.ca/AlbertKal.../msaccess.html

The above example actually allows you to create, and merge word temples all
from inside of ms-access. What is also very nice is in fact their is NO HARD
coding of field names, and thus my solution works for all forms in your
application (I always hated book marks, as then you have actually write
code, and also hard code field names in your code!. Customers/clients tend
to find this dishonest, as then to make a new word merge document, you
actually have to bring in the developer!....and that don't look very
good!!).

Anther bonus of my mail merge stuff is that it defaults to merging only the
CURRENT record you are viewing.
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.members.shaw.ca/AlbertKallal
Nov 13 '05 #4

P: n/a
To Darryl, McDermott and Albert
Many many thanks for taking the trouble to reply so quickly.
I have spent about 5 hours searching the web for a solution to what I
thought must be a common everyday problem and just could not find a solution
that even came close to what was needed.
Now I find myself worshipping at the feet of Albert who has just about the
slickest fix I could ever have imagined. What is more amazing is that it is
simple and even I could follow the clear and well written, and illustrated
text to have the data up and running in just a few minutes.

So, Albert, you have just reached super-hero status and I can only say thank
you. Don't you ever close your web site. It is too important!

Obviously Darryl is familiar with your work and to Macdermott all I can say
is give the "Albert Method" a try. I did not like using bookmarks, it seems
like a method used by quite a few people but a bit clumsy.

Now I can sleep soundly.

Thank you Thank you.......

Les

"Albert D. Kallal" <ka****@msn.com> wrote in message
news:RIwPd.371144$Xk.295322@pd7tw3no...
You might find my example code does what you want, and then you don't have
to do anything!!

Give my sample word merge a try here:

http://www.members.shaw.ca/AlbertKal.../msaccess.html

The above example actually allows you to create, and merge word temples all from inside of ms-access. What is also very nice is in fact their is NO HARD coding of field names, and thus my solution works for all forms in your
application (I always hated book marks, as then you have actually write
code, and also hard code field names in your code!. Customers/clients tend to find this dishonest, as then to make a new word merge document, you
actually have to bring in the developer!....and that don't look very
good!!).

Anther bonus of my mail merge stuff is that it defaults to merging only the CURRENT record you are viewing.
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.members.shaw.ca/AlbertKallal

Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.