Automation to Word using Crosstab Query

[besuchanko]

Hello,

I'm wondering if someone could help me with a dilemma. I'm using a crosstab query in Access to export a form letter to Word via VBA automation. This query generates a list of sessions as they're entered into the database. Right now, I'd have to manually add a session to my VBA code every time a session is added to the database in order for all of them to appear in the Word document. Is there a way to code it so that it will dynamically add all the sessions to the Word document if they exist? In my query, it would include field 6 onward. Right now, the query only includes fields 6-11, but eventually there will be a whole lot more sessions and I have no idea how many.

Below is my code, excluding the actual form letter text occuring before the session list.

Private Sub Command0_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim fld As FieldSet db = CurrentDb()
Set rs = db.OpenRecordset("Faculty_Crosstab_Qry_TEST_Crosstab")
WordEx
Do Until rs.EOF

'Session List
If Not IsNull(rs.Fields(6)) Then
sel.TypeText rs.Fields(6) & vbCrLf
Else: sel.TypeText ""
End If

If Not IsNull(rs.Fields(7)) Then
sel.TypeText rs.Fields(7) & vbCrLf
Else: sel.TypeText ""
End If

If Not IsNull(rs.Fields(8)) Then
sel.TypeText rs.Fields(8) & vbCrLf
Else: sel.TypeText ""
End If

If Not IsNull(rs.Fields(9)) Then
sel.TypeText rs.Fields(9) & vbCrLf
Else: sel.TypeText ""
End If

If Not IsNull(rs.Fields(10)) Then
sel.TypeText rs.Fields(10) & vbCrLf
Else: sel.TypeText ""
End If

If Not IsNull(rs.Fields(11)) Then
sel.TypeText rs.Fields(11) & vbCrLf
Else: sel.TypeText ""
End If

[burrina]

Select * * is a select all syntax

[besuchanko]

I did end up figuring this out on my own. It was as simple as looping through the records in the query from field 6 to field Count-1.

[besuchanko]

I'm back with a new but related question. I got the code right so that it loops thru the values from my crosstab query. As I do so, I create a comma delimited list. The problem is, I can't figure out how to get rid of the comma from the last item when I'm done. I've scoured the internet and the typical solutions don't seem to work.

        For y = 5 To rst.Fields.Count - 1
        If Not IsNull(rst.Fields(y)) Then
        .Cell(2, 1).Range.InsertAfter rst.Fields(y) & ", "
        Else: .Cell(2, 1).Range.InsertAfter ""
        End If
        Next

As you can probably tell, the list is added to a cell in a table. Can anyone help? I'm losing my mind.