Transfer multiple records to Word bookmark

J

Jody

I have a Word template that is set up to receive Access
data. It's working fine except that I would like to put
multiple records into one field (bmkAttendees) in the Word
document. Each record will be separated by a semicolon.
So far the program only inserts the first record and
stops. How do I have it continue inserting all the
records based on the select statement? Here's my code:

Private Sub cmdPrint_Click()
Dim objWord As Word.Application
Dim doc As Word.Document
Dim rst As New ADODB.Recordset
Dim strSQL As String
Dim strSalesPerson As String
Dim strCustomer As String
Dim dtmRept_Date As Date
Dim strItem As String
Dim strItem_Owner As String
Dim strAttendees As String

strSQL = "SELECT * FROM CallVisit " _
& "WHERE [Call_VisitID] = " & Me![Call_VisitID]
rst.Open strSQL, CurrentProject.Connection,
adOpenStatic, adLockReadOnly

With rst
strCustomer = Nz(.Fields("Customer")) & " " _
& Nz(.Fields("CustLoc")) & " " & Nz(.Fields
("Call_Date"))
strSalesPerson = Nz(.Fields("SalesPerson"))
dtmRept_Date = Nz(.Fields("Rept_Date"))
.Close
End With

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

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

On Error GoTo 0

With objWord
.Visible = True
Set doc = .Documents.Add("\\Data1\public data\DEPT
SHARES\RQP Sales\Travel\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

strSQL = "SELECT Item, Item_Owner FROM CallDetails " &
_
"WHERE [Call_VisitID]=" & Me![Call_VisitID]
rst.Open strSQL
With rst
doc.Bookmarks("bmkCallDetail").Select
Do Until .EOF
strItem = Nz(.Fields("Item"))
strItem_Owner = Nz(.Fields("Item_Owner"))
With objWord.Selection
.TypeText strItem
.MoveRight wdCell
.TypeText strItem_Owner
.MoveRight wdCell
.MoveRight wdCell
.MoveRight wdCell
End With
.MoveNext
Loop
End With

objWord.Activate

Set rst = Nothing
Set doc = Nothing
Set objWord = Nothing

End Sub
 
K

Kevin

Jody,

Have you tried stepping through the code? Put a break in
and then initiate the code. When it gets to the break, use
the F8 key and step through one line at a time. You can
watch variables by either putting a watch in, putting a
debug statement (debug.print myvariable) or by just
putting the pointer over the variable. I have found the
source of qwuite a number of problems by doing this. It
may be a variable does not get assigned a value (remains
null), gets the wrong value, there may be a syntax error
which you have not yet detected. There are a lot of
different reasons why a particular problem might arise
that become obvious when you step through it like this.

I sent you an E-Mail last night with the duplicate
function working. I have not yet got the conflict catcher
working yet though, but will keep working on that. I just
need top take the time to search help and to read a couple
of resources to find the answer to the problem.

Kevin
-----Original Message-----
I have a Word template that is set up to receive Access
data. It's working fine except that I would like to put
multiple records into one field (bmkAttendees) in the Word
document. Each record will be separated by a semicolon.
So far the program only inserts the first record and
stops. How do I have it continue inserting all the
records based on the select statement? Here's my code:

Private Sub cmdPrint_Click()
Dim objWord As Word.Application
Dim doc As Word.Document
Dim rst As New ADODB.Recordset
Dim strSQL As String
Dim strSalesPerson As String
Dim strCustomer As String
Dim dtmRept_Date As Date
Dim strItem As String
Dim strItem_Owner As String
Dim strAttendees As String

strSQL = "SELECT * FROM CallVisit " _
& "WHERE [Call_VisitID] = " & Me![Call_VisitID]
rst.Open strSQL, CurrentProject.Connection,
adOpenStatic, adLockReadOnly

With rst
strCustomer = Nz(.Fields("Customer")) & " " _
& Nz(.Fields("CustLoc")) & " " & Nz(.Fields
("Call_Date"))
strSalesPerson = Nz(.Fields("SalesPerson"))
dtmRept_Date = Nz(.Fields("Rept_Date"))
.Close
End With

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

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

On Error GoTo 0

With objWord
.Visible = True
Set doc = .Documents.Add("\\Data1\public data\DEPT
SHARES\RQP Sales\Travel\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

strSQL = "SELECT Item, Item_Owner FROM CallDetails " &
_
"WHERE [Call_VisitID]=" & Me![Call_VisitID]
rst.Open strSQL
With rst
doc.Bookmarks("bmkCallDetail").Select
Do Until .EOF
strItem = Nz(.Fields("Item"))
strItem_Owner = Nz(.Fields("Item_Owner"))
With objWord.Selection
.TypeText strItem
.MoveRight wdCell
.TypeText strItem_Owner
.MoveRight wdCell
.MoveRight wdCell
.MoveRight wdCell
End With
.MoveNext
Loop
End With

objWord.Activate

Set rst = Nothing
Set doc = Nothing
Set objWord = Nothing

End Sub



.
Click to expand...
 
J

Jody

Thanks for the help on the duplicate function. I'll look
for it when I get home today.

My Sales Call Report Word template works well. The only
thing ... it's not doing is inserting ALL the Attendees
names in the bmkAttendees field on my Word template. This
is where I'm selected all the names from my table
CallAttendees and separating each one with a semicolon.
When I process my code, the Word template shows only the
first Attendee name followed by a semicolon (then bypasses
the other four Attendee names) and continues on with the
rest of the document.

I took this code from an example I found in a publication
I subscribe to. I'm surprised I was able to get it to
work -- except for this one thing. I thought perhaps
there was some extra code I need to insert so that ALL the
Attendee names are included in the bmkAttendees field.

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

Have you tried stepping through the code? Put a break in
and then initiate the code. When it gets to the break, use
the F8 key and step through one line at a time. You can
watch variables by either putting a watch in, putting a
debug statement (debug.print myvariable) or by just
putting the pointer over the variable. I have found the
source of qwuite a number of problems by doing this. It
may be a variable does not get assigned a value (remains
null), gets the wrong value, there may be a syntax error
which you have not yet detected. There are a lot of
different reasons why a particular problem might arise
that become obvious when you step through it like this.

I sent you an E-Mail last night with the duplicate
function working. I have not yet got the conflict catcher
working yet though, but will keep working on that. I just
need top take the time to search help and to read a couple
of resources to find the answer to the problem.

Kevin
-----Original Message-----
I have a Word template that is set up to receive Access
data. It's working fine except that I would like to put
multiple records into one field (bmkAttendees) in the Word
document. Each record will be separated by a semicolon.
So far the program only inserts the first record and
stops. How do I have it continue inserting all the
records based on the select statement? Here's my code:

Private Sub cmdPrint_Click()
Dim objWord As Word.Application
Dim doc As Word.Document
Dim rst As New ADODB.Recordset
Dim strSQL As String
Dim strSalesPerson As String
Dim strCustomer As String
Dim dtmRept_Date As Date
Dim strItem As String
Dim strItem_Owner As String
Dim strAttendees As String

strSQL = "SELECT * FROM CallVisit " _
& "WHERE [Call_VisitID] = " & Me![Call_VisitID]
rst.Open strSQL, CurrentProject.Connection,
adOpenStatic, adLockReadOnly

With rst
strCustomer = Nz(.Fields("Customer")) & " " _
& Nz(.Fields("CustLoc")) & " " & Nz(.Fields
("Call_Date"))
strSalesPerson = Nz(.Fields("SalesPerson"))
dtmRept_Date = Nz(.Fields("Rept_Date"))
.Close
End With

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

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

On Error GoTo 0

With objWord
.Visible = True
Set doc = .Documents.Add("\\Data1\public data\DEPT
SHARES\RQP Sales\Travel\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

strSQL = "SELECT Item, Item_Owner FROM CallDetails " &
_
"WHERE [Call_VisitID]=" & Me![Call_VisitID]
rst.Open strSQL
With rst
doc.Bookmarks("bmkCallDetail").Select
Do Until .EOF
strItem = Nz(.Fields("Item"))
strItem_Owner = Nz(.Fields("Item_Owner"))
With objWord.Selection
.TypeText strItem
.MoveRight wdCell
.TypeText strItem_Owner
.MoveRight wdCell
.MoveRight wdCell
.MoveRight wdCell
End With
.MoveNext
Loop
End With

objWord.Activate

Set rst = Nothing
Set doc = Nothing
Set objWord = Nothing

End Sub



.
Click to expand...
.
Click to expand...
 
K

Kevin

How are you inserting the names. Is this a recordset
query? If so, I expect you need to loop through the
recordset and insert each name individually. Look in help
for looping through a recordset.

Hope that helps!

Kevin
-----Original Message-----
Thanks for the help on the duplicate function. I'll look
for it when I get home today.

My Sales Call Report Word template works well. The only
thing ... it's not doing is inserting ALL the Attendees
names in the bmkAttendees field on my Word template. This
is where I'm selected all the names from my table
CallAttendees and separating each one with a semicolon.
When I process my code, the Word template shows only the
first Attendee name followed by a semicolon (then bypasses
the other four Attendee names) and continues on with the
rest of the document.

I took this code from an example I found in a publication
I subscribe to. I'm surprised I was able to get it to
work -- except for this one thing. I thought perhaps
there was some extra code I need to insert so that ALL the
Attendee names are included in the bmkAttendees field.

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

Have you tried stepping through the code? Put a break in
and then initiate the code. When it gets to the break, use
the F8 key and step through one line at a time. You can
watch variables by either putting a watch in, putting a
debug statement (debug.print myvariable) or by just
putting the pointer over the variable. I have found the
source of qwuite a number of problems by doing this. It
may be a variable does not get assigned a value (remains
null), gets the wrong value, there may be a syntax error
which you have not yet detected. There are a lot of
different reasons why a particular problem might arise
that become obvious when you step through it like this.

I sent you an E-Mail last night with the duplicate
function working. I have not yet got the conflict catcher
working yet though, but will keep working on that. I just
need top take the time to search help and to read a couple
of resources to find the answer to the problem.

Kevin
-----Original Message-----
I have a Word template that is set up to receive Access
data. It's working fine except that I would like to put
multiple records into one field (bmkAttendees) in the Word
document. Each record will be separated by a semicolon.
So far the program only inserts the first record and
stops. How do I have it continue inserting all the
records based on the select statement? Here's my code:

Private Sub cmdPrint_Click()
Dim objWord As Word.Application
Dim doc As Word.Document
Dim rst As New ADODB.Recordset
Dim strSQL As String
Dim strSalesPerson As String
Dim strCustomer As String
Dim dtmRept_Date As Date
Dim strItem As String
Dim strItem_Owner As String
Dim strAttendees As String

strSQL = "SELECT * FROM CallVisit " _
& "WHERE [Call_VisitID] = " & Me![Call_VisitID]
rst.Open strSQL, CurrentProject.Connection,
adOpenStatic, adLockReadOnly

With rst
strCustomer = Nz(.Fields("Customer")) & " " _
& Nz(.Fields("CustLoc")) & " " & Nz(.Fields
("Call_Date"))
strSalesPerson = Nz(.Fields("SalesPerson"))
dtmRept_Date = Nz(.Fields("Rept_Date"))
.Close
End With

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")) & " "
Click to expand...
&
Nz
(.Fields("Attend_LNm")) & ", " _
& Nz(.Fields("Attend_Title")) & "; "
.Close
End With

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

On Error GoTo 0

With objWord
.Visible = True
Set doc = .Documents.Add("\\Data1\public data\DEPT
SHARES\RQP Sales\Travel\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

strSQL = "SELECT Item, Item_Owner FROM
Click to expand...
CallDetails "
&
_
"WHERE [Call_VisitID]=" & Me![Call_VisitID]
rst.Open strSQL
With rst
doc.Bookmarks("bmkCallDetail").Select
Do Until .EOF
strItem = Nz(.Fields("Item"))
strItem_Owner = Nz(.Fields("Item_Owner"))
With objWord.Selection
.TypeText strItem
.MoveRight wdCell
.TypeText strItem_Owner
.MoveRight wdCell
.MoveRight wdCell
.MoveRight wdCell
End With
.MoveNext
Loop
End With

objWord.Activate

Set rst = Nothing
Set doc = Nothing
Set objWord = Nothing

End Sub



.
Click to expand...
.
Click to expand...
.
Click to expand...
 
J

Jody

Is looping what I really need to do? I'm sort of
concatenating multiple records into one field on my Word
template -- in this case its names of people who attended
a customer meeting. These names are currently stored in a
separate table called CallAttendees. I'm not at all
familiar with the looping procedure and not sure how to
word it or where it should be placed within my code.

Here's the code behind the cmdPrint button that invokes
the Word template and fills it with the current data on
the data entry form:

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

With rst
strCustomer = Nz(.Fields("Customer")) & " " _
& Nz(.Fields("CustLoc")) & " " & Nz(.Fields
("Call_Date"))
strSalesPerson = Nz(.Fields("SalesPerson"))
dtmRept_Date = Nz(.Fields("Rept_Date"))
.Close
End With

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

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

On Error GoTo 0

With objWord
.Visible = True
Set doc = .Documents.Add("\\Data1\public data\DEPT
SHARES\RQP Sales\Travel\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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Inserting multiple Access records into Word bookmark field 3
Word Merge and 4
Mail Merge into MS Word 1
Word export 2
Use VB to add titles to Word Doc 0
Filtered Records as source for appending records to a table 1
Merging Access Data to a Word Document 0
fax a report to multiple people 0

Top