Access to Word - Multiple Word Templates?

[alpinegroove]

I am using VBA code to merge data from Access into a Word document.
My code currently opens a Word template and inserts the data into it.
However, is it possible to designate 3 different templates and include an IF clause that determines which template will be used?

I have a field that indicates whether a person is an Instructor, TA, or Reader for a given course. Each position requires a different type of contract, so I would like the VBA code to look at that field and based on what's in the field, choose the appropriate template.

Is that possible?

The code I currently use for a 1 template situation is below.



Thank you.

Private Sub GenerateContract_Click()

   ' Check for empty fields and unsaved record.
   If IsNull(FirstName) Then
     MsgBox "First name cannot be empty"
     Me.FirstName.SetFocus
     Exit Sub
   End If
   If IsNull(LastName) Then
     MsgBox "Last name cannot be empty"
     Me.LastName.SetFocus
     Exit Sub
   End If
   If IsNull(StreetAddress) Then
     MsgBox "Street address cannot be empty"
     Me.StreetAddress.SetFocus
     Exit Sub
   End If
  
   If Me.Dirty Then
     If MsgBox("Record has not been saved. " & Chr(13) & _
         "Do you want to save it?", vbInformation + vbOKCancel) = vbOK Then
       DoCmd.RunCommand acCmdSaveRecord
     Else
       Exit Sub
     End If
   End If
        
   ' Create a Word document from template.
   Dim WordApp As Word.Application
   Dim strTemplateLocation As String
  
   ' Specify location of template
   strTemplateLocation = "C:\Instructor Contract Template.doc"
    
    
   On Error Resume Next
   Set WordApp = GetObject(, "Word.Application")
   If Err.Number <> 0 Then
     Set WordApp = CreateObject("Word.Application")
   End If
   On Error GoTo ErrHandler
  
  
   WordApp.Visible = True
   WordApp.WindowState = wdWindowStateMaximize
   WordApp.Documents.Add Template:=strTemplateLocation, NewTemplate:=False
    
   ' Replace each bookmark with field contents.
   With WordApp.Selection
  
     .Goto what:=wdGoToBookmark, Name:="Name"
     .TypeText Trim([FirstName] & " " & [LastName])
  
     .Goto what:=wdGoToBookmark, Name:="StreetAddress"
     .TypeText [StreetAddress]
    
     .Goto what:=wdGoToBookmark, Name:="City"
     .TypeText [City]
  
     .Goto what:=wdGoToBookmark, Name:="State"
     .TypeText [State]
    
     .Goto what:=wdGoToBookmark, Name:="ZipCode"
     .TypeText [ZipCode]
        
     .Goto what:=wdGoToBookmark, Name:="Name2"
     .TypeText Trim([FirstName] & " " & [LastName])

     ' These fields are currency in Access:'
    
     .Goto what:=wdGoToBookmark, Name:="InstructorComp"
     .TypeText [Instructor]
    
     .Goto what:=wdGoToBookmark, Name:="TAComp"
     .TypeText [TA]
    
     .Goto what:=wdGoToBookmark, Name:="ReaderComp"
     .TypeText [Reader]
          
   End With
    
   DoEvents
   WordApp.Activate
    
   Set WordApp = Nothing
   Exit Sub

ErrHandler:
Set WordApp = Nothing

End Sub

[ssanfu]

Sure, you can do that. I would suggest using the "SELECT CASE" option instead of multiple IF() functions. To me, it is cleaner and easier to read.

Try this:

   ' Specify location of template
   Select Case Me.PersonType
      Case "Instructor"
         strTemplateLocation = "C:\Instructor Contract Template.doc"
      Case "TA"
         strTemplateLocation = "C:\TA Contract Template.doc"
      Case "Reader"
         strTemplateLocation = "C:\Reader Contract Template.doc"
   End Select

I don't like to hard code the location, because it might change... then you have to edit the code. I can think of two other options...

The first is to use the Open file dialog code. This lets you select which word doc you want to use, even if the location or name has changed.

The second way is to store the location & name in a table, then use a combo box or list box to select the word doc. You could have a form to edit the location & name, so when either changes, just change it in the table.

