Access to Word Automation

[sheribear]

I am designing a database that tracts projects and clients. Part of what I am using this database for is to autofill Word templates that have been predesigned. I have this working great for a fax form, however cannot get this to work for a transmittal form that has multiple checkboxes. I would like the information in Access to "check" or "uncheck" the word boxes as needed. Any help in this would be greatly appreciated.

Below is the code that I am using to automate my fax form .....

Private Sub cmdFaxContacts_Click()
Dim db As DAO.Database
Dim recSubmittal As DAO.Recordset
Dim recSubmittal2 As DAO.Recordset
Dim strSQL As String
Dim strSQL2 As String
Dim strProject, strTitle, strFirst, strLast, strSender, strPhone, strFile, strSubject, strFax, strPages, strComments, strUrgent, strReview, strReply As String

'Capture the field whole value will narrow your recordset down

strProject = Me.txtProjectNumber
If IsNull(strProject) Then
MsgBox "Project Number must be entered"
Exit Sub
End If
If strProject = "Project Number" Then
MsgBox "Project Number must be entered"
Exit Sub
End If
strTitle = Me.txtTitle
If IsNull(strTitle) Then
strTitle = " "
End If
strFirst = Me.txtFirst
If IsNull(strFirst) Then
MsgBox "Please enter a first name"
Exit Sub
End If
strLast = Me.txtLast
If IsNull(strLast) Then
MsgBox "Please enter a last name"
Exit Sub
End If
strSender = Me.txtSender
If IsNull(strSender) Then
MsgBox "Please enter a senders name"
Exit Sub
End If
strPhone = Me.txtPhone
If IsNull(strPhone) Then
MsgBox "Please enter a phone number"
Exit Sub
End If
strFile = Me.txtFile
If IsNull(strFile) Then
MsgBox "Please enter a file name"
Exit Sub
End If
strSubject = Me.txtSubject
If IsNull(strSubject) Then
MsgBox "Please enter a subject"
Exit Sub
End If
strFax = Me.txtFax
If IsNull(strFax) Then
MsgBox "Please enter a fax number"
Exit Sub
End If
strPages = Me.txtPages
If IsNull(strPages) Then
MsgBox "Please enter the number of pages"
Exit Sub
End If
strComments = Me.txtComments
If Me.optUrgent = True Then
strUrgent = True
End If
If Me.optUrgent = True Then
strUrgent = "Urgent"
End If
If Me.optReview = True Then
strReview = "For Review"
End If
If Me.optReply = True Then
strReply = "Please Reply"
End If



Set db = CurrentDb
Set recSubmittal = db.OpenRecordset("SELECT Projects.*FROM Projects WHERE Projects.ProjectNumber='" & strProject & "';")
CreateFax2 recSubmittal, (strTitle), (strFirst), (strLast), (strSender), (strPhone), (strFile), (strSubject), (strFax), (strPages), (strComments), (strUrgent), (strReview), (strReply)
DoCmd.Close acForm, "frmContactFax"

End Sub

and in the module .......

Option Compare Database
Option Explicit

'location of the documnets and templates
'Where will Access find the Word Template?

Public Const m_strDIR As String = "S:\JEC Data\templates\"
Public Const m_strTEMPLATE As String = "jecfax2.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

Public Sub CreateFax2(recSubmittal As DAO.Recordset, strTitle As String, strFirst As String, strLast As String, strSender As String, strPhone As String, strFile As String, strSubject As String, strFax As String, strPages As String, strComments As String, strUrgent As String, strReview As String, strReply As String)
Dim strName, strBkmk, strDate As String
Dim varText As Variant
Set m_objWord = New Word.Application
Set m_objDoc = m_objWord.Documents.Add(m_strDIR & m_strTEMPLATE)

m_objWord.Visible = True

strName = strTitle & (" ") & strFirst & (" ") & strLast
strDate = Date$
InsertTextAtBookmark "name", strName
InsertTextAtBookmark "date", strDate
InsertTextAtBookmark "fax", strFax
InsertTextAtBookmark "phone", strPhone
InsertTextAtBookmark "file", strFile
InsertTextAtBookmark "pages", strPages
InsertTextAtBookmark "sender", strSender
InsertTextAtBookmark "reference", strSubject
InsertTextAtBookmark "comments", strComments
InsertTextAtBookmark "project_number", recSubmittal(1)
InsertTextAtBookmark "project_name", recSubmittal(2)
InsertTextAtBookmark "urgent", strUrgent
InsertTextAtBookmark "forreview", strReview
InsertTextAtBookmark "pleasereply", strReply

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


Thanks so much,

Sheri