merging data...

[nyrob609]

Hi,

I have several vendor forms that must be completed each asking for the same data but all have their format. I would like to do is in a form I would like for the user to pick the form and put the ID # of customer. Based on that run the query and merge the data to a word, pdf, excel file..etc...

Then I will print the form and fax it email etc...

How will I go about this??? any help will be greatly appreciated.

 

[Trevor G]

How is your VBA knowledge and with Word documents are you using BookMarks and Templates?

You have to set references to use Word/Excel Commands in the VBA screen.

Also can you state which version of Microsoft Office you are using as this helps with guiding you.

 

[nyrob609]

VBA is very limitted

Right now I am using the letter merge function by going into word and picking the file and that opens the query attached to the documents and merges data.

I want to eliminate that step I want the user to do it directly from the database and open transfer the data.

I am have MS 2003.

 

[Trevor G]

OK then we may have to do some of this stage by stage. First how to automate word from Access

You will need to go into the VBA Screen to set a reference to use words functionality, so on the Keyboard use ALT + F11 this will open up the VBA screen.

Next Go to the Tools Menu and References, then I would ask you to look down the list and find

Microsoft Word 11.Object Library and tick the box
Microsoft DAO 3.6 and tick the box

Then click close, then go back to Tools and References to see that they are shown at the top of the list.

Next go to the Insert menu and select Insert Module

Then you would type (or copy and paste) in the following:

Function OpenWord()
Dim wrdApp as Word.Application
Set wrdApp=CreateObject("Word.Application")

with wrdApp
.Visible=True
.Documents.Open "Enter the full path and doucment name to open include .doc"
End with
End Function

Save the module and name it as modOpenWord, then close out of the VBA
screen.

The above code will open word and the document you are using for the mail merge.

Next to get this function to work from a button on a form.

Select the forms section and then create a new form in design view
Then from the toolbox select to create a command button.

Cancel the wizard so you only have the button. Then use the right mouse button to open the properties of the button. Then do the following:

Under the tab (at the top) select all, and name the object as cmdOpenWord, change the caption to Open Mail Merge Document

Next go to the Events tab and select On Click to the right you have a small option with 3 full stops, click it and select Event Procedure

In the event type in:

Call OpenWord

Close the VBA screen again and then save the form, change the view of the form and click the button and it will open word and the document.

Once you have done this, please give further information on what you need for your next step.

Good luck

 

[nyrob609]

Thank you for your help.

At this point I created the query with the fields that I want to merge into the form. So, I need to figure out how merge the data so when the documents the data is already pre-populated into the form.

 

[Trevor G]

Thank you for your help.

At this point I created the query with the fields that I want to merge into the form. So, I need to figure out how merge the data so when the documents the data is already pre-populated into the form.

Have you considered using the Access Reporting side to create the Mail Document and place the fields form the queries in the report?

I apologise if you have already explored this avenue.