[alpinegroove]

Thank you, this is helpful.
I am a beginner and need a little more info.
Do I replace Me.PersonType with Me.[FieldName], the field where the role is stored?

I do like the SELECT CASE option, but my situation might require a more complex IF statement because a person can have more than one role in the same course. The combination of roles determines the type of contract.

For instance, a person can be both the Instructor and the Reader, or only a Reader, or Instructor and TA. It's a bit odd, but that is the set up here.
So if a person is both Instructor and something else, I need to use the Instructor Template. If the person is only a Reader, I need to use the Reader Template, and if a person is only a TA, I need to use the TA Template.

Sorry for not mentioning this earlier.

Can the SELECT CASE option accommodate that?

Thanks again.

[alpinegroove]

A further complication is that because the different templates are obviously not identical, they contain different bookmarks, so there would be different .GoTo / .TypeText code for each template.

How can this be handled?

Thanks

[ssanfu]

Thank you, this is helpful.
I am a beginner and need a little more info.
Do I replace Me.PersonType with Me.[FieldName], the field where the role is stored?

My bad. I was going to highlight "PersonType" and say to use your control name. Yes, you should the actual control name.

I do like the SELECT CASE option, but my situation might require a more complex IF statement because a person can have more than one role in the same course. The combination of roles determines the type of contract.

For instance, a person can be both the Instructor and the Reader, or only a Reader, or Instructor and TA. It's a bit odd, but that is the set up here.
So if a person is both Instructor and something else, I need to use the Instructor Template. If the person is only a Reader, I need to use the Reader Template, and if a person is only a TA, I need to use the TA Template.

So how do you determine which template to use? Are there multiple controls on the form that could be used to determine if the person is both an Instructor and a Reader or Instructor and TA??

A further complication is that because the different templates are obviously not identical, they contain different bookmarks, so there would be different .GoTo / .TypeText code for each template.

Yes, the "SELECT CASE" option could handle the different bookmarks. How different are the bookmarks in the different templates? Are there more bookmarks in one template that another? I am thinking that all templates would have First name, last name, address, city, state & zip?

[alpinegroove]

My form contains salary information for each role a person plays. If the person does not have a specific role, the field is null.
So if there is a value in the Instructor Salary field, then an Instructor Contract is used, even if the person has other roles in the course.
If the Instructor Salary field is null, then the person can be either TA or Reader for the course, and I would chose a template based on which field has a value in it.
There might be a better way, but that's what I was able to come up with.

All the contracts have First name, last name, address, city, state & zip. The TA contract shares a few more fields with the Instructor contract, but the Reader contract only shares those fields and CourseTitle.

[ssanfu]

My form contains salary information for each role a person plays. If the person does not have a specific role, the field is null.
So if there is a value in the Instructor Salary field, then an Instructor Contract is used, even if the person has other roles in the course.
If the Instructor Salary field is null, then the person can be either TA or Reader for the course, and I would chose a template based on which field has a value in it.
There might be a better way, but that's what I was able to come up with.

All the contracts have First name, last name, address, city, state & zip. The TA contract shares a few more fields with the Instructor contract, but the Reader contract only shares those fields and CourseTitle.

It sounds like you have a salary field for an Instructor, a field for a TA and a field for a Reader. This is not a normalized structure.

So how do you determine if the person is an Instructor and TA? Are there entries in both the Instructor and TA salary fields?

[alpinegroove]

This is the general structure:

tblPeople
-pkPeopleID primary key, autonumber
-txtFName
-txtLName
-txtAddress

tblRoles
-pkRoleID primary key, autonumber
-txtRole

tblCourses
-pkCourseID primary key, autonumber
-lngCourseTitle
-txtCourseName

tblCoursePeopleRoles
-pkCoursePeopleRoleID primary key, autonumber
-fkCourseID foreign key to tblCourses
-fkPeopleID foreign key to tblPeople
-fkRoleID foreign key to tblRoles
-currSalary

