Reports to Word- automation 1

[vicky1362002]

Hello,
I have an MS Access database as the front end and SQL Server is its backend with some data in the tables.

There are some word documents given to me which need to be created as reports.

I should create some buttons on the Access, which when clicked should open the reports.

I know how to do that BUT the requirement is such that they want the reports in such a way that when they click a button to open a report, the report should open in a word format so that if they want to make any changes, they can, and then print them out.

Is there a way to do this????

In-short my question is: can we open the MS Access reports in word format on single click????

Hope you all understood.

Please help me...

Thanks and Regards
Vik

 

[MajP]

Look at "docmd.outputto" in the helpfile.
DoCmd.OutputTo acReport, "yourreportname", acFormatRTF, , True

This may or not work depending on how exact the format needs to be. If you have a lot of outline levels and complex formatting then it may not come over exactly. The next solution becomes more complicated.

 

[vicky1362002]

Thank you MajP for the reply.

Yes, i did try that, and as you know it does not give the exact format as in the report design.

I should use something like the mailmerge, but i DO NOT have much idea on using the mailmerge.

I was able to do some research and able to find something which helped me to open the word documents from some directory but am struck on pulling out the data from the tables (in Access) and inserting into the word document automatically, which is the mailmerge.

can anyone please help me in how to setup mailmerge between word and access, so that when i open a report (linking to the word doc), it opens a word document with some fields from the tables in access.

please help me.

Thanks
Vik

 

[PHV]

You may look here:
faq705-3827
faq705-3237

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

 

[vicky1362002]

Thank you PHV for the reply, but i am pretty new to MS Access and cannot follow the code.

I just wanted to make a word doc pull the values from a table in Access Database.

If anyone can list the steps for doing that, i would be very thankful, i have some word docs which are already formatted in such a way that they pull some values from a table from a database, but where can i see all those settings of the source database, the fields, etc, or how could i do all those settings???


please help me.

Thanks
Vik

 

[MajP]

What have you tried? Have you read the Mail merge help in Word? It seems pretty clear. Which parts are you having trouble with? Have you read the mail merge help in Access? Also pretty clear. It is pretty step by step. Here are some more step by step:

http://www.databasedev.co.uk/access_mail_merge.html

http://lmgtfy.com/?q=mail+merge+step+by+step

Launch Word and open a new blank document.

Go to the Tools Menu and select "MailMerge".

Under the heading, "Main Document", click on the "Create" button.

In this dropdown list, you will see four choices: Form Letters, Mailing Labels, Envelopes, and Catalogs......for this tutorial, select "Form Letters".

You will see a box which asks you if you want to use the active window or a new main document......since you have a blank document on your screen, you will select "active window".

Now, your Mail Merge Helper window has two more buttons available to you. Under the heading, "Data Source", click on the button that says "Get Data".

In this dropdown list, you will see four choices again: Create Data Source, Open Data Source, Use Address Book, and Header Options. (If you already have a database of addresses that you created previously in Word, Excel, or Access, you would choose "Open Data Source" and browse to find that file". If you want to use your Outlook Contacts for your addresses, you would choose "Use Address Book". Ignore the "Header Options" choice for now, since that is an advanced feature which is not covered in this tutorial".). For this exercise, you will choose "Create Data Source" and will make a new database of Addresses in Word. (Note: my personal preference is to create my database of addresses in Excel and use this for my Word Mail Merges, but this is a matter of preference only.) So, click on "Create Data Source".

This box will show you a list of fields that will be used by default. You can add or remove fields. To remove fields, simply highlight them in the list on the right and click on the "Remove field name" button on the left. For this exercise, highlight the following fields one at a time and remove them: job title, company, address 2, country, postal code, homephone, and workphone. When you are done, your list should only include title, firstname, lastname, address1, city, and state. Now, in the box on the left, below where it says "Field Name", type Zipcode. Then click on the button below it that says "Add field name". Now Zipcode has been added to the list and you know how to both add and remove fields in your list.

Click on OK and you will see the Save As dialog box. Name your address database file with a name you will remember and recognize and save it in a location where you can find it when you want to use it again in the future for another mail merge (see step 7 above where "Open Data Source" is discussed.). Click on "Save".

Now, you see a new box asking if you want to edit your data source or your main document. (If you were using a data source that was already complete, you would choose "Edit main document".) In this case, since we haven't yet put any information into our data source file, you will click on the button that says "Edit Data Source".

Now, you see the Data Form box. Fill in the information for the first person you want to add to your database. You may leave any of these fields empty if you do not have that field's info for any particular person. Once you have all the info filled in for the first person, click on the "Add New" button. (Note: do NOT click on the OK button as this will tell Word that you are finished creating your database and it will close this box. If you accidentally click on OK, prematurely, just go to tools>mail merge again and click on the button that says "Edit Data Source" and it will return you to this box.) After you click on the "Add New" button, your fields will be empty and you can add the second person. Continue adding people, clicking "Add New" after each one, until you have all of the people in your database. When you are done, click on "OK".

