I am trying to create a form of mail merge that will take data from a query and add it to a word document with bookmarks. Ive created the procedure and managed to get the data in to the word doc with no trouble at all. The problem lies with the fact that access will only export one record at a time. There are over 1000 records I need to export to word; so going through clicking each one isn’t really an option! I have created a continuous form that displays that data that is used with an onclick event. Is there a way I can export all of the data generated by the query at once?

You have to loop thru the recordset and send each record to MS Word.

Dim wrd As Object
Dim rst As Object
Set wrd = CreateObject("Word.Application")
Set rst = CurrentDb.OpenRecordset("YourQueryNameHere")
Do While Not rst.EOF

' Your code for merging dat to Word here.

rst.MoveNext
Loop
rst.Close
wrd.Quit
Set rst = Nothing
Set wrd = Nothing

(IMG:http://www.utteraccess.com/forum/style_emoticons/default/smile.gif)

thanks, i think this is going the right direction, ive tried putting
Dim wrd As Object
Dim rst As Object
Set wrd = CreateObject("Word.Application")
Set rst = CurrentDb.OpenRecordset("YourQueryNameHere")
Do While Not rst.EOF

above the code that allocates the bookmarks in word and

rst.MoveNext
Loop
rst.Close
wrd.Quit
Set rst = Nothing
Set wrd = Nothing

below, it opens word fine but loops the same record 1000 times!! i must be wrong somewhere!! thanks

Post the missing code, please.

(IMG:http://www.utteraccess.com/forum/style_emoticons/default/smile.gif)

I am going off line soon. Someone may jump in here to help you.

(IMG:http://www.utteraccess.com/forum/style_emoticons/default/smile.gif)

Public Function newchrg()
Dim wrd As Object
Dim rst As Object
Set wrd = CreateObject("Word.Application")
Set rst = CurrentDb.OpenRecordset("New Charges 105-33")
Do While Not rst.EOF

With wrd
' Make the application visible.
.Visible = True
' Open the document.
.Documents.Open ("I:\new charge letter.doc")
' Move to each bookmark and insert text from the form.
.ActiveDocument.Bookmarks("names").Select
.Selection.Text = (CStr([Forms]![New Charges 105-33]![Dear]))
.ActiveDocument.Bookmarks("Address").Select
.Selection.Text = (CStr([Forms]![New Charges 105-33]![RoomNumber]))
.ActiveDocument.Bookmarks("Address2").Select
.Selection.Text = (CStr([Forms]![New Charges 105-33]![Address1]))
.ActiveDocument.Bookmarks("Address3").Select
.Selection.Text = (CStr([Forms]![New Charges 105-33]![Address2]))
.ActiveDocument.Bookmarks("Address4").Select
.Selection.Text = (CStr([Forms]![New Charges 105-33]![Address3]))
.ActiveDocument.Bookmarks("Address5").Select
.Selection.Text = (CStr([Forms]![New Charges 105-33]![Address4]))
.ActiveDocument.Bookmarks("contfrom").Select

End With

rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set wrd = Nothing

End Function

heres the code! thanks for help

I don't understand why you don't do a mail merge with Word using your underlying table as the data source? If you could do that, you wouldn't need to loop through a recordset.

What fiftyfour said (IMG:http://www.utteraccess.com/forum/style_emoticons/default/wink.gif)

how would i do this then? i dont think ive come across havingto do this before. would i still be able to use the parameters within my query though?

thanks

Sure, there's a couple of ways of doing it through code.
1. Have your query create a table containing the output. This can be done in code.


In Microsoft Word, you would go to Tools, Mail Merge, Form Letters and follow the wizard. When done, you will have a letter template that contains the fields from the Access Table that we just created above. You would open this letter through Access code, perform a mail merge to a new document


2. Run a SQL statement in Word's Mail Merge code. The following is something that I copied from a post to this sight. My apologies for not referencing the author, but I did not make a notation of their name.


Either way should work like a charm and is almost a standard technique for Access users.

Just as an aside you can also base the doc on a query and not have to create a temp table
which could be difficult if there are multiple users