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

[Ciaran89]

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]

[twinnyfo]

You might be better off using a recordset for the MS Access data and then cycling through. However, you did not provide a whole lot of information that describes your db and how it is structured.

[Ciaran89]

A quotation is made in access as shown on screenshot 2. The data on this form then fills bookmarks in Word shown on screenshot 1. The quotes can contain more than 1 product ID (I've shown 2 in the list). When i export this to Word it fills only the first column of my table. I need to specify on the FormFill to retrieve data from the second row (record 2) to be able to fill the second column in my Word template.

Is there a way you can briefly describe the process of using a recordset for the data?

Many thanks.

[twinnyfo]

I am unable to see your screenshots here at work, so I am a little unclear about how this is being used. Perhaps paste your screenshots into a Word Doc and I should be able to see them.

Perhaps the screenshots will help me understand your problem a bit more clearly, as I need to see what is being updated and how.

If your Template does not contain any proprietary or confidential information, you can also attach that, to help us look at what you are trying to do.

We'll get there.... We just need to figure out how to go about it.

[Ciaran89]

http://www.landscapetreesecology.com...creenshots.doc

Apologies for the late response. I'm afraid I can't share the template as it is for work purposes, but I have shared a link with the screenshots in a Word document. I hope this helps and many thanks for your time on this.

[twinnyfo]

P.S., an easier way to send a Word Doc is to attach it (Don't use links, as they can be finicky for some people's work network systems).

I'll take a look and see what I can see....

[Ciaran89]

Rushed this reply earlier and forgot to click the 'advanced' button. I will take note of this next time. Thanks!

[twinnyfo]

I think one of your challenges may be the insertion of the form fields in Word with each consecutive record. I have not automated MS Word form fields with MS Access, although I am sure it can be done.

However, I think an easier solution would be to generate a report in MS Access and then export it, preferably in PDF--but RTF would work for opening in Word. I prefer PDF because it is more difficult to modify, but it is a universal document format.