Word VBA Data from Access

[JulieK]

Re: https://www.accessforums.net/forms/t...otf-25113.html
I have a similar issue. I have vba that sends my form data to a word document (see below code) but I need it to loop through the last 5 fields in the code but so far I can only get it to send the first record. There are anywhere from 1 to about 20 records depending on the number of containers per order. The first 22 fields are header information. It is based on a query where the information in the first 22 fields is duplicated for each container (container details are represented in the last 5 fields). I need the header information to appear once in the word document and all associated container details. Need help with how to structure the code to loop through the container information only.

Private Sub cmdcreate_Click()

On Error GoTo MergeButton_Err

Dim objWord As Word.Application
Dim rsCOA As DAO.Recordset
Set objWord = GetObject("Word.Application")
Set rsCOA = CurrentDb.OpenRecordset("frmcoa")

With objWord
.Visible = True

.Documents.Open ("H:\Certificate.docx")

rsCOA.MoveFirst
.ActiveDocument.Bookmarks("txtwbcnum").Select
.Selection.Text = (CStr(Forms![frmcoa]![txtwbcnum]))
.ActiveDocument.Bookmarks("txtordernum").Select
.Selection.Text = (CStr(Forms![frmcoa]![txtordernum]))
.ActiveDocument.Bookmarks("txtbookingnum").Select
.Selection.Text = (CStr(Forms![frmcoa]![txtbookingnum]))
.ActiveDocument.Bookmarks("txtsupplier").Select
.Selection.Text = (CStr(Forms![frmcoa]![txtsupplier]))
.ActiveDocument.Bookmarks("txtsaddress1").Select
.Selection.Text = (CStr(Forms![frmcoa]![txtsaddress1]))
.ActiveDocument.Bookmarks("txtsaddress2").Select
.Selection.Text = (CStr(Forms![frmcoa]![txtsaddress2]))
.ActiveDocument.Bookmarks("txtsaddress3").Select
.Selection.Text = (CStr(Forms![frmcoa]![txtsaddress3]))
.ActiveDocument.Bookmarks("txtconsignee").Select
.Selection.Text = (CStr(Forms![frmcoa]![txtconsignee]))
.ActiveDocument.Bookmarks("txtdeladdress1").Select
.Selection.Text = (CStr(Forms![frmcoa]![txtdeladdress1]))
.ActiveDocument.Bookmarks("txtdeladdress2").Select
.Selection.Text = (CStr(Forms![frmcoa]![txtdeladdress2]))
.ActiveDocument.Bookmarks("txtdeladdress3").Select
.Selection.Text = (CStr(Forms![frmcoa]![txtdeladdress3]))
.ActiveDocument.Bookmarks("txtcountry").Select
.Selection.Text = (CStr(Forms![frmcoa]![txtcountry]))
.ActiveDocument.Bookmarks("txtcustomer").Select
.Selection.Text = (CStr(Forms![frmcoa]![txtcustomer]))
.ActiveDocument.Bookmarks("txtaddress1").Select
.Selection.Text = (CStr(Forms![frmcoa]![txtaddress1]))
.ActiveDocument.Bookmarks("txtaddress2").Select
.Selection.Text = (CStr(Forms![frmcoa]![txtaddress2]))
.ActiveDocument.Bookmarks("txtaddress3").Select
.Selection.Text = (CStr(Forms![frmcoa]![txtaddress3]))
.ActiveDocument.Bookmarks("txtccountry").Select
.Selection.Text = (CStr(Forms![frmcoa]![txtCcountry]))
.ActiveDocument.Bookmarks("txtloadportname").Selec t
.Selection.Text = (CStr(Forms![frmcoa]![txtloadportname]))
.ActiveDocument.Bookmarks("txtvesselvoyage").Selec t
.Selection.Text = (CStr(Forms![frmcoa]![txtvesselvoyage]))
.ActiveDocument.Bookmarks("dtmsailingdate").Select
.Selection.Text = (CStr(Forms![frmcoa]![dtmsailingdate]))
.ActiveDocument.Bookmarks("txtdisportname").Select
.Selection.Text = (CStr(Forms![frmcoa]![txtdisportname]))


.ActiveDocument.Bookmarks("txtdcountry").Select
.Selection.Text = (CStr(Forms![frmcoa]![txtDcountry]))
.ActiveDocument.Bookmarks("txtcontainernum").Selec t
.Selection.Text = (CStr(Forms![frmcoa]![txtcontainernum]))
.ActiveDocument.Bookmarks("txtpackages").Select
.Selection.Text = (CStr(Forms![frmcoa]![txtpackages]))
.ActiveDocument.Bookmarks("txtproductname").Select
.Selection.Text = (CStr(Forms![frmcoa]![txtproductname]))
.ActiveDocument.Bookmarks("numahecc").Select
.Selection.Text = (CStr(Forms![frmcoa]![numahecc]))
.ActiveDocument.Bookmarks("numweight").Select
.Selection.Text = (CStr(Forms![frmcoa]![numweight]))
rsCOA.MoveNext

