Access 2016 VBA

I'm using the following code to write records to Word 2016 from a table. My table contains two records however only one record is being written to Word. Any thoughts?

Function Export2DOC(sQuery As String)
    Dim oWord           As Object
    Dim oWordDoc        As Object
    Dim oWordTbl        As Object
    Dim bWordOpened     As Boolean
    Dim db              As DAO.Database
    Dim rs              As DAO.Recordset
    Dim iCols           As Integer
    Dim iRecCount       As Integer
    Dim iFldCount       As Integer
    Dim i               As Integer
    Dim j               As Integer
    Const wdPrintView = 3
    Const wdWord9TableBehavior = 1
    Const wdAutoFitFixed = 0
 
 
    'Start Word
   On Error Resume Next
    Set oWord = GetObject("Word.Application")    'Bind to existing instance of Word
 
    If Err.Number <> 0 Then    'Could not get instance of Word, so create a new one
        Err.Clear
        On Error GoTo Error_Handler
        Set oWord = CreateObject("Word.application")
        bWordOpened = False
    Else    'Word was already running
        bWordOpened = True
    End If
    On Error GoTo Error_Handler
    oWord.Visible = False   'Keep Word hidden until we are done with our manipulation
    Set oWordDoc = oWord.Documents.Add   'Start a new document
 
    'Open our SQL Statement, Table, Query
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblSearch")
    With rs
        If .RecordCount <> 0 Then
            .MoveLast   'Ensure proper count
            iRecCount = .RecordCount    'Number of records returned by the table/query
            .MoveFirst
            iFldCount = .Fields.Count   'Number of fields/columns returned by the table/query
 
            oWord.ActiveWindow.View.Type = wdPrintView 'Switch to print preview mode (not req&#39;)
            oWord.ActiveDocument.Tables.Add Range:=oWord.Selection.Range, NumRows:=iRecCount, NumColumns:= _
                                            iFldCount, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _
                                            wdAutoFitFixed
 
            Set oWordTbl = oWordDoc.Tables(1)
            'Build our Header Row
            For i = 0 To iFldCount - 1
                oWordTbl.Cell(1, i + 1) = rs.Fields(i).Name
            Next i
            'Build our data rows
            For i = 1 To iRecCount
                For j = 0 To iFldCount - 1
                    oWordTbl.Cell(i + 1, j + 1) = Nz(rs.Fields(j).Value, "")
                Next j
                .MoveNext
            Next i
        Else
            MsgBox "There are no records returned by the specified queries/SQL statement.", vbCritical + vbOKOnly, "No data to generate an Word spreadsheet with"
            GoTo Error_Handler_Exit
        End If
    End With
 
    '    oWordDoc.Close True, sFileName 'Save and close
 
    'Close Word if is wasn't originally running
    '    If bWordOpened = False Then
    '        oWord.Quit
    '    End If
 
Error_Handler_Exit:
    On Error Resume Next
    oWord.Visible = True   'Make Word visible to the user
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Set oWordTbl = Nothing
    Set oWordDoc = Nothing
    Set oWord = Nothing
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: Export2DOC" & vbCrLf & _
           "Error Description: " & Err.Description _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function

Open in new window

shieldscoAsked:
Who is Participating?
 
NorieVBA ExpertCommented:
Now that you aren't using the variable i for looping through the records you'll need to increment it separately and set its initial value.
   Set oWordTbl = oWordDoc.Tables(1)

            'Build our Header Row
            For i = 0 To iFldCount - 1
                oWordTbl.Cell(1, i + 1) = rs.Fields(i).Name
            Next i

            i = 2 ' first row of data goes in 2nd row of table

            'Build our data rows
            Do Until rs.EOF = True
                For j = 0 To iFldCount - 1
                    oWordTbl.Cell(i, j + 1) = Nz(rs.Fields(j).Value, "")
                Next j
                .MoveNext
                i = i + 1
            Loop

Open in new window

0
 
PatHartmanCommented:
Rather than using
For i = 1 To iRecCount
...
Next I

use the more normal
Do Until rs.EOF = True
...
    rs.MoveNext
Loop
0
 
shieldscoAuthor Commented:
Do without loop error

 Do Until rs.EOF = True
                For j = 0 To iFldCount - 1
                    oWordTbl.Cell(i + 1, j + 1) = Nz(rs.Fields(j).Value, "")
                 rs.MoveNext
               ' Next j
                '.MoveNext
            'Next i
            Loop

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
PatHartmanCommented:
You have commented out your "Next J"

You have also commented out the "rs.MoveNext"  but you'll see that problem when the loop never ends.
0
 
shieldscoAuthor Commented:
Still only one record and now partial data
'Build our data rows
            Do Until rs.EOF = True
                For j = 0 To iFldCount - 1
                    oWordTbl.Cell(i + 1, j + 1) = Nz(rs.Fields(j).Value, "")
                 rs.MoveNext
                Next j
                .MoveNext
            'Next i
            Loop

Open in new window

0
 
PatHartmanCommented:
We are looking at the code out of context so who knows what is going on.  There was no need to do a .movelast since you really don't need a record count.  If you left in the .movelast, did you also leave in the .movefirst?


PS - always remove dead code unless you really think you might need to reactivate it.  So, get rid of the 'Next i
0
 
shieldscoAuthor Commented:
Yes... same results
0
 
PatHartmanCommented:
If you want someone to help debug your code, you will need to post it after each change so we can see what it is "now".
0
 
shieldscoAuthor Commented:
Thanks Norie

 i = 2 ' first row of data goes in 2nd row of table

changed to I=1 worked great
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.