For data entry, I use frmCourseInfo, which is linked to tblCourses, and a subform linked to tblCoursePeopleRole. I use the form to scroll through the courses and the subform to associate an instructor with a the course and assign a role. tblCoursePeopleRole also contains the salary information, so I use the same subform to enter the salary information.

As far as I can tell, that part is normalized.

But I had to use a crosstab query to make be able to merge the data:
qryCompensation is the cross tab query that yields a table that lists the salary of each person for each role he or she plays in a given course. If the person teaches only one course, he or she is has only one row and the salary for each role is listed. If the person is associated with more than one course, he or she is listed multiple times. This is necessary since I need to issue a separate contract for each course the person is associated with. The contract then needs to list the compensation for each role the person holds in the course.

I am attaching a copy of the db. It's not the most recent version as I have been tweaking it all day, but I don't have access to that file now.

[alpinegroove]

This is the code I came up with. It's probably much messier than Cases, but it does seem to work.
Should I be concerned about using it?

Option Compare Database

Private Sub GenerateContract_Click()
           
' Create a Word document from template.
   Dim WordApp As Word.Application
   Dim strTemplateLocation As String
           
If (IsNull(Me.[Instructor]) = False) Then
  
   ' Specify location of template
   strTemplateLocation = "H:\Instructor Contract Template.doc"
    
   On Error Resume Next
   Set WordApp = GetObject(, "Word.Application")
   If Err.Number <> 0 Then
     Set WordApp = CreateObject("Word.Application")
   End If
   On Error GoTo ErrHandler
   
   WordApp.Visible = True
   WordApp.WindowState = wdWindowStateMaximize
   WordApp.Documents.Add Template:=strTemplateLocation, NewTemplate:=False
    
   ' Finds Bookmark in the template listed above.
   ' Bookmark name from Word is listed under Name.
   ' Inserts field contents into bookmarked location.
   ' If a field is empty, the code will stop at that field.
   
   With WordApp.Selection
   
     .GoTo what:=wdGoToBookmark, Name:="Name"
     .TypeText Trim(Me.[FirstName] & " " & Me.[LastName])
   
     .GoTo what:=wdGoToBookmark, Name:="StreetAddress"
     .TypeText Me.[StreetAddress]
    
     .GoTo what:=wdGoToBookmark, Name:="City"
     .TypeText Me.[City]
  
     .GoTo what:=wdGoToBookmark, Name:="State"
     .TypeText Me.[State]
    
     .GoTo what:=wdGoToBookmark, Name:="ZipCode"
     .TypeText Me.[ZipCode]
         
     .GoTo what:=wdGoToBookmark, Name:="Name2"
     .TypeText Trim(Me.[FirstName] & " " & Me.[LastName])
     
     .GoTo what:=wdGoToBookmark, Name:="CourseTitle"
     .TypeText Me.[CourseTitle]
     
     .GoTo what:=wdGoToBookmark, Name:="Section"
     .TypeText Me.[SectionNumber]
     
     .GoTo what:=wdGoToBookmark, Name:="CourseDT"
     .TypeText Me.[CourseD/T]
     
     .GoTo what:=wdGoToBookmark, Name:="CourseRoom"
     .TypeText Me.[CourseRoom]
     
     ' Not all courses have a final exam.
     ' Since the code stops if a field is null, this line inserts N/A if the
     ' field is empty and the code continues.
     ' Nz means that if the field has data in it, the data will be inserted.
     ' If the field is empty, N/A will be inserted.
     
     .GoTo what:=wdGoToBookmark, Name:="CourseFinalDTR"
     .TypeText Nz(Me.[CourseFinalD/T/R], "N/A")
     
     .GoTo what:=wdGoToBookmark, Name:="CourseFinalDate"
     .TypeText Nz(Me.[CourseFinalDate], "N/A")
                   
     .GoTo what:=wdGoToBookmark, Name:="CourseDisc1DT"
     .TypeText Nz(Me.[CourseDisc1D/T], "N/A")
          
     .GoTo what:=wdGoToBookmark, Name:="CourseDisc1Rm"
     .TypeText Nz(Me.[CourseDisc1Rm], "N/A")
          
     .GoTo what:=wdGoToBookmark, Name:="CourseDisc2DT"
     .TypeText Nz(Me.[CourseDisc2D/T], "N/A")
     
     .GoTo what:=wdGoToBookmark, Name:="CourseDisc2Rm"
     .TypeText Nz(Me.[CourseDisc2Rm], "N/A")
     
     .GoTo what:=wdGoToBookmark, Name:="CourseDisc3DT"
     .TypeText Nz(Me.[CourseDisc3D/T], "N/A")
          
     .GoTo what:=wdGoToBookmark, Name:="CourseDisc3Rm"
     .TypeText Nz(Me.[CourseDisc3Rm], "N/A")
          
     .GoTo what:=wdGoToBookmark, Name:="CourseDisc4DT"
     .TypeText Nz(Me.[CourseDisc4D/T], "N/A")
     
     .GoTo what:=wdGoToBookmark, Name:="CourseDisc4Rm"
     .TypeText Nz(Me.[CourseDisc4Rm], "N/A")
        
     ' This data needs to remain with its currency formatting.
     ' Nz cannot handle the formatting code.
     ' Instead, this IIf statement inserts N/A if the field is null
     ' and field contents with currency formatting if it is not null.
        
     .GoTo what:=wdGoToBookmark, Name:="InstructorComp"
     .TypeText IIf(IsNull(Me.[Instructor]), "N/A", FormatCurrency(Me.[Instructor]))
         
     .GoTo what:=wdGoToBookmark, Name:="TAComp"
     .TypeText IIf(IsNull(Me.[TA]), "N/A", FormatCurrency(Me.[TA]))
        
     .GoTo what:=wdGoToBookmark, Name:="ReaderComp"
     .TypeText IIf(IsNull(Me.[Reader]), "N/A", FormatCurrency(Me.[Reader]))
                
   End With
    
   DoEvents
   WordApp.Activate
    
   Set WordApp = Nothing
   Exit Sub