Now, you are returned to your blank document and you will notice a new toolbar at the top of your screen, which includes a button that says "Insert Merge Field". This is your Mail Merge toolbar and if you hover your mouse pointer over the various buttons you will see little popup windows which tell you what these buttons do. Notice there is a button for the Mail Merge Helper that will take you to the same place as going to the Tools menu and clicking on Mail Merge. Also there is a button for Edit data source, which will take you back into the Data Form box where you can add more addresses or edit any existing addresses.

Put your flashing cursor at the beginning of your blank document and click on the button that says "Insert Merge Field". From this dropdown list, click on Title. Notice that this inserts the title field into your document (It looks like <<Title>>) and your mouse pointer is blinking just to the right of this field. Hit your spacebar once to put a space between this field and your next field.

Now, go back to the "Insert Merge Field" button and select the FirstName field, hit your space bar again, then go back and insert the LastName field. You should now have a line in your document which looks like this: <<Title>> <<FirstName>> <<LastName>> . DON'T FORGET THE SPACES!

Now, hit your Enter key on your keyboard to move to the next line and insert your Address1 field. Hit Enter again to drop to the next line and insert your City field. Following your city field, type a comma (,), then hit the spacebar, then insert your State field, hit the spacebar, and insert your Zipcode field.

Now, hit your Enter key a couple times to drop down a few lines and type the word, Dear. Now go back to your Insert Merge Field button and insert the Title field, hit the spacebar, insert the LastName field and type a comma.

Now, hit Enter a couple times to drop down a few lines and type the body of your letter. (If you want any personalized info within the body of your letter, you can insert these merge fields within the body of your letter anywhere you want. For example you can try things like this.......I know this information will be useful to you, <<FirstName>>, so each letter will print with the individual's own name. Or, you could try ..... We have found this information to be especially relevant to people who live in <<City>> and each letter you print will include the indiviual's own city.).

Once you finish the body of the mail, type your sign off line and signature and your form letter is complete.

Now, go back up to your Mail Merge toolbar and click on the button that says "Merge".

When the Merge box comes up, leave the settings as they are and click on the button that says "Merge".

Now you will see a new document with the information filled in for the first person in your database. Use your scrollbar and scroll down and you will see an individual letter for each person in your database with individual information filled in wherever you put fields.

Your mail merge is now complete and all you need to do is print it.

When you are finished printing it, you can close all of these files and save the ones you want. You probably will not want to save the individual letters with the personalized info in them, but you SHOULD save the letter with the merge fields in it, in case you want to send this same letter to another group of people in the future, and you MUST save your database of addresses if you ever want to use this database with a different form letter or envelopes or mailing labels (see step 7 above where "Open Data Source" is discussed.).

 

[vicky1362002]

MajP, you are awesome.....
Thank you so very much, this helped me a lot:

http://www.databasedev.co.uk/access_mail_merge.html

Now, i am able to create the docs merged with access data.

But i do have some more issues now.

when i open the word document directly from the folder, the "mail merge" toolbar is active, but when i open the word document through the VB code in Access, it opens the word doc but the "mail merge" toolbar is not active, it's disabled.

Let me explain what i am looking for.

From the access, when i click a button (created on a form) it opens the word doc (the doc which i just merged with the access data), but it has the "mail merge" toolbar disabled, and it shows the merged data of the first record from the access data (table), what to do if i want to go to the next data, if the "mail merge" toolbar was active, i would click on the "Next Record" on the "mail merge" toolbar but it is not active.

Also if there are some thousand records, is there any code to be written so that it goes to the record we wanted, or prompts for the ID or something and pulls that data from the Access table and shows the word document. I can actually enter the serial number in the "mail merge" toolbar and hit ENTER to go to any record but just looking for an alternative. Anyways, this is secondary, firstly i want the "mail merge" toolbar to be active when i open the word document by clicking a button on the access form (FYI: i know how to create forms and its buttons and link it to the word documents).

please help me,


thanks a lot again,
Vik

 

[vicky1362002]

Guys, you know what, i got my issues resolved as of now.

I just created some buttons and hyperlinked them to the documents i created (using mailmerge), they are working pretty good.

Do any one think that it might cause some problems in the near future somewhere???

Please let me know what you think or what you can advice me.

Thanks again,

Regards,
Vik

 

[vicky1362002]

Please let me know if you have any other solutions.

Thanks,
Vik

 

[vicky1362002]

Guys,
Hyperlink might cause some issues in the near future sue to security concerns.

I got a new solution to this:

