Mail Merge 2

[pluto1415]

Thanks to help from here, I have a Mail Merge from Access that works fairly well, but I'd like to refine it even further.

Right now I have a contract in MS Word that needs to get data from Access. It needs to merge signature lines (or signature blocks or even half pages of text) for 3 different categories of signers. Insureds, Owners and Beneficiaries. Unfortunately, there could be different numbers of each category. For example, there could be 1 insured, 2 owners and 4 beneficiaries. For this reason, I'm having issues with 1 big query pulling all the data (you can imagine the results I get).

How can I mail merge one document, but have different sections repeat different amounts of times? If I keep it to 2 owners for example, I can just put an IF statement in the Word doc, ie. IF Owner2 is not null then...and have it repeat the whole signature block. I need it to not be hard coded to only 2 as that number can change depending on the case. Also, if the information must come from multiple queries, how do I merge those all into 1 document?

 

[pluto1415]

OK, I've made some changes on the Access side and I now have 3 queries (1 for insured, 1 for beneficiary, 1 for owner) that are append queries that append the information to one big table. So I basically need to merge into Word if the field is not null.

I still have a question on the MS Word side on how to make a section loop through the table and repeat as long as the value is not null....

 

[pluto1415]

I've now got it passing the value in the table to the bookmark in Word, but I've still got 2 problems.

One is that sometimes the value is null, in which case I just want it to move on to the next record and skip the current record. In the table it's evaluating, there could be any number of rows, some are populated and some aren't. I won't know how many rows are populated in advance.

The other is that I don't want it to just fill in the value in the bookmark, I actually need to repeat an entire section of the document, with the value of the bookmark inserted at one particular spot.

Here's my code as it stands:
Option Compare Database
Option Explicit
Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Private Sub ClosingContracts_Click()
On Error GoTo ErrorHandler
Dim strDocName1 As String
strDocName1 = "S:\document preparation\Compliance 2010\Testing\12v1.dot"
Call OpenMergedDoc1(strDocName1)

Exit Sub

ErrorHandler:
MsgBox "Error #" & Err.Number & " occurred. " & Err.Description, vbOKOnly, "Error"
Exit Sub
End Sub

Private Sub OpenMergedDoc1(strDocName1 As String)
'On Error GoTo WordError

Dim sOwner As String
Dim objWord As New Word.Application
objWord.Visible = True
Dim objDoc As Word.Document
Set objDoc = objWord.Documents.Add(strDocName1)
Dim strTble As String
strTble = "TblTesting"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strTble, dbOpenTable)

With rst
.MoveFirst
Do While Not .EOF
sOwner = rst.Fields("Owner_Name")
With objWord.Selection
.GoTo What:=wdGoToBookmark, Name:="SellerSig"
.TypeText Text:=sOwner
End With
.MoveNext
Loop
End With

Exit Sub

WordError:
MsgBox "Err #" & Err.Number & " occurred." & Err.Description, vbOKOnly, "Word Error"
objWord.Application.Documents.Close wdDoNotSaveChanges
objWord.Quit
End Sub

 

[PHV]

As a starting point you may replace this:
Set rst = dbs.OpenRecordset(strTble, dbOpenTable)
with something like this:
Set rst = dbs.OpenRecordset("SELECT * FROM " & strTble & " WHERE Owner_Name IS NOT NULL")

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

 

[pluto1415]

OK - this works, but I was thinking I would only open the recordset once, then have a loop for each field (or one owner loop that would contain owner name, address, zip - then one insured loop, the one beneficiary loop)

This way I would have to open the recordset 56 times (once for each separate field in the table that has to be merged).

 

[PHV]

This way I would have to open the recordset 56 times
Really ?
What about the MoveFirst method ?

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

 

[pluto1415]

I guess that would work too!

I'm not super familiar with VBA and may have gotten myself in over my head with this project.

I was just discussing it with someone else in the office who uses VBA/Excel (but who didn't know you could use it in Access or Word) and he said something similar.

Anyone have any ideas about repeating a chunk of the document (a formatted table and some text) and just inserting the Owner_Name in a specific spot?

 

[MajP]

I have never used mail merge because most of the times I want to do something beyond the limits of mailmerg such as populate tables and include multiple records. I have made many word templates that connect to a database and instantiates ADO recordsets. Then pull information from the database and put it into different areas to include tables and formatted sections.

Do you want some general examples of code going that route? I find this way the most flexible. Basically you can grab anything from the database and put it anywhere.

 

[pluto1415]

Sure, that would probably help. Thanks!

I have the same document running as a mail merge from Access, I'm trying to change it to automation.
Currently the document has entire sections embedded within an If statement that basically says IF Owner2 is not null then print this stuff else don't. Unfortunately it has to be hard coded for the maximum number of Owners I think I might have - this is quite cumbersome when it could be anywhere from 1-100 owners.

We'd really like it to be all programmatic and automatically determine how many owners I have then print that many sections. (same with insureds & beneficiaries)

 

[MajP]

http://www.4shared.com/file/liQTJxe1/PullDataFromAccess.html

Here is a lot of code. To include:
1)Connect to access from word
2)Open and loop multiple recordsets
3)Format and fill tables from access data
4)Format and fill other sections of the documents with continous records.

 

[pluto1415]

It says the file I've requested isn't valid.

 

[MajP]

I am getting the same problem. Let me try a different file type.

 

[MajP]

http://www.4shared.com/file/259136147/75360f18/PullDataFromAccess_1_.html

try this

 

[pluto1415]

I was able to download a file called PullDataFromAccess(1).dotm but I can't open it.

I have office 2003 and if I try to use Word, it just looks like gibberish....

 

[MajP]

http://www.4shared.com/file/259172440/977b69c7/PullDataFromAccess.html

try this. Hopefully gives you some ideas.

 

[pluto1415]

I got it this time. I'll take a look through it and see what I can do...

Thanks

 

[pluto1415]

Slowly but surely I'm getting closer to what I want. Here's my current problem.

I have a bookmark that is actually about 1/2 a page long and contains a table AND several paragraphs of Text. I need to loop through a recordset and if a field exists >1x (ie. 2 owner_names or 3 or 4, etc..), I need to repeat that entire bookmark with all of the formatting and shading once for each value in the recordset. I cannot hard code 4 bookmarks, because I don't know how many there will be (anywhere from 1-10). I do know in Word I can create a REF to that bookmark and have it repeat, but how can I do it from within a loop in VBA? (also each one needs to be on a new page with the page numbering continuing from the previous)

 

[SkipVought]

{quote]I cannot hard code 4 bookmarks[/quote]
How about the Bookmarks Collection?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[pluto1415]

Not sure what you mean Skip?

 

[pluto1415]

It's actually a bookmark inside a bookmark. Meaning - I have a table that has some spaces for signature lines and once in this table I need to insert the Owner_Name. Then, I need to recreate this table, and the following Notary statement and signature line (and all the other legalese in between) only insert Owner_Name2

I've got it working if all I want it to do is list Owner_Name1, Owner_Name2 where the bookmark exists but I need the whole table and everything to repeat with the Owner_Name inserted in the proper space in the table.