Trying to Create a Report to generate a six Page contract with fields embedded from my tables

[Jamescdawson]

I have a contract document (six pages) which requires specific clauses to be customised with fields from my database.



The contract exists as a Word Document which is manually modified with the required data at the moment. My aim to have a button on one of my forms which generates the contract (report) with the fields embedded at the right points throughout the document. i.e. It picks up Name, address etc etc from the customer file and populates the contact (report) as required.

I'm struggling to do this using a report.

I've tried starting with a blank report and tried various ways of cutting and pasting the text from the Word document into labels and unbound fields - none of which work and I can't seem to master the OLE concept. I can get the Document into the report and I am given the opportunity to edit it, but I can't master the pagination.

Can anyway suggest the best way or the right way to go about achieving this?

Jimbo

[ItsMe]

Boilerplates are the one thing that I would recommend using mailmerge. Actually, I do not believe it is literally mailmerge but, merge fields. IIRC, you will need to create your boilerplate in word, adding merge fields to the doc. Then, you need to create a connection to your Access DB. I may have some examples floating around if you decide to go this way. The only reliable way that I know of is to automate MS Word from Access using VBA.

[Jamescdawson]

ItsMe,

Thanks for that.... I had a sneeky feeling that someone was going say this (i.e. come at it from the other direction), and have me start with Word and dip into the database for the custom fields.... I already had a look at Word Mail merge options, but couldn't see how to pull the data directly out of Access. I've only ever mail merged from an excel file. My train of thought was therefore that it could be done by exporting the fields I needed to excel and then using the excel doc as the data source for the fields, but it all seemed too messy. I'd be interested in seeing your examples.

If there IS a way doing it with a report, I'd be a happy to hear that too.

Jimbo

[ItsMe]

Here is some code that I believe was used to create a table in Word. It is not exactly what I was referring to but it is very similar.
https://www.accessforums.net/modules...tml#post237148

I believe I created a sample doing the table thing in the following link. Don't recall what is inside but it might trigger some ideas. I might have a chance to look at it some more later in the day. So post up with what you discovered/understand.
https://www.accessforums.net/sample-...ble-45539.html

[Jamescdawson]

ItsMe,

Thanks for your input here. I can see you test export working to a table in Word, but I can't translate this into my problem. I had a look at the VB behind the button and I'm afraid it loses me. In addition, I would need to introduce criteria into the query so that only the fields in a particular customer would land onto the Word Docvument.

I'm not entirely sure how I could use this code.

[ItsMe]

There may be a way to use the Wizard within Access, once the merge fields are created in the Word doc. But, I do not know for sure. If I were to tackle the problem, I would use VBA to create a connection to the word doc. I would use VBA to open a Query Object and iterate the records retrieved, writing to the merge fields as the loop executed.

I will take a look at it in a little bit and see what I can come up with that is specific to merge fields and not a whole table.

[ItsMe]

Using the wizard may be the solution for you. While in Access, if you highlight the desired Query Object in your Navigation Pane, you can then go to the External Data tab within the Ribbon. There is a Word Merge button there. Clicking it will prompt you to create a new doc or link to an existing word doc.

If you already have a boilerplate, use the link option and the Wizard will allow you to browse to the word doc that is the Boilerplate. When the file opens, it will open in Word and automatically create the VBA behind the Word Doc to connect to your Access DB.

From there, follow the prompts on the right hand sidebar. What you will want to pay close attention to is the insert Merge Field tool in the Ribbon of Microsoft Word. You will use it during one of the steps to generate your Mail Merge doc. When editing your document, add merge fields for the appropriate field named in your query. This Merge Field button is under the Mailings tab. Place your cursor in the body of your word doc and use the Merge Field tool to insert a merge field where your cursor is located.

That is pretty much it. Use the sidebar in the Word Doc to finish the steps. You will start at around step number three if you launch the Word Doc from the Access Wizard. So don't worry about the envelopes thing and the address header insert options. Go straight to editing your document using the Merge Field thing in the ribbon. Then save your doc. The last step will give you an option to Mail or something like that. Go ahead and create a separate word doc file to distribute. The original boilerplate file will be a separate file that can be saved and reused. The original boilerplate file has code behind and a connection to the Access Data base. It will display real-time data from the query. Use the "Mailings" tab to view the multiple files (one for every record in the query) and export additional word docs that will be used for distribution.

[Jamescdawson]

ItsMe,

Thanks for that. apologies for the delay in responding. I'll give a try and let you know how I do

Jimbo

[Jamescdawson]

ItsMe,

Again apologies for the delay in responding.

I worked through your suggestion and it works fine. However, the user would need to go through all the steps....

1. Run the Query
2. Click on "Export Data to Word"
3. Select the individual from a list within the Wizard
4. Create the document within Word using that filtered Data from the db
5. preview & Print.

Can you suggest any way that I could stitch that lot together using VB and attach it to a button on the Client details form .... so that the user can click a button and have VB go through these steps ?

[ItsMe]

After the Wizard is used to create the Word Doc, there is not a need to revisit the Wizard. The Word Doc will always be connected to your Access DB when the Word Doc is opened.

Before launching the Wizard, select a query object. The wizard will create a connection string from the Word Doc to your query object and store the string in the Word Doc. Build and save your Word Doc (Boilerplate). Now, whenever you open the Word Doc, it will display your boilerplate. Each record within the query will be represented by its own copy of the boiler plate.

Once you have this working, you can build some VBA to adjust the query object and open the word doc.