Export a table column to a single Word table cell (1 Viewer)

Hi All,

I know that similar things have been discussed on this forum (because I have spent all day on here - much to the dismay of my boss!!), but I do not seem to be able to find the answer I am looking for.

I have a form, which is based on a query. The form also contains a subform, based on another query, which has a single column displaying a list of item numbers.

I am exploring ways of getting all of this information into a word document.

The code I have works perfectly - up to a point.

I am able to get all of the information out from the main form into my word document.

When it gets to the part where I want the full list of items numbers to be inserted, all I get is the first one in the list.

I would really appreciate if someone could point me in the right direction.

Ultimately what I would like to achieve is that all of the items appear horizontally (separated by commas would be great but no essential.

This is my code (the problem bit highlighted in red):

Private Sub btnAuditSheet_Click()
On Error GoTo btnAuditSheet_Click_Err

Dim dbs As DAO.Database
Dim rstMergeThese As Recordset
Dim oApp As Object
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmPrintAuditSht"

stLinkCriteria = "[ID]=" & Me![ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Set oApp = CreateObject("Word.Application")
oApp.Visible = True

With oApp
.Documents.Open "e:\HTCT Audit Sheet.doc"
' Move to each bookmark and insert text from the form.

.Activedocument.Bookmarks("CCRIO").Select
.Selection.Text = (CStr(Forms![frmPrintAuditSht]![CCRIO No]))
.Activedocument.Bookmarks("CCT").Select
.Selection.Text = (CStr(Forms![frmPrintAuditSht]![HTCT No]))
.Activedocument.Bookmarks("Yr").Select
.Selection.Text = (CStr(Forms![frmPrintAuditSht]![Year]))
.Activedocument.Bookmarks("analyst").Select
.Selection.Text = (CStr(Forms![frmPrintAuditSht]![Analyst]))
.Activedocument.Bookmarks("subdate").Select
.Selection.Text = (CStr(Forms![frmPrintAuditSht]![Submission date]))
.Activedocument.Bookmarks("number").Select
.Selection.Text = (CStr(Forms![frmPrintAuditSht]![Number]))
.Activedocument.Bookmarks("rank").Select
.Selection.Text = (CStr(Forms![frmPrintAuditSht]![Rank]))
.Activedocument.Bookmarks("name").Select
.Selection.Text = (CStr(Forms![frmPrintAuditSht]![Name]))
.Activedocument.Bookmarks("acqhrs").Select
.Selection.Text = (CStr(Forms![frmPrintAuditSht]![Acquisition Hrs]))
.Activedocument.Bookmarks("analhrs").Select
.Selection.Text = (CStr(Forms![frmPrintAuditSht]![Analysis Hrs]))
.Activedocument.Bookmarks("mischrs").Select
.Selection.Text = (CStr(Forms![frmPrintAuditSht]![Misc Hrs]))
.Activedocument.Bookmarks("acqstart").Select
.Selection.Text = (CStr(Forms![frmPrintAuditSht]![AcqStart]))
.Activedocument.Bookmarks("acqfinish").Select
.Selection.Text = (CStr(Forms![frmPrintAuditSht]![AcqFinish]))
.Activedocument.Bookmarks("analstart").Select
.Selection.Text = (CStr(Forms![frmPrintAuditSht]![Analysis Start Date]))
.Activedocument.Bookmarks("rptdate").Select
.Selection.Text = (CStr(Forms![frmPrintAuditSht]![Report Date]))
.Activedocument.Bookmarks("analfin").Select
.Selection.Text = (CStr(Forms![frmPrintAuditSht]![Analysis Finish Date]))
.Activedocument.Bookmarks("hddqty").Select
.Selection.Text = (CStr(Forms![frmPrintAuditSht]![Hard Diskqty]))
.Activedocument.Bookmarks("hdd").Select
.Selection.Text = (CStr(Forms![frmPrintAuditSht]![HDD]))
.Activedocument.Bookmarks("cdqty").Select
.Selection.Text = (CStr(Forms![frmPrintAuditSht]![cdqty]))
.Activedocument.Bookmarks("cd").Select
.Selection.Text = (CStr(Forms![frmPrintAuditSht]![CD]))
.Activedocument.Bookmarks("dvdqty").Select
.Selection.Text = (CStr(Forms![frmPrintAuditSht]![dvdqty]))
.Activedocument.Bookmarks("dvd").Select
.Selection.Text = (CStr(Forms![frmPrintAuditSht]![DVD]))
.Activedocument.Bookmarks("hdqty").Select
.Selection.Text = (CStr(Forms![frmPrintAuditSht]![BluRay/HDqty]))
.Activedocument.Bookmarks("hd").Select
.Selection.Text = (CStr(Forms![frmPrintAuditSht]![HD]))
.Activedocument.Bookmarks("fdqty").Select
.Selection.Text = (CStr(Forms![frmPrintAuditSht]![fdqty]))
.Activedocument.Bookmarks("fd").Select
.Selection.Text = (CStr(Forms![frmPrintAuditSht]![FD]))
.Activedocument.Bookmarks("mediaqty").Select
.Selection.Text = (CStr(Forms![frmPrintAuditSht]![Media cardsqty]))
.Activedocument.Bookmarks("media").Select
.Selection.Text = (CStr(Forms![frmPrintAuditSht]![Media]))
.Activedocument.Bookmarks("usbqty").Select
.Selection.Text = (CStr(Forms![frmPrintAuditSht]![usbqty]))
.Activedocument.Bookmarks("usb").Select
.Selection.Text = (CStr(Forms![frmPrintAuditSht]![USB]))
.Activedocument.Bookmarks("pdaqty").Select
.Selection.Text = (CStr(Forms![frmPrintAuditSht]![pdaqty]))
.Activedocument.Bookmarks("pda").Select
.Selection.Text = (CStr(Forms![frmPrintAuditSht]![PDA]))
.Activedocument.Bookmarks("tapeqty").Select
.Selection.Text = (CStr(Forms![frmPrintAuditSht]![Backup tapesqty]))
.Activedocument.Bookmarks("tape").Select
.Selection.Text = (CStr(Forms![frmPrintAuditSht]![Tapes]))
.Activedocument.Bookmarks("zipqty").Select
.Selection.Text = (CStr(Forms![frmPrintAuditSht]![Zip Diskqty]))
.Activedocument.Bookmarks("zip").Select
.Selection.Text = (CStr(Forms![frmPrintAuditSht]![Zip]))
.Activedocument.Bookmarks("otherqty").Select
.Selection.Text = (CStr(Forms![frmPrintAuditSht]![otherqty]))
.Activedocument.Bookmarks("other").Select
.Selection.Text = (CStr(Forms![frmPrintAuditSht]![Other]))
.Activedocument.Bookmarks("data").Select
.Selection.Text = (CStr(Forms![frmPrintAuditSht]![Total]))
.Activedocument.Bookmarks("exhibits").Select
.Selection.Text = (CStr(Forms![frmPrintAuditSht]![frmAuditExhibits]![Exhibit No]))
End With

DoCmd.Close , frmAuditExhibits

btnAuditSheet_Click_Exit:

Exit Sub

btnAuditSheet_Click_Err:

If Err.Number = 94 Then
oApp.Selection.Text = ""
Resume Next
End If

End Sub

Many thanks in advance for your help and Merry Christmas!!

Regards

Rob