End If

If IsNull(Me.[Instructor]) And (IsNull(Me.[TA]) = False) And IsNull(Me.[Reader]) Then
   
   ' Specify location of template
   strTemplateLocation = "H:\TA Contract Template.doc"
    
   On Error Resume Next
   Set WordApp = GetObject(, "Word.Application")
   If Err.Number <> 0 Then
     Set WordApp = CreateObject("Word.Application")
   End If
   On Error GoTo ErrHandler
   
   WordApp.Visible = True
   WordApp.WindowState = wdWindowStateMaximize
   WordApp.Documents.Add Template:=strTemplateLocation, NewTemplate:=False
    
   ' Finds Bookmark in the template listed above.
   ' Bookmark name from Word is listed under Name.
   ' Inserts field contents into bookmarked location.
   ' If a field is empty, the code will stop at that field.
   
   With WordApp.Selection
   
     .GoTo what:=wdGoToBookmark, Name:="Name"
     .TypeText Trim(Me.[FirstName] & " " & Me.[LastName])
   
     .GoTo what:=wdGoToBookmark, Name:="StreetAddress"
     .TypeText Me.[StreetAddress]
    
     .GoTo what:=wdGoToBookmark, Name:="City"
     .TypeText Me.[City]
  
     .GoTo what:=wdGoToBookmark, Name:="State"
     .TypeText Me.[State]
    
     .GoTo what:=wdGoToBookmark, Name:="ZipCode"
     .TypeText Me.[ZipCode]
         
     .GoTo what:=wdGoToBookmark, Name:="Name2"
     .TypeText Trim(Me.[FirstName] & " " & Me.[LastName])
     
     .GoTo what:=wdGoToBookmark, Name:="CourseTitle"
     .TypeText Me.[CourseTitle]
     
     .GoTo what:=wdGoToBookmark, Name:="Section"
     .TypeText Me.[SectionNumber]
              
     ' Not all courses have a final exam or discussion sections.
     ' Since the code stops if a field is null, this line inserts N/A if the
     ' field is empty and the code continues.
     ' Nz means that if the field has data in it, the data will be inserted.
     ' If the field is empty, N/A will be inserted.
     
     .GoTo what:=wdGoToBookmark, Name:="CourseFinalDTR"
     .TypeText Nz(Me.[CourseFinalD/T/R], "N/A")
     
     .GoTo what:=wdGoToBookmark, Name:="CourseFinalDate"
     .TypeText Nz(Me.[CourseFinalDate], "N/A")
                   
     .GoTo what:=wdGoToBookmark, Name:="CourseDisc1DT"
     .TypeText Nz(Me.[CourseDisc1D/T], "N/A")
          
     .GoTo what:=wdGoToBookmark, Name:="CourseDisc1Rm"
     .TypeText Nz(Me.[CourseDisc1Rm], "N/A")
          
     .GoTo what:=wdGoToBookmark, Name:="CourseDisc2DT"
     .TypeText Nz(Me.[CourseDisc2D/T], "N/A")
     
     .GoTo what:=wdGoToBookmark, Name:="CourseDisc2Rm"
     .TypeText Nz(Me.[CourseDisc2Rm], "N/A")
     
     .GoTo what:=wdGoToBookmark, Name:="CourseDisc3DT"
     .TypeText Nz(Me.[CourseDisc3D/T], "N/A")
          
     .GoTo what:=wdGoToBookmark, Name:="CourseDisc3Rm"
     .TypeText Nz(Me.[CourseDisc3Rm], "N/A")
          
     .GoTo what:=wdGoToBookmark, Name:="CourseDisc4DT"
     .TypeText Nz(Me.[CourseDisc4D/T], "N/A")
     
     .GoTo what:=wdGoToBookmark, Name:="CourseDisc4Rm"
     .TypeText Nz(Me.[CourseDisc4Rm], "N/A")
        
     .GoTo what:=wdGoToBookmark, Name:="TAComp"
     .TypeText FormatCurrency(Me.[TA])
                            
   End With
    
   DoEvents
   WordApp.Activate
    
   Set WordApp = Nothing
   Exit Sub

