Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-20-2007, 08:42 AM   #1
ssenyl
Registered User
 
Join Date: Apr 2007
Location: Gosport, Hants
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
ssenyl is on a distinguished road
Export a table column to a single Word table cell

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

ssenyl is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
way too many tables qwertyjjj Theory and practice of database design 3 08-01-2007 01:04 PM
[SOLVED] Want to export only single row of table - any ideas? PurpleHeather Modules & VBA 3 09-18-2005 02:04 PM
Alter table rename column RobMc Queries 3 07-28-2005 04:21 AM
Help with Relationships and table design: Bill Harrison Tables 7 01-12-2005 06:44 AM




All times are GMT -8. The time now is 03:42 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World