End With

Set objWord = Nothing
Set rsCOA = Nothing

Exit Sub

MergeButton_Err:
If Err.Number <> 0 Then
Set objWord = New Word.Application
Resume Next
End If

End Sub

[atuhacek]

Assuming the records are in this recordset

Private Sub cmdcreate_Click()


On Error GoTo MergeButton_Err


Dim objWord As Word.Application
Dim rsCOA As DAO.Recordset
Set objWord = GetObject("Word.Application")
Set rsCOA = CurrentDb.OpenRecordset("frmcoa")


With objWord
.Visible = True


.Documents.Open ("H:\Certificate.docx")


rsCOA.MoveFirst
.ActiveDocument.Bookmarks("txtwbcnum").Select
.Selection.Text = (CStr(Forms![frmcoa]![txtwbcnum]))
.ActiveDocument.Bookmarks("txtordernum").Select
.Selection.Text = (CStr(Forms![frmcoa]![txtordernum]))
.ActiveDocument.Bookmarks("txtbookingnum").Select
.Selection.Text = (CStr(Forms![frmcoa]![txtbookingnum]))
.ActiveDocument.Bookmarks("txtsupplier").Select
.Selection.Text = (CStr(Forms![frmcoa]![txtsupplier]))
.ActiveDocument.Bookmarks("txtsaddress1").Select
.Selection.Text = (CStr(Forms![frmcoa]![txtsaddress1]))
.ActiveDocument.Bookmarks("txtsaddress2").Select
.Selection.Text = (CStr(Forms![frmcoa]![txtsaddress2]))
.ActiveDocument.Bookmarks("txtsaddress3").Select
.Selection.Text = (CStr(Forms![frmcoa]![txtsaddress3]))
.ActiveDocument.Bookmarks("txtconsignee").Select
.Selection.Text = (CStr(Forms![frmcoa]![txtconsignee]))
.ActiveDocument.Bookmarks("txtdeladdress1").Select
.Selection.Text = (CStr(Forms![frmcoa]![txtdeladdress1]))
.ActiveDocument.Bookmarks("txtdeladdress2").Select
.Selection.Text = (CStr(Forms![frmcoa]![txtdeladdress2]))
.ActiveDocument.Bookmarks("txtdeladdress3").Select
.Selection.Text = (CStr(Forms![frmcoa]![txtdeladdress3]))
.ActiveDocument.Bookmarks("txtcountry").Select
.Selection.Text = (CStr(Forms![frmcoa]![txtcountry]))
.ActiveDocument.Bookmarks("txtcustomer").Select
.Selection.Text = (CStr(Forms![frmcoa]![txtcustomer]))
.ActiveDocument.Bookmarks("txtaddress1").Select
.Selection.Text = (CStr(Forms![frmcoa]![txtaddress1]))
.ActiveDocument.Bookmarks("txtaddress2").Select
.Selection.Text = (CStr(Forms![frmcoa]![txtaddress2]))
.ActiveDocument.Bookmarks("txtaddress3").Select
.Selection.Text = (CStr(Forms![frmcoa]![txtaddress3]))
.ActiveDocument.Bookmarks("txtccountry").Select
.Selection.Text = (CStr(Forms![frmcoa]![txtCcountry]))
.ActiveDocument.Bookmarks("txtloadportname").Selec t
.Selection.Text = (CStr(Forms![frmcoa]![txtloadportname]))
.ActiveDocument.Bookmarks("txtvesselvoyage").Selec t
.Selection.Text = (CStr(Forms![frmcoa]![txtvesselvoyage]))
.ActiveDocument.Bookmarks("dtmsailingdate").Select
.Selection.Text = (CStr(Forms![frmcoa]![dtmsailingdate]))
.ActiveDocument.Bookmarks("txtdisportname").Select
.Selection.Text = (CStr(Forms![frmcoa]![txtdisportname]))
.ActiveDocument.Bookmarks("txtdcountry").Select
.Selection.Text = (CStr(Forms![frmcoa]![txtDcountry]))
.ActiveDocument.Bookmarks("txtcontainernum").Selec t
.Selection.Text = (CStr(Forms![frmcoa]![txtcontainernum]))
.ActiveDocument.Bookmarks("txtpackages").Select
.Selection.Text = (CStr(Forms![frmcoa]![txtpackages]))
.ActiveDocument.Bookmarks("txtproductname").Select
.Selection.Text = (CStr(Forms![frmcoa]![txtproductname]))
.ActiveDocument.Bookmarks("numahecc").Select
.Selection.Text = (CStr(Forms![frmcoa]![numahecc]))
.ActiveDocument.Bookmarks("numweight").Select
.Selection.Text = (CStr(Forms![frmcoa]![numweight]))