End If

If IsNull(Me.[Instructor]) And IsNull(Me.[TA]) And (IsNull(Me.[Reader]) = False) Then
   
   ' Specify location of template
   strTemplateLocation = "H:\Reader Contract Template.doc"
    
   On Error Resume Next
   Set WordApp = GetObject(, "Word.Application")
   If Err.Number <> 0 Then
     Set WordApp = CreateObject("Word.Application")
   End If
   On Error GoTo ErrHandler
   
   WordApp.Visible = True
   WordApp.WindowState = wdWindowStateMaximize
   WordApp.Documents.Add Template:=strTemplateLocation, NewTemplate:=False
    
   ' Finds Bookmark in the template listed above.
   ' Bookmark name from Word is listed under Name.
   ' Inserts field contents into bookmarked location.
   ' If a field is empty, the code will stop at that field.
   
   With WordApp.Selection
   
     .GoTo what:=wdGoToBookmark, Name:="Name"
     .TypeText Trim(Me.[FirstName] & " " & Me.[LastName])
   
     .GoTo what:=wdGoToBookmark, Name:="StreetAddress"
     .TypeText Me.[StreetAddress]
    
     .GoTo what:=wdGoToBookmark, Name:="City"
     .TypeText Me.[City]
  
     .GoTo what:=wdGoToBookmark, Name:="State"
     .TypeText Me.[State]
    
     .GoTo what:=wdGoToBookmark, Name:="ZipCode"
     .TypeText Me.[ZipCode]
         
     .GoTo what:=wdGoToBookmark, Name:="Name2"
     .TypeText Trim(Me.[FirstName] & " " & Me.[LastName])
     
     .GoTo what:=wdGoToBookmark, Name:="CourseTitle"
     .TypeText Me.[CourseTitle]
     
     .GoTo what:=wdGoToBookmark, Name:="Section"
     .TypeText Me.[SectionNumber]
              
     ' Not all courses have a final exam or discussion sections.
     ' Since the code stops if a field is null, this line inserts N/A if the
     ' field is empty and the code continues.
     ' Nz means that if the field has data in it, the data will be inserted.
     ' If the field is empty, N/A will be inserted.
     
     .GoTo what:=wdGoToBookmark, Name:="CourseFinalDTR"
     .TypeText Nz(Me.[CourseFinalD/T/R], "N/A")
     
     .GoTo what:=wdGoToBookmark, Name:="CourseFinalDate"
     .TypeText Nz(Me.[CourseFinalDate], "N/A")
                   
     .GoTo what:=wdGoToBookmark, Name:="ReaderComp"
     .TypeText FormatCurrency(Me.[Reader])
                            
   End With
    
   DoEvents
   WordApp.Activate
    
   Set WordApp = Nothing
   Exit Sub

