Merge all the fields from Access Table/Query into MS Word

[vicky1362002]

I have a strange issue.

I have a query which pulls the data with some criteria on the date, and it runs well.

Now, i want to merge the data which i get out of this query into a Word document, i should be able to merge the entire data of the query, not just one row or one record, i need the merging in such a way that the word document gets all the data from the query.

Please help me in this regard.

Thank you,
Vik

 

[dhookom]

You haven't told us what you tried and what the results were. We aren't mind readers and can't see your screen. I expect your issue is more of a MS Word question than an Access question.

Duane
Hook'D on Access
MS Access MVP

 

[vicky1362002]

Thank you dhookom for your reply and question.

I have tried to do the normal mailmerge using the Access Query, but as you know it inserts one record at a time, but i need all the records, all the rows to be merged to the word document at one time.

It works with the database toolbar, using the "insert database" option, but it inserts ONLY when the user inserts, i want the insert to be done automatically which would be possible with the mailmerge i guess.

Please help me and let me know if you still did not understand the question.

Thanks
Vik

 

[PHV]

Did you try a Catalog mailmerge ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vicky1362002]

nope...i DO NOT know how the catalog mailmerge works.

PHV, can you help me with this, please?

 

[vicky1362002]

FYI: I am using MS Office/Word 2003

 

[dhookom]

When beginning your merge in Word, select Directory as the document type. Then use the wizard to walk you through the document creation.

Duane
Hook'D on Access
MS Access MVP

 

[vicky1362002]

It does the mailmerge, but gets me the 1st record from the query and then when i click the 'next record' in the mailmerge toolbar it takes me to the next record, but i need all the records to be displayed at once, like for example, if the query has this data:
row1 Tim Knight Honda
row2 Raul Garcia Toyota

The mailmerge should display everything together...like

Tim Knight Honda
Raul Garcia Toyota

While when i am doing the mailmerge, its showing one record at a time...like

Tim Knight Honda

then if i click the 'next record' in mailmerge toolbar, it takes me to next record...like

Raul Garcia Toyota

But i dont need this, i need all the records at one time on the word document.
I hope you understood my problem.

 

[dhookom]

This is kinda like a report design. You only see one definition of the detail section but when you preview the report, the detail section actually renders for every record in your report's record source.

Finish the merge wizard and merge to a new document.

Duane
Hook'D on Access
MS Access MVP

 

[vicky1362002]

Yes Duane, exactly, its similar to the report design in preview mode BUT even in the preview mode we have to click the > (right arrow) for the next records.

My question is: Is there any way to get all the records from a query/table into an MS Word Document as a list.

Or let me put the question in other words...

Is there a way to put ALL the records/all the data from a query/table to a Word document AUTOMATICALLY, other than the normal export, say for example, if i click a button it should open the Word document with all the data from the query/table.

Is there any way to do this?

Anybody please advice/help me.

Thanks

 

[vicky1362002]

Hello PHV,

How do i do the Catalog mailmerge ???

 

[dhookom]

Vicky,
Did you finish the merge or just create the merge definition. You must complete the steps and merge the document with your data to a new document as per my 13 Jan 10 13:45 post.

I believe PH's catalog merge is the same as what I explained. When you click the "Directory" option, notice the description of this option

Create a single document containing a catalog or printed list of addresses.

Step 6 of 6 is where you Merge to a New Document. I don't think you did this.

Duane
Hook'D on Access
MS Access MVP

 

[vicky1362002]

Hey Duane,

I did te step 6 of 6, and it did merge it to a new word document with all the records BUT i had to do it manually, is there a way to do the "Merge to a New Document" automatically or with the VB Code.

As i mentioned already that the merging should be done automatically when i click a button.

When i click a button, it should merge the data with the MS Word and produce all the records, i understand that doing the "Merge to a New Document" does that, but any way of making it do automatically???

Thanks Duane.

 

[vicky1362002]

Hey Duane,

I did the step 6 of 6, and it did merge it to a new word document with all the records BUT i had to do it manually, is there a way to do the "Merge to a New Document" automatically or with the VB Code.

As i mentioned already that the merging should be done automatically when i click a button.

When i click a button, it should merge the data with the MS Word and produce all the records, i understand that doing the "Merge to a New Document" does that, but any way of making it do automatically???

Thanks Duane.

 

[dhookom]

You can write some code to automate this. As a matter of fact practically anything you can do through the user interface, you can do with code. You might want to google on "Word Automation Merge".

Duane
Hook'D on Access
MS Access MVP

 

[vicky1362002]

I couldn't find any good code or i should say i did not understand the codes which i found on google.

Can someone please help me in coding it for me, please.


Thank you,
Vik

 

[dhookom]

The quickest method might be something like the following which will export the query to a new Word document as a table with headings.

   Dim strFileName as String
   Dim strQuery as String
   Dim strSendTo as String
   strSendTo = acFormatRTF 
   strFileName = "C:\Temp\Export\MyQuery.RTF"
   strQuery="qselMyQueryName"
   DoCmd.OutputTo acOutputQuery, strQuery, strSendTo, strFileName, True

Duane
Hook'D on Access
MS Access MVP

 

[vicky1362002]

Thank you Duane.

It inserts all the data from the access table/query to a new word document.
One last question, is it possible to use the same or similar code to insert the full data from the table/query to a document which already has some text in it....i mean say for example if there is a word document named myword.doc which has some text in it.....and somewhere in between that text, the data from the query should be inserted AUTOMATICALLY when i click a button on access form. I know how to create the form and the button.

This is the code i am using in the module to do the mail merge for one of the requirements, "complaint":

Public Function Complaint()

On Error Resume Next

Dim wks As Word.Document
DoCmd.Hourglass True

Set wks = GetObject(Application.CurrentProject.Path & "\forms\Complaint.doc")

wks.Parent.Visible = True
    wks.MailMerge.DataSource.FindRecord Forms!menu!Combo9, "ID"
        Set wks = Nothing
' Set the Word Object to nothing to free resources

DoCmd.Hourglass False


   Exit Function
End Function

This code opens the word document which i merged with a table, and pulls the data from the table for a particular ID on the "Menu" form.

I was using the similar code for my current requirement, but as you know, it opens the word document but has only one record, and have to click "next record" button on mailmerge toolbar for getting next records on the word document.

My question is that: can i insert all the data from the access table/query to the word document at once, in between the text in the word document.

For example: Lets assume the following is the word document:

----------------------------------------------------------
To
The DMV,

This is the data which we have on our records

< all the data from the table/query should be inserted here>

Thank you,
John Walter
Office of ISS

------------------------------------------------------------


Hope you understood my problem.

Thanks Duane,
Vik

 

[dhookom]

I don't know Word automation code without having to google for examples, test, then post back to you. You might have better luck posting questions in a Word forum or possibly someone else here can help.

Duane
Hook'D on Access
MS Access MVP