Access to Word Mail Merge - Trouble with Word 2013

Dear Experts.

I have a form in ms access (2010) with a command Button to print a contract with (On Click) event procedure as illustrated in below vba code. (Thanks to ExperExchange who help in this code, "Regret I forget the name")

This code is working perfect with in my PC and other Pcs (using ms word 2010) , the word document is protected so only text form field is enabled for editing. However when using ms access 2013 and ms word 2013 all fields are exported to ms-word and ONLY the last part of code (BOLD - start from line # 136) is not extracted.

I would greatly appreciate your help.

In advance, Thanks a millions for expert cooperation.

Private Sub cmdPrintContract_Click()

'Print Rent Contract for Current Customer.

    Dim appWord As Word.Application
    Dim doc As Word.Document
    Dim FileRef As String
    Dim dd As String
    Dim User As String
    Dim DocPath As String
    Dim DocName As String
    Dim FullPath As String
    
    
    Dim tbl As Word.Table       
    Dim rs As Recordset         
   


'Avoid error 429, when Word isn't open.
    
    On Error Resume Next
    Err.Clear
    
'Set appWord object variable to running instance of Word.
    
    Set appWord = GetObject(, "Word.Application")
    
    If Err.Number <> 0 Then
    'If Word isn't open, create a new instance of Word.
    Set appWord = New Word.Application
    appWord.Visible = True
    End If
    
    
    DocPath = DLookup("[CLocationPath]", "tblDocuments", "CDocType = 'CONTRACT'")
    DocName = DLookup("[CDocName]", "tblDocuments", "CDocType = 'CONTRACT'")
    FullPath = DocPath & DocName
 
 
    Set doc = appWord.Documents.Open(FullPath, , True)        
    
    dd = Format(Now(), "yyyymmdd_hhmm")
    User = getUserName()
    

    FileRef = dd & "_" & User & "_" & Me.Contract_Ref_ID ' Used to Save word doc name as Contract Number
    
        
    With doc
    .FormFields("fldPart2Name").Result = Me.P2_FullName
    .FormFields("fldProjectName").Result = Form!frm00_tblProjectUnit_sub!Project_Name_A
    .FormFields("fldProjectUnitNBR").Result = Form!frm00_tblProjectUnit_sub!ProjectUnit_No
    .FormFields("fldContractDateHijra").Result = Me.StartDHijri
    .FormFields("fldContractDateGreg").Result = Me.StartDGreg
    
    .FormFields("fldPart1Name").Result = Me.P1_FullName
    .FormFields("fldPart1CR").Result = Me.P1_CR
    .FormFields("fld_P1_CR_DATE").Result = Form!frm00_tblPart1_sub!P1_CR_Date
    .FormFields("fldPart1Address").Result = Form!frm00_tblPart1_sub!P1_Address
    .FormFields("fldPart1POBOX").Result = Form!frm00_tblPart1_sub!P1_POBox
    .FormFields("fldPart1City").Result = Form!frm00_tblPart1_sub!P1_City
    .FormFields("fldPart1ZipCode").Result = Form!frm00_tblPart1_sub!P1_ZIPCode
    .FormFields("fldPart1Telephone").Result = Form!frm00_tblPart1_sub!P1_Phone
    .FormFields("fldPart1FAX").Result = Form!frm00_tblPart1_sub!P1_Fax
    .FormFields("fldPart1RepsName").Result = Form!frm00_tblPart1_sub!P1_Reps_Name
    .FormFields("fldPart1RepsID").Result = Form!frm00_tblPart1_sub!P1_Reps_ID
    .FormFields("fldPart1RepsPosition").Result = Form!frm00_tblPart1_sub!P1_Reps_Position
    .FormFields("fldPart1RepsAuth").Result = Form!frm00_tblPart1_sub!P1_Reps_Authority_Type
    
    .FormFields("fldPart2Name_2").Result = Me.P2_FullName
    .FormFields("fldPart2CR").Result = Me.P2_CR
    .FormFields("fldPart2CRDate").Result = Form!frm00_tblPart2_sub!P2_CR_Date
    .FormFields("fldPart2Address").Result = Form!frm00_tblPart2_sub!P2_Address
    .FormFields("fldPart2POBOX").Result = Form!frm00_tblPart2_sub!P2_POBox
    .FormFields("fldPart2City").Result = Form!frm00_tblPart2_sub!P2_City
    .FormFields("fldPart2ZipCode").Result = Form!frm00_tblPart2_sub!P2_ZIPCode
    .FormFields("fldPart2Telephone").Result = Form!frm00_tblPart2_sub!P2_Phone
    .FormFields("fldPart2Mobile").Result = Form!frm00_tblPart2_sub!P2_Mobile
    .FormFields("fldPart2Fax").Result = Form!frm00_tblPart2_sub!P2_Fax
    .FormFields("fldPart2RepsName").Result = Form!frm00_tblPart2_sub!P2_Reps_Name
    .FormFields("fldPart2RepsID").Result = Form!frm00_tblPart2_sub!P2_Reps_ID
    .FormFields("fldPart2RepsPosition").Result = Form!frm00_tblPart2_sub!P2_Reps_Position
    .FormFields("fldPart2RepsAuthorit").Result = Form!frm00_tblPart2_sub!P2_Reps_Authority_Type
    .FormFields("fldPart2Activities").Result = Form!frm00_tblPart2_sub!P2_Activities
    
    .FormFields("fldProjectName_2").Result = Form!frm00_tblProjectUnit_sub!Project_Name_A
    .FormFields("fldProjectUnitNBR_2").Result = Form!frm00_tblProjectUnit_sub!ProjectUnit_No
    .FormFields("fldProjectDistrict").Result = Form!frm00_tblProjectUnit_sub!Project_District
    .FormFields("fldProjectStreet").Result = Form!frm00_tblProjectUnit_sub!Project_Street
    
    .FormFields("fldProjectUnitNBR_3").Result = Form!frm00_tblProjectUnit_sub!ProjectUnit_No
    .FormFields("fldSQM").Result = Form!frm00_tblProjectUnit_sub!ProjectUnit_SQM
    
    .FormFields("fldNumOfYrs").Result = Me.NumberOfYears
    .FormFields("fldStartHijri").Result = Me.StartDHijri
    .FormFields("fldEndtHijri").Result = Me.EndDHijri
    '.FormFields("fldRenewalNotice").Result =
    .FormFields("fldLeaseAMT").Result = Me.LeaseAmount_No
    .FormFields("fldLeaseAMTWORDS").Result = Me.LeaseAmount_Words
    .FormFields("fldRepaymentPeriod").Result = Me.RePaymentPeriod
    .FormFields("fldPayInAdvance").Result = Me.Pay_In_Advance_Month
    .FormFields("fldP2CheqName").Result = Me.P1_FullName
    
    .FormFields("fldInsuranceAMT").Result = Me.InsuranceAmount
    .FormFields("fldInsuranceAMTWORDS").Result = Me.InsuranceAmount_Words
    .FormFields("fldParking").Result = Form!frm00_tblProjectUnit_sub!ProjectUnit_Parking
    
    .FormFields("fldPrepation").Result = Me.Preparation_NbrOfMonth
    .FormFields("fldPrepationDate").Result = Me.Preparation_StartDateH
    
    
    .FormFields("fldProjectUnitNBR_4").Result = Form!frm00_tblProjectUnit_sub!ProjectUnit_No
    .FormFields("fldProjectName_3").Result = Form!frm00_tblProjectUnit_sub!Project_Name_A
    .FormFields("fldProjectDistrict_2").Result = Form!frm00_tblProjectUnit_sub!Project_District
    .FormFields("fldProjectStreet_2").Result = Form!frm00_tblProjectUnit_sub!Project_Street
    .FormFields("fldContractDateH_2").Result = Me.StartDHijri
    .FormFields("fldPart1Name_2").Result = Me.P1_FullName
    .FormFields("fldPart2Name_3").Result = Me.P2_FullName
    .FormFields("fldPart2Name_4").Result = Me.P2_FullName
    .FormFields("fldPart2CR_2").Result = Me.P2_CR
    .FormFields("fldPart2CRDate_2").Result = Form!frm00_tblPart2_sub!P2_CR_Date
    .FormFields("fldPart2Address_2").Result = Form!frm00_tblPart2_sub!P2_Address
    .FormFields("fldPart2POBOX_2").Result = Form!frm00_tblPart2_sub!P2_POBox
    .FormFields("fldPart2City_2").Result = Form!frm00_tblPart2_sub!P2_City
    .FormFields("fldPart2ZipCode_2").Result = Form!frm00_tblPart2_sub!P2_ZIPCode
    .FormFields("fldPart2Telephone_2").Result = Form!frm00_tblPart2_sub!P2_Phone
    .FormFields("fldPart2Fax_2").Result = Form!frm00_tblPart2_sub!P2_Fax
    .FormFields("fldPart2RepsName_2").Result = Form!frm00_tblPart2_sub!P2_Reps_Name
    .FormFields("fldPart2RepsID_2").Result = Form!frm00_tblPart2_sub!P2_Reps_ID
    .FormFields("fldPart2RepsPos_2").Result = Form!frm00_tblPart2_sub!P2_Reps_Position
    .FormFields("fldPart2RepsAuth_2").Result = Form!frm00_tblPart2_sub!P2_Reps_Authority_Type
    .FormFields("fldPart2Name_5").Result = Me.P2_FullName
    
    
[b]    Set rs = Forms!frm00_tblContract!frm00_tblScheduleT.Form.RecordsetClone 

        Set tbl = .Tables.Add(.Bookmarks("fldPaymentTable").Range, 1, 3) 
        
        
        With tbl.Rows.First
            .Cells(1).Range.Text = "Payment Number"
            .Cells(2).Range.Text = "Date"
            .Cells(3).Range.Text = "Due Amount"
        End With
        rs.MoveFirst
        Do Until rs.EOF
            Set rw = tbl.Rows.Add
            'With tbl.Rows.First
            With rw
                .Cells(1).Range.Text = rs.Fields("ST_PaymentNumber")
                .Cells(2).Range.Text = rs.Fields("ST_DueDateH")
                .Cells(3).Range.Text = rs.Fields("ST_AmountDue")
            End With
            rs.MoveNext
        Loop
        rs.Close[/b]

 
    appWord.Visible = True             
    appWord.Activate                   
    
 
    .SaveAs DocPath & FileRef           'see tbldocument path
    .SaveAs "C:\Rent_Sys_Production\ListOfContracts\" & FileRef
    
    
    
    
    
    End With
    
    Set doc = Nothing
    Set appWord = Nothing
    
    Exit Sub
    
errHandler:
    
    MsgBox Err.Number & ": " & Err.Description


End Sub

Open in new window

TAB-000Asked:
Who is Participating?
 
GrahamSkanRetiredCommented:
I'm sorry. The only extra thing that I can suggest is that you post the original document here. There just might be something significant about the location of the bookmark.

If you don't want the document to be generally available, you can attach it as a message to me. I will try to keep the EE community informed with any relevant details
0
 
GrahamSkanRetiredCommented:
You are suppressing all error reporting with On Error Resume Next so we can't see what is going wrong.

There is some simple error handling  code at the end of the procedure, so this code uses that. It replaces lines 20 to 33
'Avoid error 429, when Word isn't open.
    
    On Error Resume Next
    Err.Clear
    
'Set appWord object variable to running instance of Word.
    
    Set appWord = GetObject(, "Word.Application")
    On Error GoTo errHandler 'resume error handling
    If appWord Is Nothing Then
        'If Word isn't open, create a new instance of Word.
        Set appWord = New Word.Application
        appWord.Visible = True
    End If

Open in new window

0
 
TAB-000Author Commented:
Dear Graham.

Having modify the code as provided, I've received the following error :

5830 : The character position is not valid or not in the right place.

All data was fields data exported ONLY the table not exported.

Kindly advice.

Thanks.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
GrahamSkanRetiredCommented:
On which line?
0
 
TAB-000Author Commented:
There wasn't any line number, only above message.
0
 
TAB-000Author Commented:
Fyi the word document contains 11 pages, the (text form field) is distributed in different pages, and the table position (fldPaymentTable) located in pages number 4, whereas in vba code was placed at the end.

i.e. vba code exported all (text form field) then start the table export. Does this impact the code? or have something wrong?

The export from access to ms word 2010 is working fine, but only with ms word 2013 the table is not exported.

Thanks.
0
 
GrahamSkanRetiredCommented:
Is it a Compile or a run-time error?
0
 
GrahamSkanRetiredCommented:
Sorry. I just remembered that there is an error handler.  It won't show you the failing line.

Change this line:
On Error GoTo errHandler 'resume error handling

Open in new window

To this:
On Error GoTo 0 'resume error handling

Open in new window


The code should then break on the failing line
0
 
TAB-000Author Commented:
Dear GrahamSkan ,

I'll back to you soon with the result.

Best regards.
0
 
TAB-000Author Commented:
Dear GrahamSkan ,

Sorry to the delay as I was away from my office.

Having changed the error handler line,  This message appeared (5830 : The character position is not valid or not in the right place). and the code breaks on the following line :

Set tbl = .Tables.Add(.Bookmarks("fldPaymentTable").Range, 1, 3)

Open in new window


Regards.
0
 
GrahamSkanRetiredCommented:
Is the bookmark 'fldPaymentTable' in an unprotected section?
0
 
TAB-000Author Commented:
Yes it is in an unprotected section.
0
 
GrahamSkanRetiredCommented:
I have never seen this error message before and can find no useful information on it. I only have up to Word 2007, so am unlikely to be able to reproduce the problem. As a workaround I suggest that you try removing the protection before adding the table  and re-applying it after that work is done. Some new lines are inserted into the code and marked with  '<---
    Set rs = Forms!frm00_tblContract!frm00_tblScheduleT.Form.RecordsetClone
        If .ProtectionType = wdAllowOnlyFormFields Then '<---
            .Unprotect 'password here if there is one  '<---
        End If                                         '<---
        Set tbl = .Tables.Add(.Bookmarks("fldPaymentTable").Range, 1, 3)
        
        
        With tbl.Rows.First
            .Cells(1).Range.Text = "Payment Number"
            .Cells(2).Range.Text = "Date"
            .Cells(3).Range.Text = "Due Amount"
        End With
        rs.MoveFirst
        Do Until rs.EOF
            Set rw = tbl.Rows.Add
            'With tbl.Rows.First
            With rw
                .Cells(1).Range.Text = rs.Fields("ST_PaymentNumber")
                .Cells(2).Range.Text = rs.Fields("ST_DueDateH")
                .Cells(3).Range.Text = rs.Fields("ST_AmountDue")
            End With
            rs.MoveNext
        Loop
        rs.Close [/b]

    .Protect wdAllowOnlyFormFields, True ', Password here  '<---
    appWord.Visible = True
    appWord.Activate

Open in new window

0
 
TAB-000Author Commented:
Dear GrahamSkan,

The suggested modification lines has been applied and still the situation as is. Furthermore, the following actions has been done and still all ([text form field]) exported, except table is not exported to word :

1) Removed all protections from ms word 2010 -->> still the situation remain the same.
2) Convert ms word document to ms word 2013 -->> still the situation remain the same.
3) Convert and Removed all protections from ms word 2013 -->> still the situation remain the same.

