Inserting multiple Access records into Word bookmark field

[Jody]

I have created a sales call report database in Access.
When the user clicks a "Print Report" button it generates
a Word report based on the current record. Everything
works well except for one of my bookmarks in Word. I'm
trying to concatenate the names of all people who attended
the meeting (which are captured as individual records in a
separate table in Access) and place them into one field in
Word called bmkAttendees. When my code runs, it only
places the first name in my recordset into the bookmark
field instead of all four names. Each name is separated by
a semicolon. My question is: what other VB code do I need
(perhaps a loop?). I'm fairly new to VB and not sure
where to begin.

Here's the code to generate the recordset:

strSQL = "SELECT Attend_FNm, Attend_LNm, Attend_Title FROM
CallAttendees " _
& "WHERE [Call_VisitID]=" & Me![Call_VisitID]
rst.Open strSQL

With rst
strAttendees = Nz(.Fields("Attend_FNm")) & " " & Nz
(.Fields("Attend_LNm")) & ", " _
& Nz(.Fields("Attend_Title")) & "; "
.Close
End With

Here's the code to "insert" the data into the bmkAttendees
bookmark in Word::

With objWord
.Visible = True
Set doc = .Documents.Add("\\Data1\public data\DEPT
SHARES\RQP Sales\Sales Trip Report.dot")
With doc.Bookmarks
.Item("bmkSalesPerson").Range.Text =
strSalesPerson
.Item("bmkCustomer").Range.Text = strCustomer
.Item("bmkRept_Date").Range.Text = dtmRept_Date
.Item("bmkAttendees").Range.Text = strAttendees

End With
End With

Any suggestions would be greatly appreciated.

 

[Gary Miller]

Jody,

You are exactly right. You need to add a loop to your code
assuming that the Call_VisitID filter will give you all the
folks you need. Here is part of your recordset code amended
a bit...

With rst
Do Until .EOF
strAttendees = strAttendees &
Nz(.Fields("Attend_FNm")) & " " & Nz
(.Fields("Attend_LNm")) & ", " _
& Nz(.Fields("Attend_Title")) & "; "
.MoveNext
Loop
.Close
End With

Set rst = Nothing

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________

 

[Jody]

Thank you -- It works great!

-----Original Message-----
Jody,

You are exactly right. You need to add a loop to your code
assuming that the Call_VisitID filter will give you all the
folks you need. Here is part of your recordset code amended
a bit...

With rst
Do Until .EOF
strAttendees = strAttendees &
Nz(.Fields("Attend_FNm")) & " " & Nz
(.Fields("Attend_LNm")) & ", " _
& Nz(.Fields("Attend_Title")) & "; "
.MoveNext
Loop
.Close
End With

Set rst = Nothing

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________

I have created a sales call report database in Access.
When the user clicks a "Print Report" button it generates
a Word report based on the current record. Everything
works well except for one of my bookmarks in Word. I'm
trying to concatenate the names of all people who attended
the meeting (which are captured as individual records in a
separate table in Access) and place them into one field in
Word called bmkAttendees. When my code runs, it only
places the first name in my recordset into the bookmark
field instead of all four names. Each name is separated by
a semicolon. My question is: what other VB code do I need
(perhaps a loop?). I'm fairly new to VB and not sure
where to begin.

Here's the code to generate the recordset:

strSQL = "SELECT Attend_FNm, Attend_LNm, Attend_Title FROM
CallAttendees " _
& "WHERE [Call_VisitID]=" & Me![Call_VisitID]
rst.Open strSQL

With rst
strAttendees = Nz(.Fields("Attend_FNm")) & " " & Nz
(.Fields("Attend_LNm")) & ", " _
& Nz(.Fields("Attend_Title")) & "; "
.Close
End With

Here's the code to "insert" the data into the bmkAttendees
bookmark in Word::

With objWord
.Visible = True
Set doc = .Documents.Add("\\Data1\public data\DEPT
SHARES\RQP Sales\Sales Trip Report.dot")
With doc.Bookmarks
.Item("bmkSalesPerson").Range.Text =
strSalesPerson
.Item("bmkCustomer").Range.Text = strCustomer
.Item("bmkRept_Date").Range.Text = dtmRept_Date
.Item("bmkAttendees").Range.Text = strAttendees

End With
End With

Any suggestions would be greatly appreciated.

.

 

[Gary Miller]

Glad to help.

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________

Thank you -- It works great!

-----Original Message-----
Jody,

You are exactly right. You need to add a loop to your code
assuming that the Call_VisitID filter will give you all the
folks you need. Here is part of your recordset code amended
a bit...

With rst
Do Until .EOF
strAttendees = strAttendees &
Nz(.Fields("Attend_FNm")) & " " & Nz
(.Fields("Attend_LNm")) & ", " _
& Nz(.Fields("Attend_Title")) & "; "
.MoveNext
Loop
.Close
End With

Set rst = Nothing

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________

I have created a sales call report database in Access.
When the user clicks a "Print Report" button it generates
a Word report based on the current record. Everything
works well except for one of my bookmarks in Word. I'm
trying to concatenate the names of all people who attended
the meeting (which are captured as individual records in a
separate table in Access) and place them into one field in
Word called bmkAttendees. When my code runs, it only
places the first name in my recordset into the bookmark
field instead of all four names. Each name is separated by
a semicolon. My question is: what other VB code do I need
(perhaps a loop?). I'm fairly new to VB and not sure
where to begin.

Here's the code to generate the recordset:

strSQL = "SELECT Attend_FNm, Attend_LNm, Attend_Title FROM
CallAttendees " _
& "WHERE [Call_VisitID]=" & Me![Call_VisitID]
rst.Open strSQL

With rst
strAttendees = Nz(.Fields("Attend_FNm")) & " "

&
Nz

(.Fields("Attend_LNm")) & ", " _
& Nz(.Fields("Attend_Title")) & "; "
.Close
End With

Here's the code to "insert" the data into the bmkAttendees
bookmark in Word::

With objWord
.Visible = True
Set doc = .Documents.Add("\\Data1\public data\DEPT
SHARES\RQP Sales\Sales Trip Report.dot")
With doc.Bookmarks
.Item("bmkSalesPerson").Range.Text =
strSalesPerson
.Item("bmkCustomer").Range.Text = strCustomer
.Item("bmkRept_Date").Range.Text = dtmRept_Date
.Item("bmkAttendees").Range.Text = strAttendees

End With
End With

Any suggestions would be greatly appreciated.

.