Do Until .EOF
    'Add the five fields inside here
    'Not sure how youare putting them in Word
    
rsCOA.MoveNext
Loop


End With


Set objWord = Nothing
Set rsCOA = Nothing


Exit Sub


MergeButton_Err:
If Err.Number <> 0 Then
Set objWord = New Word.Application
Resume Next
End If


End Sub

[atuhacek]

this won't work with a sub form. you will need to use the sub forms recordset for that.

[ItsMe]

Why are you retrieving data from a form? Is frmcoa a bound form?

[atuhacek]

That's a good question. And why not loop through the heading info?

[JulieK]

Thanks for the suggested code atuhacek. But, it keeps looping the first record only in word. I have to end task on word and access to get it to stop. I think it has more to do with how I have the fields set up in word. Open to suggestions for a different way to accomplish what I need.

[JulieK]

It is a bound, continuous form (no subform). The form really only displays the information that is destine for the word doc so the user can check all details are correct before creating it but also gives the user the opportunity to amend any information before sending to word. The word doc is a government template we are required to fill in and submit in word format so I really need a solution to this. The header information only needs to appear on the word doc once, its the container information related to the header that can have multiple records. I.e. 1 set of client information and any number of containers the client has shipped.

[atuhacek]

I need to see your form or query in order to know how you data the query would be best it would also be helpful to see the report is going to. I think we can simplify your code and use the do loop to get your desired result.

[ItsMe]

It is a bound...

Since it is bound, I recommend using a RecordsetClone

Instead of this line of code
Set rsCOA = CurrentDb.OpenRecordset("frmcoa")
Use
Set rsCOA = Me.RecordsetClone

There are a couple of other small changes you would make too. Let me know if you would like to explore this option.

The problem you are having seems to be associated with the fact that you are writing data to the same fields in your word doc. In other words, you .MoveNext and then use the data in the next record to writre to the exact same fields you just populated.

If you have five fields in your word doc that need to be populated with a number of records that varies from 1 to 20, you are going to need the number of fields in your Word doc to be dynamic. The "group of five" fields will need to be duplicated a number of times to match the Record Count of your Recordset. You are currently moving through your Access database, looking at different records but writing the data (from various records) to the same fields in your word doc.

Another thing that is puzzling to me is this line
H:\Certificate.docx

If you have a Word doc with fields, I believe the doc has to be Macro enabled so
H:\Certificate.docm

You need to make sure your word doc is a trusted doc. I recommend not placing your Word doc in the Root of H. Create a folder to hold it in or use UNC instead of Mapping a drive to the server.

[atuhacek]

I am curious why you chose to update a word document instead of creating the document in access then exporting it as a word document. ItsMe brings up some valid points especially about the word fields. Do you get anything to write from the last five fields and if so which record is writting. If its the last one then it is writting over itself. If your not getting anything then did you add your writting code between the do loop I added to your code.

[JulieK]

Thank you both for spending time on this, I really appreciate it. Below is my query statement:

SELECT tblorders.txtordernum, tblorders.txtbookingnum, tblbookings.txtwbcnum, tblsuppliers.txtsupplier, tblsuppliers.txtsaddress1, tblsuppliers.txtsaddress2, tblsuppliers.txtsaddress3, tblcustdelivery.txtconsignee, tblcustdelivery.txtdeladdress1, tblcustdelivery.txtdeladdress2, tblcustdelivery.txtdeladdress3, tblcountries.txtcountry, tblcustomers.txtcustomer, tblcustomers.txtaddress1, tblcustomers.txtaddress2, tblcustomers.txtaddress3, tblloadports.txtloadportname, tblvessels.txtvesselvoyage, tblvessels.dtmsailingdate, tbldisport.txtdisportname, tblcontainers.txtcontainernum, [numitems] & " " & [txtunitshort] AS txtpackages, tblapprovals.txtproductname, tblahecc.numahecc, [numitems]*[tblcontainers]![numunitsg] AS numweight
FROM tblunits INNER JOIN (tblsuppliers INNER JOIN ((tblcountries INNER JOIN (tbldisport INNER JOIN (tblvessels INNER JOIN ((tblloadports INNER JOIN ((tblcustomers INNER JOIN tblcustdelivery ON tblcustomers.txtcustomer = tblcustdelivery.txtcustomerdel) INNER JOIN (tblbookings INNER JOIN tblorders ON tblbookings.txtbookingnum = tblorders.txtbookingnum) ON tblcustomers.txtcustomer = tblorders.txtcustomer) ON tblloadports.numloadport = tblbookings.numloadport) INNER JOIN tblcontainers ON tblorders.txtordernum = tblcontainers.txtordernum) ON tblvessels.numvessel = tblbookings.numvessel) ON tbldisport.numdisport = tblbookings.numdisport) ON (tblcountries.txtcountrycode = tblcustomers.txtcountrycode) AND (tblcountries.txtcountrycode = tblcustdelivery.txtdcountrycode)) INNER JOIN ((tblahecc INNER JOIN tblapprovals ON tblahecc.numahecc = tblapprovals.numahecc) INNER JOIN tblorderdetails ON tblapprovals.numproduct = tblorderdetails.numproduct) ON tblorders.txtordernum = tblorderdetails.txtordernum) ON (tblsuppliers.numsupplierid = tblorders.numsupplierid) AND (tblsuppliers.numsupplierid = tblapprovals.numsupplierid)) ON (tblunits.numunit = tblorderdetails.numunit) AND (tblunits.numunit = tblcontainers.numunit)
WHERE (((tblorders.txtbookingnum)=[Forms]![frmbookings]![txtbookingnum]));

I have attached the certificate I have to work with (it's accessible on a public web page so there are no privacy issues). I have also attached a spread sheet showing the resulting records from the query (customer information has been altered for privacy).

[JulieK]

Also, the location of the certificate (on the H drive) is only temporary while I'm working out this solution. The certificate will be in a folder on a network share with the file path updated once I have it all working (with your very much appreciated help)

[JulieK]

ItsMe is correct, it is cycling through the first record as there are only one set of fields to take the data for the containers. I need this group of fields to expand to accommodate the number of containers there are which varies from booking to booking. I just don't know how to accomplish what I need

[ItsMe]

I went ahead and created some code to address the last five fields in your Word doc. It does not address the first fields because you have already accomplished this. I did not test my code, I simply created it in my minds eye. So you get to test it. You will need to add your FieldNames to the code. These are the names of the fields at the table layer.

Dim objWord As Word.Application
Dim rsCOA As DAO.Recordset
Set objWord = GetObject("Word.Application")
Set rsCOA = Me.RecordsetClone
    If mrsCOA.EOF = True Then
        MsgBox "There are not any records available for Export. Now Exiting."
        Set rsCOA = Nothing
        objWord = Nothing
    Exit Sub
    End If
Dim strContainernum As String
Dim strPackages As String
Dim strProductname As String
Dim strNumahecc As String
Dim strNumweight As String
'Initialize your variables
strContainernum = ""
strPackages = ""
strProductname = ""
strNumahecc = ""
strNumweight = ""

rsCOA.MoveFirst 'It is OK to MoveFirst because we have records
    While rsCOA.EOF = False
        strContainernum = strContainernum & rsCOA![FieldName] & vbCrLf
        strPackages = strPackages & rsCOA![FieldName] & vbCrLf
        strProductname = strProductname & rsCOA![FieldName] & vbCrLf
        strNumahecc = strNumahecc & rsCOA![FieldName]& vbCrLf
        strNumweight = strNumweight & rsCOA![FieldName]& vbCrLf
        rsCOA.MoveNext
    Wend    'rscoa.EOF
    
rsCOA.Close
Set rsCOA = Nothing
With objWord
.Visible = True
.Documents.Open ("H:\Certificate.docx")
.ActiveDocument.Bookmarks("txtcontainernum").Select
.Selection.Text = strContainernum
.ActiveDocument.Bookmarks("txtpackages").Select
.Selection.Text = strPackages
.ActiveDocument.Bookmarks("txtproductname").Select
.Selection.Text = strProductname
.ActiveDocument.Bookmarks("numahecc").Select
.Selection.Text = strNumahecc
.ActiveDocument.Bookmarks("numweight").Select
.Selection.Text = strNumweight
End With
Set objWord = Nothing

[JulieK]

Thanks guys for all your help but it isn't working. I have attacked it from a different angle though. I have code to append the query results to a table, set up the document as a mail merge linking to the table, then delete the records from the table once the form is closed. This seems to have done the trick. A few more clicks than I'd like to produce the final required result but its better then having to manually type the information in each time.

Thanks again