Continuous subform output to microsoft word?

[Marclem]

Hi all,

I would like to have all my data sent to a microsoft word file from a continuous subform.

At this moment I can only send one record.

This is my code below:

Private Sub Command143_Click()

On Error GoTo markerr:

Dim objWord As Word.Application
Set objWord = CreateObject("Word.application")
With objWord
.Visible = True
.Documents.Open ("Y:\Mark\Vendor's Database\PO\GAMEPO.doc")
.ActiveDocument.Bookmarks("PO").Select
.Selection.Text = (CStr(Forms![PO ALL WOOL]![PO]))
.ActiveDocument.Bookmarks("Account").Select
.Selection.Text = (CStr(Forms![PO ALL WOOL]![Acct #]))
.ActiveDocument.Bookmarks("Company").Select
.Selection.Text = (CStr(Forms![PO ALL WOOL]![TO]))
.ActiveDocument.Bookmarks("CUSTOMER").Select
.Selection.Text = (CStr(Forms![PO ALL WOOL]![ATT]))
.ActiveDocument.Bookmarks("TODAY").Select
.Selection.Text = (CStr(Forms![PO ALL WOOL]![DATE]))
.ActiveDocument.Bookmarks("REQ").Select
.Selection.Text = (CStr(Forms![PO ALL WOOL]![REQUISITION #]))
.ActiveDocument.Bookmarks("REQUISITIONED").Select
.Selection.Text = (CStr(Forms![PO ALL WOOL]![REQUISITIONED BY]))
.ActiveDocument.Bookmarks("SHIP").Select
.Selection.Text = (CStr(Forms![PO ALL WOOL]![WHEN SHIP]))
.ActiveDocument.Bookmarks("VIA").Select
.Selection.Text = (CStr(Forms![PO ALL WOOL]![Ship Via]))
.ActiveDocument.Bookmarks("FOB").Select
.Selection.Text = (CStr(Forms![PO ALL WOOL]![F O B POINT]))
.ActiveDocument.Bookmarks("TERM").Select
.Selection.Text = (CStr(Forms![PO ALL WOOL]![Terms]))
.ActiveDocument.Bookmarks("QTYORD").Select
.Selection.Text = (CStr(Forms![PO ALL WOOL]![PO ALL WOOL DESC subform].Form![QTY ORD]))
.ActiveDocument.Bookmarks("UM").Select
.Selection.Text = (CStr(Forms![PO ALL WOOL]![PO ALL WOOL DESC subform].Form![U/M]))
.ActiveDocument.Bookmarks("ITEM").Select
.Selection.Text = (CStr(Forms![PO ALL WOOL]![PO ALL WOOL DESC subform].Form![ITEM]))
.ActiveDocument.Bookmarks("STYLE").Select
.Selection.Text = (CStr(Forms![PO ALL WOOL]![PO ALL WOOL DESC subform].Form![STYLE]))
.ActiveDocument.Bookmarks("STOCKNUMBER").Select
.Selection.Text = (CStr(Forms![PO ALL WOOL]![PO ALL WOOL DESC subform].Form!

))
.ActiveDocument.Bookmarks("UP").Select
.Selection.Text = (CStr(Forms![PO ALL WOOL]![PO ALL WOOL DESC subform].Form![UP]))
.ActiveDocument.Bookmarks("TOTAL").Select
.Selection.Text = (CStr(Forms![PO ALL WOOL]![PO ALL WOOL DESC subform].Form![TOTAL]))
.ActiveDocument.Bookmarks("NOTES").Select
.Selection.Text = (CStr(Forms![PO ALL WOOL]![PO ALL WOOL DESC subform].Form![NOTE]))
.ActiveDocument.Bookmarks("grandtotal").Select
.Selection.Text = (CStr(Forms![PO ALL WOOL]![PO ALL WOOL DESC subform].Form![GRABTOTAL]))[/b]
.ActiveDocument.Bookmarks("Authorized").Select
.Selection.Text = (CStr(Forms![PO ALL WOOL]![REQUISITIONED BY]))
.ActiveDocument.Bookmarks("Copy").Select
.Selection.Text = (CStr(Forms![PO ALL WOOL]![COPY]))
End With

Dim m As String
m = Me.WPO & " " & Me.TO & ".doc"
objWord.ActiveDocument.SaveAs FileName:="Y:\Mark\Vendor's Database\PO\" & m

markerr:
If Err.Number = 94 Then
objWord.Selection.Text = ""
Resume Next
End If
Exit Sub
End Sub

Would I need to create more bookmarks?
I use this file to create a purchase order.

Thanks in advance for you attention!

 

[RoyVidar]

Hi!

Here you grab the current record of the subform, you might perhaps consider fetching the subform recordset, and loop thru it (short sample, I'm to lazy to go thur your complete example, I'm afraid;-)):

[tt]dim rs as dao.recordset
set rs=Forms![PO ALL WOOL]![PO ALL WOOL DESC subform].Form.RecordsetClone
rs.movefirst
do while not rs.eof
' do all your assigning to word here, using the field names
' the controls are bound to
rs.movenext
loop
set rs=nohting[/tt]

- would require a reference to Microsoft DAO 3.# Ojbect Library (VBE - Tools | References) - and the question of bookmarks - probably, but that would be rather static, wouldn't it? Not very familiar with Word programming, but I assume you can set the "starting" point thru a bookmark, then just "keep on writing", using linebraks at the end of each lines...?

Roy-Vidar

 

[Marclem]

Thanks Roy,

When I click the button it opens word fine and starts to fill in my fields but I still do not know how to make all these subform records to appear on this file.

Not very familiar with Word programming, but I assume you can set the "starting" point thru a bookmark, then just "keep on writing", using linebraks at the end of each lines...?
Roy-Vidar

 

[RoyVidar]

I thought my previous post would give the means of fetching the recordset of the subform. Try just enter

[tt]debug.print rs(0), rs(1), rs(2)[/tt]

inside the above loop, to see whether it does that (hit CTRL+G to study the results after running).

I'm not very familiar with Word programming. If someone else doesn't pop in on how exactly to populate word with the information of the recordset, perhaps try posting in forum707? I just thought that if you placed the cursor somewhere (bookmark?) you could:

[tt]do while not rs.eof
.selection.typetext rs![QTY ORD] & " " & rs![U/M] & _
...<rest of the fields>... & rs![GRABTOTAL] & vbCr
rs.movenext
loop[/tt]

To populate several rows in Word. Doesn't that work?

Roy-Vidar