Merge docs with Word??? 1

[mtpgringa]

I am brain-dead over this one...so forgive me if it's something simple...

I have a database which holds--in separate tables all linked by caseID--information regarding hundreds of individual court cases. For each case, the end user must send out several form documents. I made a query to merge with....however, it merges ALL THE RECORDS in the database instead of just the one focused upon. I tried to put a prompt in the query so that the user could select the individual case and then merge, but that did not work (merge did not perform). What parameter do i need to put in my query to select an individual CaseID???? PLEASE HELP, AM UNDER DEADLINE!!!

 

[Telsa]

I had the same problem a couple of years ago and found a neat solution in a book from Wrox.

Here is a sample of what needs to be done...

Generating Word Doc from Access without using Merge

WORD TEMPLATE
Create your Word document with a template format. Save it as a template file (.dot). Use bookmarks to mark the place you want the data to be pulled in. You can have as many bookmarks as you want. If you require the data to be pulled into tables, don't create tables in Word, but let Access VBA create the tables for you.

ACCESS DATABASE
Add the Word Object to your database (Tools- Reference - Microsoft Word Object 9.0 Library).

Set up queries showing the fields you want to transfer to the Word document. You may need to set up more than one query. If you have to do that, then you will need to set up each as a recordset in the code with its own SQL string. The idea of the SQL string is to narrow down the records in the recordset to the exact info you need.

Assuming only two queries were made, code as follows:

In a module, key in a Public variable to be shared in database


Option Compare Database
Option Explicit

' location of the documents and templates -
' Where will Access find the Word Template?
Public Const m_strDIR As String = "d:\database\"
Public Const m_strTEMPLATE As String = "submittalcd.dot"

' set up objects for use and Public variables to be shared in database
Private m_objWord As Word.Application
Private m_objDoc As Word.Document
Public strProdNum As String

In the Forms Button for starting the event…

Create SQL statements based on the values of the active record (i.e., prodnum)

Click event:

Dim db As DAO.Database
Dim recSubmittal As DAO.Recordset
Dim recSubmittal2 As DAO.Recordset
Dim strSQL As String
Dim strSQL2 As String


' Capture the field whose value will narrow your recordset down
strProdNum = Me.PartsID

strSQL = "SELECT * FROM qrySubmittalBase WHERE ProdNum= '" & strProdnum & "';"
Set db = CurrentDb()
Set recSubmittal = db.OpenRecordset(strSQL)

StrSQL2 = "SELECT * FROM qrySubmittalDetail WHERE ProdNum= '" & strProdnum & "';"
Set db = CurrentDb()
Set recSubmittal2 = db.OpenRecordset(strSQL2)



' This CreateSubmittal sub is created in the module
CreateSubmittal recSubmittal, recSubmittal2

Back in the module, create the above sub (remember, this is referenced in the Forms click procedure)
This can be a little confusing here… the recSubmit is capturing the recSubmittal and the recSubmit2 is capturing the recSubmittal2 recordsets.

Public Sub CreateSubmittal(recSubmit As DAO.Recordset, recSubmit2 As DAO.Recordset)

Set m_objWord = New Word.Application
Set m_objDoc = m_objWord.Documents.Add(m_strDIR & m_strTEMPLATE)

m_objWord.Visible = True

InsertTextAtBookmark "basepart", recSubmit("base&quot
InsertTextAtBookmark "title", recSubmit("title-version&quot
InsertTextAtBookmark "bundledparts", recSubmit("bundledparts&quot
InsertTextAtBookmark "ReleaseDate", recSubmit("ReleaseDate&quot
InsertTextAtBookmark "version", recSubmit("Version&quot


' Generate the table data
InsertSummaryTable recSubmit2


Set m_objDoc = Nothing
Set m_objWord = Nothing


End Sub

Private Sub InsertTextAtBookmark(strBkmk As String, varText As Variant)
' This finds the bookmarks in the Word template to place the data.
m_objDoc.Bookmarks(strBkmk).Select
m_objWord.Selection.Text = varText & ""
End Sub

Private Sub InsertSummaryTable(recR As DAO.Recordset)
' This pulls in the data for a table then highlights the data
' and creates a table in the Word document at a bookmark location
' for each field you want in the column of the table, have tabs
' surround it. Items in quotes are field names from the query/recordset
' If you need to have a blank column, just place vbTab in twice
On Error GoTo No_Record_Err
Dim strTable As String
Dim objTable As Word.Table

recR.MoveFirst
strTable = ""
While Not recR.EOF
strTable = strTable & vbTab & recR("discontinuedpart&quot & vbTab & vbTab &
recR("5x5No&quot & vbCr
recR.MoveNext
Wend

InsertTextAtBookmark "DiscPart", strTable
Set objTable = m_objWord.Selection.ConvertToTable(Separator:=vbTab)

objTable.Select
objTable.Columns(1).Width = InchesToPoints(1.51)
objTable.Columns(2).Width = InchesToPoints(2.56)
objTable.Columns(3).Width = InchesToPoints(1.44)
objTable.Columns(4).Width = InchesToPoints(2.14)

Set objTable = Nothing
No_Record_Err:
Exit Sub

End Sub


Hope this helps!! Mary

Violence begets Violence
Let's Give Peace a Chance!!