End If

ErrHandler:
Set WordApp = Nothing

End Sub

[ssanfu]

This is the code I came up with. It's probably much messier than Cases, but it does seem to work.
Should I be concerned about using it?

As long as your document returns what you want, its good. There are a few things that could be done to streamline your code; it all depends on how much time you want to put into it. It does help with code maintenance to streamline to code, but it is not necessary.

[alpinegroove]

I still have time at this point, so any suggestions would be helpful.

Is the cross tab query a concern. Some have suggested that it is a bad practice to use it, but I can't figure out any other solution given the business process.

Thanks!

[huongdl1987]

Hi, I was in similar situation as you. And Here is how I solved mine using a combo box

Option Compare Database
Public Function CreateWordLetter(strDocPath As String)
    On Error GoTo MergeButton_Error
    'if no path is passed to function, exit
    
    If IsNull(strDocPath) Or strDocPath = "" Then
        Exit Function
    End If
    
    Dim dbs As Database
    Dim objWord As Object
    Dim PrintResponse
    
    Set dbs = CurrentDb
    
    'create reference to Word Object
    
    Set objWord = CreateObject("Word.Application")
    
    'Word Object is created - now let's fill it with data
    
    With objWord
        .Visible = True
        .Documents.Open (strDocPath)
        
        'move to each bookmark, and insert correct text.
        .ActiveDocument.Bookmarks("firstname").Select
        .Selection.Text = (CStr(Forms!MailMerge!strMomFirstName))
                        
        .ActiveDocument.Bookmarks("address").Select
        .Selection.Text = (CStr(Forms!MailMerge!UpdatedMomAddress))
        
        .ActiveDocument.Bookmarks("city").Select
        .Selection.Text = (CStr(Forms!MailMerge!UpdatedMomCity))
        
        .ActiveDocument.Bookmarks("state").Select
        .Selection.Text = (CStr(Forms!MailMerge!UpdatedMomState))
        
        .ActiveDocument.Bookmarks("zip").Select
        .Selection.Text = (CStr(Forms!MailMerge!UpdatedMomZip))
        
        .ActiveDocument.Bookmarks("firstname2").Select
        .Selection.Text = (CStr(Forms!MailMerge!strMomFirstName))
        
        .ActiveDocument.Bookmarks("lastname").Select
        .Selection.Text = (CStr(Forms!MailMerge!strMomLastName))
        
    End With
    
    'find out if the user would like to print the document at this time.
    
    PrintResponse = MsgBox("Print this document?", vbYesNo)
    If PrintResponse = vbYes Then
        objWord.ActiveDocument.PrintOut Background:=False
    End If
    
MergeButton_Error:
    'If a field on the form is empty, remove the bookmark text, and continue.
    If Err.Number = 5941 Then
        objWord.Selection.Text = ""
    Resume Next
    End If
    
    'release all objects
    
    Set objWord = Nothing
    Set dbs = Nothing
    
End Function
          
Private Sub cbxTemplate_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
End Sub
Private Sub cmdMailMerge_Click()
    If Not Len(cbxTemplate.Value) = 0 Then
        CreateWordLetter (cbxTemplate.Value)
    Else
        MsgBox "Select Mail Merge process"
    End If
           
    If cbxTemplate.Value = "Z:\Studies\Cleft Utah New CDC Study\Utah mother file\ThankYou.docx" Then
            DateofThankYouLetterSent = Date
            TrackingStatus = 8
        Else
            DateofLetterSent = Date
            TrackingStatus = 2
    End If
    
End Sub

Private Sub Combo121_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
End Sub