We help IT Professionals succeed at work.
Get Started

How can I export Form and subform (datasheet) to word template ?

930 Views
Last Modified: 2016-02-11
Hi,

I would need to export a main form and subform (ONE subform is a datasheet with multiple entries\records) to a word template. The main and subform form exports fine by using bookmarks -as shown below- .

However, I'm struggling with exporting all the records from the subform (datasheet with multiple records).

The subform name is (frm00_tblScheduleT) and the requited field to be exported is :

- PaymentNumber
- DueDateG
- AmountDue

The number of records in this subform is varies based on number of repayments, it might be x record and up to xx records.

Any help on this would be greatly appreciated.

A sample of the code used below.



Private Sub cmdPrintContract_Click()

'Print customer Contract for current customer.

    Dim appWord As Word.Application
    Dim doc As Word.Document
    Dim FileRef As String

'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
    
    Set doc = appWord.Documents.Open("C:\Rent_Sys\Development_20150215\20150224_FINAL_CONTRACT_TEST.docx", , True)
    
    FileRef = Me.Contract_Ref_ID    ' Used to Save word doc name as Contract Number
    
        
    With doc
    .FormFields("fldContractNbr").Result = Me.Contract_Ref_ID
    .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("fldPart1RepsAuthority").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
 
 
    appWord.Visible = True
    appWord.Activate
    .SaveAs "C:\Rent_Sys\Development_20150215\" & FileRef
    
    End With
    
    Set doc = Nothing
    Set appWord = Nothing
    
    Exit Sub
    
errHandler:
    
    MsgBox Err.Number & ": " & Err.Description


End Sub

Open in new window

Comment
Watch Question
Retired
CERTIFIED EXPERT
Top Expert 2012
Commented:
This problem has been solved!
Unlock 1 Answer and 5 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE