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

Creating Word Documents From An Access Query

P: n/a
I need to find a way to automatically create several Word docs from 1 access
query.
Example:
I have a query which has 40 records in it according to my criterea.
Let's say that it is employees.
Of those 40 employees, they are divided by 3 supervisors.
I need to automatically create a word doc, one for each supervisor, that
lists all of there respective employees.
Is there a way to do this???
Thank you!
Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
There is a FAQ at Tek-Tips which you might find helpful.

http://www.tek-tips.com/faqs.cfm?fid=3237

Linda
"Fred Glickman via AccessMonster.com" <fo***@AccessMonster.com> wrote in
message news:50***********@AccessMonster.com...
I need to find a way to automatically create several Word docs from 1
access
query.
Example:
I have a query which has 40 records in it according to my criterea.
Let's say that it is employees.
Of those 40 employees, they are divided by 3 supervisors.
I need to automatically create a word doc, one for each supervisor, that
lists all of there respective employees.
Is there a way to do this???
Thank you!

Nov 13 '05 #2

P: n/a
Thanks Linda.
I already know how to do mail merge.
I don't need a filled in document for each employee, I need a list of
employees in one document.
Nov 13 '05 #3

P: n/a
Fred,
Depending on your level of ability with VBA, this is relatively easy to
do in code. What I would do is create a document template which has
two bookmarks, one for the supervisor name "supervisor" and another for
the supervisor's employees "employees". Save it as c:\supervisor.dot.
Add a reference to the Microsoft Word Object Library.

Assuming you have 2 tables,
tblSupervisor which has PK SupID and SupName
tblEmployee which has PK EmpID, FK SupID and EmpName

Then you'd do something like this.

Sub CreateSupReport()
Dim objWord as Word.Application
Dim rsSup as Recordset, rsEmp as Recordset
Dim db as database
Dim strSQL as String, strEmp as String

Set db = CurrentDb
Set objWord = CreateObject("Word.Application")
Set rsSup = db.Openrecordset("tblSupervisor")
rsSup.movefirst
While not rsSup.EOF
strSQL = "SELECT * FROM tblEmployee WHERE SupID = " & rsSup!SupID
Set rsEmp = db.Openrecordset(strSQL)
rsEmp.Movefirst
While not rsEmp.EOF
strEmp = strEmp & rsEmp!EmpName & vbCrLf
rsEmp.movenext
Wend
With objWord
.Documents.Add "c:\supervisor.dot"
.ActiveDocument.Bookmarks("Supervisor").Range.Text = rsSup!SupName
.ActiveDocument.Bookmarks("Employee").Range.Text = strEmp
.ActiveDocument.SaveAs "C:\" & rsSup!SupName & ".doc"
.ActiveDocument.Close False
End With
rsEmp.Close
Set rsEmp = Nothing
rsSup.MoveNext
Wend

rsSup.close
Set rsSup = Nothing
objWord.quit
Set objWord = Nothing
db.Close
Set db = Nothing
End Sub

This assumes that every supervisor has associated employees, if not it
will fail because it does not trap the error of movefirst on a
recordset that is already EOF. It will create one document for each
supervisor, with the supervisors name as the document name.

This code is intended to give you a general idea of how you can solve
your problem. It's not intended to be the ultimate whiz-bang solution
and there may even be errors in it because I haven't tested it. It
should get you started though.

Good luck!

Nov 13 '05 #4

P: n/a
Fred: Is there a reason you can't do the "document" in Access. I have
successfully done a letter by entering label boxes for static
information and text boxes for dynamic information. Just put the text
box with the output for the employee info in and do a page break on the
change of supervisor field. When mine printed, it looked just like a
letter. I did not have to mess with mail merge or two applications at
the same time.

Don Glenn

Nov 13 '05 #5

P: n/a
If I do a page break for each supervisor in one document then break up the
doc into 3 separate ones, I might as well cut and paste the table and do it
that way. I need a separate docs for each supervisor.

--
Message posted via http://www.accessmonster.com
Nov 13 '05 #6

P: n/a
Wolf, you're moving in the right direction, but my supervisors and employees
are in the same table.

--
Message posted via http://www.accessmonster.com
Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.