Thanks for the help. I have really good VBA code that allows me to select a document or many, mailmerge and preview or print documents. What I am trying to do differently is pull in the data from a multi-row(record) query and fill a table in a word document. I probably haven't yet grasped the essentials. I could easily add the mergefield names, but I'm not sure that would get me 25 rows of data or more than one row of data. I have looked at the following references but don't see how they work
"http://windowssecrets.com/forums/sho...ngs-(2000-2010)
or
http://www.gmayor.com/Zips/Catalogue%20Mailmerge.zip
The tutorial covers everything from list creation to the insertion & calculation of values in multi-record tables in letters. Do read the tutorial before trying to use the mailmerge document included with it.
For a recent, worked example, see the attachment to post #13 at: http://www.msofficeforums.com/mail-m...nt-dollar.html"
This is the good I use to do normal mailmerge, which is based on an access query with one row(record) of data, I want to be able to pull in multiple rows(records), It would look like a table.
'Itterate over FormName looking for Checkx boxes where x=1..n.
'
'For each check box found, print the document in the checkbox's
'StatusText property using OLE and Word.
'
Function PreviewDocs(FormName$)
On Error GoTo PreviewDocs_ErrHandler
'Variables for form controls
Dim Frm As Form
Dim iCurrentControl As Integer
Dim bControlExists As Boolean
Dim bControlSelected As Boolean
Dim szCheckBox As String
Dim szConnection As String
'Variables for Word
Dim o_App As Object
Dim szWordPath As String
Dim szDocumentFilename As String
Dim szDocumentLabel As String
Dim szFocus As String * 100
Dim szTempFileName As String
Dim o_Doc As Object
'Praveenb: For using OLE in mail merge
Dim szTempTextFilePath As String
Dim szQueryName As String
On Error Resume Next
'Praveenb - Adding to error handler
'On Error GoTo PreviewDocs_ErrHandler
DoCmd.Hourglass True
'Link to word, starting if required, and get Word's path
Application.Echo True, "Opening Word link..."
Set o_App = CreateObject("Word.Application")
szWordPath = GetDBPath() & "Docs\"
'An error occured. Word could not be opened
If szWordPath = "" Then
GoTo PreviewDocs_ErrHandler:
Else 'Word started. Now itterate over FormName
bControlExists = True
iCurrentControl = 1
bControlSelected = False
'Stop the loop when there are no more controls OR
'a shift key is held down. 16=vk_Shift.
Do While bControlExists 'And (GetASyncKeyState(16) >= 0)
If iCurrentControl < 233 Then
szCheckBox = "Check" & CStr(iCurrentControl)
Else
Exit Do
End If
'Try to get the check state for CurrentControl
bControlSelected = Forms(FormName$)(szCheckBox).Visible _
And Forms(FormName$)(szCheckBox)
If bControlSelected Then 'Control was selected. Print it
'Lookup the filename of the document
'Get the LineIdent from the form
szTempFileName = Forms(FormName$)("Name" & CStr(iCurrentControl)).caption
'Do 'Strip everthing except the last line from the string
'Debug.Print TempFileName
szTempFileName = Right(szTempFileName, _
Len(szTempFileName) - InStr(szTempFileName, Chr$(10)))
'Loop Until IsNull(TempFileName) Or (InStr(TempFileName,
'Chr$(10)) = 0)
If Len(szTempFileName) <> 0 Then
szDocumentFilename = szTempFileName
szDocumentLabel = Left$(Forms(FormName$)("Name" & CStr(iCurrentControl)).caption, _
InStr(Forms(FormName$)("Name" & CStr(iCurrentControl)).caption, Chr$(13)) - 1)
szConnection = DLookup("[Queryname]", "TDocuments", "[Filename] Like'" & szTempFileName & "'")
'Debug.Print DocumentFileName$, DocumentLabel$
Application.Echo True, "Opening document " & szDocumentLabel & "..."
If FileExists(szTempTextFilePath) Then Kill szTempTextFilePath
With o_App
Set o_Doc = .Documents.Add(szWordPath & szDocumentFilename, False)
If CInt(o_App.Version) < 10 Then
'Word 97 and 2000
o_Doc.Mailmerge.OpenDataSource Name:=GetDBFilenamep(), _
ReadOnly:=False, Connection:=szConnection ', _
OpenExclusive:=False, SubType:=wdMergeSubTypeWord2000
Else
'Word xp and higher
' o_Doc.Mailmerge.OpenDataSource Name:=GetDBFilenamep(), _
' ReadOnly:=False, Connection:=szConnection, _
' SubType:=8
'Praveennb - getting the query name without the string "QUERY" as the TDocuments table currently has it.
szQueryName = Replace(szConnection, "QUERY ", "")
'Praveenb - Currently storing the temporary text file at the same location as the database.
szTempTextFilePath = GetDBPath() & "/" & szQueryName & ".txt"
'Praveenb Export the query result to a text file
DoCmd.TransferText acExportDelim, , szQueryName, szTempTextFilePath, True
'Praveenb - Perform mailmerge with the text file as the source
o_Doc.Mailmerge.OpenDataSource _
Name:=szTempTextFilePath, _
ReadOnly:=True
End If
o_Doc.Mailmerge.Destination = 0
'o_Doc.MailMerge.Destination = wdSendToNewDocument
.Visible = True
.Activate
.WindowState = 1
'.WindowState = wdWindowStateMaximize
'.Activate (to bring up pop-up, move this to after Visible for Win Vista, Win7 11.12.10)
o_Doc.Mailmerge.Execute Pause:=False
'o_Doc.CommandBars("Mail Merge").Visible = False
'We must activate Word to allow the user to interact
'with any ASK prompts or errors.
'DoCmd.RunCommand acCmdAppMinimize
End With
o_Doc.Close 0
'Praveenb - Delete the temporary text file - if it exists
If FileExists(szTempTextFilePath) Then Kill szTempTextFilePath
'o_Doc.Close wdDoNotSaveChanges
'o_App.Visible = True
Application.Echo True, "Document has been merged " & szDocumentLabel & "..."
Line1: ' Else
Else 'An error occured. We must be past the end of the Checkxx boxes. End print run
bControlExists = False
End If
End If
iCurrentControl = iCurrentControl + 1
Loop
End If
'o_App.Visible = True
Application.Echo True, "Closing Word link..."
'Set o_App = Nothing
'o_App.Quit
'o_App.Visible = True
' Exit Function
PreviewDocs_Exit:
On Error Resume Next
'o_App.Quit
Set o_App = Nothing
'o_App.Quit
'Praveenb - Delete the Temporary text file - if it exists
If FileExists(szTempTextFilePath) Then Kill szTempTextFilePath
Exit Function
PreviewDocs_ErrHandler:
Select Case Err.Number
'Case 2465 happens when iterating over docs and the
'iteration passes the 130 documents on the
'FGenerateDocuments Form
Case 4605
GoTo Line1:
Case 5151
MsgBox "a document could not be found"
GoTo Line1:
Case Else
MsgBox "Please report the following error to support:" & vbCr _
& vbTab & Err.Number & vbCr & Err.Description, _
vbCritical + vbOKOnly, C_MsgTitle
Resume PreviewDocs_Exit
Resume
End Select
End Function