Create the forms as you wanted and the buttons ofcourse.
For the buttons, use this code:
------------------------------------------------------
Private Sub Command51_Click()
Dim wks As Word.Document
Set wks = GetObject(Application.CurrentProject.Path & "\documents\formname.doc")
wks.Parent.Visible = True
Set wks = Nothing
End Sub
------------------------------------------------------
Note: The "documents" directory should be in the same directory as of the Access Database.

Before doing this, you might have to merge the documents using the desired Access database.

The following link is awesome, provided by MajP (a user here, posted above)

http://www.databasedev.co.uk/access_mail_merge.html

Thanks Guys,
Merry Christmas!!!!!

-Vik

 

[vicky1362002]

Hello Guys,

I am using the code which i mentioned above for opening a word document merged with the ms access data table, but the problem which i am facing is: if i click a button (created and when clicked opens the word using above post's code), but it opens the word with the data from the table of the first record alphabetically, it should be in such a way that it open the word document with the data which i want, say for a particular ID from the table.

Please help me guys....if any questions or did not understand my question please reply, i will try to explain further.

Thanks all,
Vik

 

[MajP]

The mail merge should pull the data from a query. I would have a form with a combo box with the ID in it. I would select an ID that I want to print. Lets say the form is called, "frmSelectID" and the combo is "cmboID" (unbound). Then I will have a query, "qryMailMerge" with a criteria like
"...WHERE ID = [forms]![frmSelectID]![ComboID]. Then on my frmSelectID I will have a hyperlink or someother means to launch my document. It now will mail merge whatever is in my combo.

 

[vicky1362002]

Thank you MajP for the reply.

I did try this, but i am struck at "Then on my frmSelectID I will have a hyperlink or someother means to launch my document", the form name in my case is "printmenu" amd the combo is "Combo9". I am having the query "test" with this SQL: select * from tableA WHERE (((TABLEA.ID)=[Forms]![printmenu]![Combo9]));

Now, how to i make the hyperlink or how do i open the merged word document???

-Vik

 

[MajP]

Launch your documents the same way you launched them before. But the documents need to pull the data from the new query, "qryMailMerge". If the form is open and a value selected in the combo, when you launch the document it will pull the values from the "qryMailMerge". "QryMailMerge" will only have the record selected in the combo.

 

[vicky1362002]

MajP,

This is what i am doing.

The form name in my case is "printmenu" amd the combo is "Combo9". I am having the query "test-query" with this SQL: select * from tableA WHERE (((TABLEA.ID)=[Forms]![printmenu]![Combo9]));

I merged this query with a word doc named test.doc

Now, i created a module named "testmodule" with this code:
------------------------------------------------------------
Option Compare Database
Option Explicit

Declare Function FindWindow Lib "user32" Alias _
"FindWindowA" (ByVal lpClassName As String, _
ByVal lpWindowName As Long) As Long

Declare Function SendMessage Lib "user32" Alias "SendMessageA" _
(ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, _
ByVal lParam As Long) As Long
___________________________________________________________

Public Function MergetoWordtest()
' This method creates a new document in MS Word 97 using Automation.
On Error Resume Next

Dim WordObj As Word.Application

DoCmd.Hourglass True

Set WordObj = GetObject(, "Word.Application")
If Err.Number <> 0 Then
Set WordObj = CreateObject("Word.Application")
End If

WordObj.Visible = True

WordObj.Documents.Open Application.CurrentProject.Path & "\new_Forms\test.doc"


DoEvents
WordObj.Activate

' Set the Word Object to nothing to free resources

DoCmd.Hourglass False


Exit Function


End Function

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

Now, on the printmenu form, i created a button, and then used a macro named "test-macro", whose Actions are as follows:
openQuery: QueryName "test-query"
Requery
RunCode: runCode MergeToWordtestmodule()
Close: Query "test-query"
StopMacro


As you know, this macro runs the MergeToWordtestmodule function or i should say the module, and then opens up the word document, but it still opens up with the 1st entry alphabetically in the Access Table.

Please advice me what to do...

Thank you,
Vik

 

[vicky1362002]

MajP,

I am still trying to do what you suggested but it isn't working.

I created a query with the SQL: select * from tableA WHERE (((TABLEA.ID)=[Forms]![printmenu]![Combo9]));

But the Word Doc doesn't care, it still opens the alphabetically first entry from the Access Table.

I dont know what to do.....please help me buddy.

 

[MajP]

I do not use a lot of mail merge so I am not that knowledgeable. I write word code to read from a database. That is probably beyond the scope of this discussion. However, it appears that a mail merge can only read from a table, not a query. Bizarre. But from any table you can pick the sort order, select individual records. This is based on A2003, not sure what features you have in A2007. So create a dummy table with the fields you need. Then write an update query that update the record to the fields from the ID selected in your form. Then mail merge to the table.

 

[vicky1362002]

Ok MajP, Thank you for all your help. I really appreciate it.

You have a wonderful day.

Merry Christmas and a Happy New Year.

-Vik