Exporting access records to Word

[tamus121]

I am exporting data from Access (2010) to Word and at the end of the Word document I use the following code segment to place records from a table WarePlantNonCom. The records are placed in Word but I can't find a way to line up the data from the 4 fields under their headings. If a value has extra characters it can push the rest of the data out of line with the previous record or headings. Tried using GetString but it throws an error.

Any help or suggestions appreciated.

tamus121

Dim rs As DAO.Recordset
Dim txt As String
Dim new_range As Range

    strSql = "SELECT FieldNo, Area, Variety, NonCom FROM WarePlantNonCom WHERE WPID =" & Forms!WarePlantInspLetter!WPID
    Set rs = DBEngine(0)(0).OpenRecordset(strSql)

    Do Until rs.EOF
        txt = txt + "  " & rs.Fields("FieldNo") & vbTab & "" & vbTab & "" & vbTab & "" & vbTab & rs.Fields("Area") & vbTab & rs.Fields("Variety") & vbTab & rs.Fields("NonCom") & vbCrLf
       rs.MoveNext
    Loop
    
    ' Close the Recordset
    rs.Close

    ' Make a Range at the end of the Word document
    Set new_range = appWord.ActiveDocument.Range
    new_range.Collapse wdCollapseEnd

    ' Insert the text and convert it to a table
    new_range.InsertAfter txt
    new_range.ConvertToTable Separator:=wdSeparateByParagraphs

 

[tamus121]

Well I got this to work, used vbCrlf between the fields, set the number of columns to 4 and adjusted the size of the columns by setting the cell sizes

seem to have got the errors out and it does what I want

Dim rs As DAO.Recordset
Dim txt As String
Dim new_range As Range
Dim oRow As Row

    strSql = "SELECT FieldNo, Area, Variety, NonCom FROM WarePlantNonCom WHERE WPID =" & Forms!WarePlantInspLetter!WPID
    Set rs = DBEngine(0)(0).OpenRecordset(strSql)
    
    Do Until rs.EOF
        txt = txt + "  " & rs.Fields("FieldNo") & vbCrLf & rs.Fields("Area") & "ha" & vbCrLf & rs.Fields("Variety") & vbCrLf & rs.Fields("NonCom") & vbCrLf
        rs.MoveNext
    Loop

    ' Close the Recordset
    rs.Close

    ' Make a Range at the end of the Word document
    Set new_range = appWord.ActiveDocument.Range
    new_range.Collapse wdCollapseEnd

    ' Insert the text and convert it to a table
    new_range.InsertAfter txt
    new_range.ConvertToTable Separator:=wdSeparateByParagraphs, NumColumns:=4

With new_range.Tables(1)
    For Each oRow In new_range.Tables(1).Rows
        oRow.Cells(1).Width = appWord.InchesToPoints(2.1)
        oRow.Cells(2).Width = appWord.InchesToPoints(0.75)
        oRow.Cells(3).Width = appWord.InchesToPoints(1.9)
        oRow.Cells(4).Width = appWord.InchesToPoints(2)
    Next
End With