Any other idea or workaround are highly appreciated.

Best regards.
0
 
TAB-000Author Commented:
Dear GrahamSkan,

First of all, I would like to thank you very much for your cooperation to help.

I'll send the document to you shortly. Can we try one more step please.

That we'll unprotect and protect the document from vba . Hence could you please confirm if the expression .UnProtect and protect in below code is correct (assume the password is 1234)

.Unprotect 1234       '<--- 
.Protect wdAllowOnlyFormFields, , 1234  '<--- 

Open in new window



Best regards.
0
 
GrahamSkanRetiredCommented:
Yes, that looks OK.
0
 
TAB-000Author Commented:
I'll explain what I did to you.

1) I have protected the word document (No Changes "Read Only")  with a password. and I made sure non of the field or content is editable.
2) Applied the above code.

But unfortunately, non of the data was exported.

Any how, I'll send you the word document, but the content is Arabic Language.

best regards.
0
 
GrahamSkanRetiredCommented:
I don't know why it didn't work.

It's interesting to learn that the document text is in Arabic. I Googled the exact message and  got very few results. The main one had the Arabic translation of the phrase:
http://www.proz.com/?sp=gloss/term&id=2996650
I don't know anything about the language except that it runs from right-to-left, so I wondered if it is possible that there is some new check for right to left languages.

However there are a couple of problems with the document that make me wonder how it can work in any Word version. Firstly, there is only one Section, so if there is to be forms protection, it will apply to the whole document. Secondly, the bookmark is for a formfield. In my version of Word (2007) this gives an error:

Run-time error '6208':
The range cannot be deleted.

This happens whether protection is applied or not. The error is because you can't insert a table into a form field.

I have inserted a pair of continuous section breaks in the document, one before and one after the position, removed the form field and replaced the bookmark. The document is protected without a password, but with the middle section unprotected. I will attach it to a message. This should work without the protect and unprotect code.
0
 
TAB-000Author Commented:
Dear Graham,

Since this morning, I've gone with extra steps and I would like to share it and the results with you.

I have created NEW document, and I've used the bookmarks rather than formfield, to which the vba code has been changed from (.FormFields("XYZ").Result = Me.XYZ) to (..Bookmarks("XYZ").Range = Me.XYZ), The observation as follow:-

1- ms word 2010 - Results:
Exported from access 2010 to word 2010 , were completed without error and all required fields & Table done.

2- ms word 2013 - Results:
a) Converted the doc to word-2013.
b) Exported from access 2013 to word 2013,
c) Although the document is unprotected but its not completed and this gives an error:

Run-time error '6208':
You are mot allowed to edit this section because it is protected.


