How to use .FormFields to fill numerous records under the same column

[Ciaran89]

Hi, First time to these forums
I hope this makes some sense!

I am using .FormFields to fill a quotation template in Word, but the subform I am retrieving the data from has rows of records under each column and I need to send more than just the first row to fill the required bookmarks in Word.

The code i'm using is

Set doc = appWord.Documents.Open("S:\Templates\Quotation Template - Test.doc", , True)
With doc
.FormFields("FirstName").Result = Me!FirstName
.FormFields("FirstName2").Result = Me!FirstName
.FormFields("LastName").Result = Me!LastName
.FormFields("LastName2").Result = Me!LastName
.FormFields("CompanyName").Result = Me!CompanyName
.FormFields("CompanyName2").Result = Me!CompanyName
.FormFields("Address1").Result = Me!Address1
.FormFields("Address1_2").Result = Me!Address1
.FormFields("Address2").Result = Me!Address2
.FormFields("Address2_2").Result = Me!Address2
.FormFields("Address3").Result = Me!Address3
.FormFields("Address3_2").Result = Me!Address3
.FormFields("fldRegion").Result = Me!Region
.FormFields("PostalCode").Result = Me!PostalCode
.FormFields("PostalCode2").Result = Me!PostalCode
.FormFields("emailname").Result = Me!EmailName
.FormFields("emailname2").Result = Me!EmailName
.FormFields("QuoteReference").Result = Me!QuoteReference
.FormFields("QuoteReference2").Result = Me!QuoteReference
.FormFields("QuoteProjectName").Result = Me!QuoteProjectName
.FormFields("QuoteProjectName2").Result = Me!QuoteProjectName
.FormFields("QuoteDate").Result = Me!QuoteDate
.FormFields("QuoteDate2").Result = Me!QuoteDate
.FormFields("txtVatValue").Result = Me!txtVatValue
.FormFields("txtTotal").Result = Me!txtTotal
.FormFields("txtSubTotal").Result = Me!txtSubTotal
.FormFields("cmbStaff").Result = Me!cmbStaff
 
.FormFields("ProductID").Result = Me.[frmQuotesSub].Form.[ProductID]
.FormFields("Qty").Result = Me.[frmQuotesSub].Form.[Qty]
.FormFields("UnitPrice").Result = Me.[frmQuotesSub].Form.[UnitPrice]
.FormFields("ExtendedPrice").Result = Me.[frmQuotesSub].Form.[ExtendedPrice]

Many thanks.

 

[pr2-eugin]

Hello Ciaran89, Welcome to AWF

What I would suggest you is to create a RecordSet object and then loop through it. Something like,

    Dim rsObj As DAO.RecordSet
    
    Set rsObj = Me.RecordsetClone
    
    Do While NOT rsObj.EOF
        .FormFields("FirstName").Result = rsObj.Fields("FirstName")
        .FormFields("FirstName2").Result = rsObj.Fields("FirstName")
        .FormFields("LastName").Result = rsObj.Fields("LastName")
        :
        :
        .FormFields("txtSubTotal").Result = rsObj.Fields("txtSubTotal")
        .FormFields("cmbStaff").Result = rsObj.Fields("cmbStaff")
        
        rsObj.MoveNext
    Loop
    
    Set rsObj = Nothing

More on creating and manipulating Recordsets :Recordsets for Beginners - UtterAccess

Although the other more sensible option would be to create Word Mail Merge.

 

[Ciaran89]

Hi, thanks for the quick reply!

I can't quite explain it, but Mail Merge just wasn't suitable for how I wanted to use and store information with our database and with Word.

I've had a read through what you linked and have read about the 'Looping a Recordset'. Do I need to loop only the fields in the subform which are filling rows in my Word table.. or do I need to loop all the records for this process?

The only fields that require more than 1 row of data are:

ProductID Qty UnitPrice ExtendedPrice

Apologies if this isn't clear but I don't have permission to post images until 10 posts! No doubt it won't be long before i reach that though.

 

[pr2-eugin]

You can also use a Query to Open the Recordset and loop through. Using a Query can also make it very simple terms of getting the right data. With your description it is not sure what you need.

You can post pics before you reach the post limit, as long as you ZIP them and add to your reply.

 

[Ciaran89]

To save misleading you I will attach some images of what I'm trying to achieve.

At the moment the 'Export To Word' button will FormFill fields from the form in Access to what is required in the Word template. As an example i've listed 2 Product ID's which appear in the subform (although there could be more) and as of the code at the moment only the first row in the Word template is filled.

Would another option be to make this sub form display in textboxes below the form that aren't visible in the form. So that as rows are added these are then displayed in the main form below and each row in Word can be filled in the same way as my code is set to now?

I've made some major changes to the database we are using and this is the only part that is stopping me from having it ready to use by all. :banghead:

Thanks again