Passing form values to Word

[hawkingsright]

I have a problem with passing data to a word form for quoting customers. This works fine, but the Item Description and Pricing values won't transfer past the first entry. Any help would be greatly appreciated. Here's the code:

Dim appWord As Word.Application
Dim doc As Word.Document
Dim rst As ADODB.Recordset
Dim strSQL As String
Dim strReportsTo As String

On Error Resume Next
Set appWord = GetObject(, "Word.application")
If Err = 429 Then
    Set appWord = New Word.Application
    Err = 0
End If

With appWord
    Set doc = .Documents(DOC_NAME)
    'If Err = 0 Then
    '    If MsgBox("Do you want to save the current document " _
    '        & "before updating the data?", vbYesNo) = vbYes Then
    '            .Dialogs(wdDialogFileSaveAs).Show
    '    End If
    'doc.Close False
    'End If

    On Error GoTo ErrorHandler
    
    Set doc = .Documents.Open(DOC_PATH & DOC_NAME, , True)
    Set rst = New ADODB.Recordset
    
    With doc
        .FormFields("fldCOMPANY").Result = Nz(Me!COMPANY)
        .FormFields("fldCITY").Result = Nz(Me!CITY)
        .FormFields("fldSTATE").Result = Nz(Me!STATE)
        .FormFields("fldCONTACT").Result = Nz(Me!CONTACT)
        .FormFields("fldPHONE").Result = Nz(Me!PHONE)
        .FormFields("fldFax").Result = Nz(Me!Fax)
        .FormFields("fldDESCRIPTIO").Result = Nz(Me!History.Form.DESCRIPTIO)
        .FormFields("fldSELL").Result = Nz(Me!History.Form.Sell)
        .FormFields("fldSELLBROKEN").Result = Nz(Me!History.Form.SellBroken)
        
    End With
    .Visible = True
    .Activate
End With

Set rst = Nothing
Set doc = Nothing
Set appWord = Nothing
Exit Sub

ErrorHandler:
MsgBox Err & Err.Description


End Sub

 

[Libre]

I'm really not sure how to accomplish your specific goal, but in my experience, it is much easier to export data from Access to an Excel Spreadsheet (using the DoCmd.TransferSpreadsheet method), and then use that spreadsheet as a data source for a mail-merged Word doc, than it is to directly export from Access to Word - which I've never gotten to work satisfactorily.
I know it sounds like a work-around, and I guess it is, but it has worked for me every time.

 

[mdlueck]

Here is a sample of my code successfully transferring database field to a Word document:

Example Access VBA preparing a Word document using Range Bookmarks
http://www.access-programmers.co.uk/forums/showthread.php?t=236469

 

[hawkingsright]

thank you both for your suggestions.

 

[hawkingsright]

Here is what I ended up doing. Instead of using a dump to Word, I switched to Excel to make this a whole lot easier and less complicated. I hope it helps someone else looking for a solution.
Code is inserted in a button on the main form in Access:

Private Sub Command18584_Click()

On Error GoTo Command18584_Click_Err

Forms![My Form].SetFocus
DoCmd.GoToControl "MyControl"
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy

Dim xlapp As Excel.Application
Set xlapp = CreateObject("Excel.Application")
With xlapp
.Workbooks.Open "C: File Location\My File.xls"
.Sheets("My Sheet").Activate
.Sheets("My Sheet").PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
False

.Sheets("My Sheet").Cells.EntireColumn.AutoFit
.Visible = True

.Range("a5").Select

Forms![My Form]![My SubForm].SetFocus
DoCmd.GoToControl "My Control"
DoCmd.RunCommand acCmdSelectAllRecords
DoCmd.RunCommand acCmdCopy

.Sheets("My Sheet").PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
False

.Sheets("My Sheet").Cells.EntireColumn.AutoFit
.Visible = True

.Sheets("My Other Sheet For Printing").Activate
End With

Command18584_Click_Exit:
Exit Sub
Command18584_Click_Err:
MsgBox Error$
Resume Command18584_Click_Exit

End Sub