FileRef = Me.Contract_Ref_ID & "_" & User & "_" & dd     
    
        
    With doc
    .Bookmarks("fldPart2Name").Range = Me.P2_FullName        '<<<- - - - - --  The falling line
    
    .Bookmarks("fldProjectName").Range = Form!frm00_tblProjectUnit_sub!Project_Name_A
    .Bookmarks("fldProjectUnitNBR").Range = Form!frm00_tblProjectUnit_sub!ProjectUnit_No

Open in new window


As a summary :-

1- Access and word 2010 :
1.1 Using formfield : exported successfully either with protection or without, also the table was exported to form field.
1.2 Using bookmarks:  exported successfully either with protection or without, also the table was exported to bookmarks.

2- Access and word 2013 :
2.1 Using formfield : All fields exported EXCEPT Recordset (table).
2.2 Using bookmarks : Not exported - either for fields or the table, and it gives the Run-time error '6208'.. etc.

I've used the formfield because its easy to modify the format of the field such as Date or Number, whereas -Honestly- its first time for me using bookmarks and not sure if I can change the format for the fields or the table.

Once again Graham, I appreciate your cooperation and swift reply and looking forward to be in touch.

Thanks and best regards
0
 
TAB-000Author Commented:
GrahamSkan went extra miles and extended his full support to make things solved.

His dedication was and were amazing.

Regards.
0
 
GrahamSkanRetiredCommented:
Thanks.

Form Fields are easy to use programatically, but the automatic formatting facility is there mainly to help when they are filled manually. If you are using VBA to apply text to a document, you can do the formatting in the code.

doc.Bookmarks("MyDate").Range.Text  = Format(dtAdate, "dd/MM/yyyy")
0
 
TAB-000Author Commented:
Once again thanks a millions.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.