Iteration problem with Do Until rstOutput.EOF

When doing a mail merge with Word, I can't seem to get the below
(abbreviated) routine to iterate strBookmark01 -- it just uses the first
name in the recordset for all 10 (or whatever) documents...

Is there some other code I need to get the string to iterate with each name?
Thanks in advance...

Set rstOutput = db.OpenRecordset("qryMailMerge")
Do Until rstOutput.EOF
strBookmark01 = rstData!Name
With objWord
.Selection.GoTo what:=wdGoToBookmark, Name="Bookmark01"
.Slection.TypeText Text:=strBookmark01
End With

re: Iteration problem with Do Until rstOutput.EOF

"deko" <dje422@hotmail.com> wrote:
[color=blue]
> it just uses the first
> name in the recordset for all 10 (or whatever) documents...
>
> Is there some other code I need to get the string to iterate with each
> name?
>[/color]

I'm no expert, but I'm surprised you're getting anything because you've
opened the recordset but not set the pointer to any record. I think you
need to use MoveFirst and MoveNext statements as appropriate.

Regards,
Keith.

re: Iteration problem with Do Until rstOutput.EOF

On 3 Dec 2003 13:51:56 GMT, Keith Wilby
<keith.wilby@AwayWithYerCrap.com> wrote:

MoveFirst is not needed. A new recordset is either at EOF if no rows,
or at the first record.
But the fellow needs MoveNext, as well as the Loop instruction at the
bottom of the loop. The code as presented will not compile.

-Tom.


[color=blue]
>"deko" <dje422@hotmail.com> wrote:
>[color=green]
>> it just uses the first
>> name in the recordset for all 10 (or whatever) documents...
>>
>> Is there some other code I need to get the string to iterate with each
>> name?
>>[/color]
>
>I'm no expert, but I'm surprised you're getting anything because you've
>opened the recordset but not set the pointer to any record. I think you
>need to use MoveFirst and MoveNext statements as appropriate.
>
>Regards,
>Keith.[/color]

re: Iteration problem with Do Until rstOutput.EOF

Tom van Stiphout <tom7744@no.spam.cox.net> wrote:
[color=blue]
> MoveFirst is not needed. A new recordset is either at EOF if no rows,
> or at the first record.
>[/color]

Thanks Tom, duly noted :-)

re: Iteration problem with Do Until rstOutput.EOF

Thanks for the tip... I am reviewing the code now and will re-post revision
and results...

"Tom van Stiphout" <tom7744@no.spam.cox.net> wrote in message
news:e7srsvgqgomut9hqntbbld5d28j26018q2@4ax.com...[color=blue]
> On 3 Dec 2003 13:51:56 GMT, Keith Wilby
> <keith.wilby@AwayWithYerCrap.com> wrote:
>
> MoveFirst is not needed. A new recordset is either at EOF if no rows,
> or at the first record.
> But the fellow needs MoveNext, as well as the Loop instruction at the
> bottom of the loop. The code as presented will not compile.
>
> -Tom.
>
>
>[color=green]
> >"deko" <dje422@hotmail.com> wrote:
> >[color=darkred]
> >> it just uses the first
> >> name in the recordset for all 10 (or whatever) documents...
> >>
> >> Is there some other code I need to get the string to iterate with each
> >> name?
> >>[/color]
> >
> >I'm no expert, but I'm surprised you're getting anything because you've
> >opened the recordset but not set the pointer to any record. I think you
> >need to use MoveFirst and MoveNext statements as appropriate.
> >
> >Regards,
> >Keith.[/color]
>
>[/color]

re: Iteration problem with Do Until rstOutput.EOF

tom7744@no.spam.cox.net (Tom van Stiphout) wrote in
<e7srsvgqgomut9hqntbbld5d28j26018q2@4ax.com>:
[color=blue]
>On 3 Dec 2003 13:51:56 GMT, Keith Wilby
><keith.wilby@AwayWithYerCrap.com> wrote:
>
>MoveFirst is not needed. A new recordset is either at EOF if no
>rows, or at the first record.
>But the fellow needs MoveNext, as well as the Loop instruction at
>the bottom of the loop. The code as presented will not compile.[/color]

MoveFirst may be unnecessary, but I always explicitly include it,
anyway, just so it's clear where we're starting from.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

re: Iteration problem with Do Until rstOutput.EOF

Hi and thanks for the reply.

I do have a Move.Next on line 84
The problem is on line 43 and following - the string variables get stuck on
the first value in the recordset. For example, if there are 10 records in
the recordset, Word opens 10 documents, all addressed to the same person.

The purpose of this code is to allow the user to select a set of records for
use in a mail merge (see line 21 and following).
Also, a predefiend series of Bookmark "handles" (lines 3 - 9) allow the user
to create custom Word Templates using Bookmarks named after these handles.

Another problem I'm having is with the Do Loop (line 41). I'm not sure what
recordset to use here...

again, thanks for your comments!

1 Public Function WordDoc()
2 'predefined merge fields
3 Dim strReturnAddress As String
4 Dim strFirstName
5 Dim strLastName
6 Dim strFullName As String
7 Dim strSalutation As String
8 Dim strNameAddressBlock As String
9 Dim strAddressOnly As String
10
11 Dim rsEntity As DAO.Recordset
12 Dim rsAddress As DAO.Recordset
13 Dim rsReturnAddress As DAO.Recordset
14 Dim rsOutput As DAO.Recordset
15 Dim rst As DAO.Recordset
16 Dim objWord As Word.Application
17 Dim db As DAO.Database
18 'open word (code omitted)
19
20 Set db = CurrentDb
21 strRsDef = DLookup("RecSel", "tblOutput")
22 Select Case strRsDef
23 Case 0
24 strQry = "tblEntity"
25 Set rsEntity = db.OpenRecordset("SELECT ... code omitted)
26 Set rsAddress = db.OpenRecordset("SELECT ... code omitted)
27 Case 1
28 strQry = DLookup("SearchQry", "tblOutput")
29 Set rsEntity = db.OpenRecordset("SELECT ... code omitted)
30 Set rsAddress = db.OpenRecordset("SELECT ... code omitted)
31 Case 2
32 strQry = "qryMergeCat"
33 Set rsEntity = db.OpenRecordset("qryMergeCat")
34 Set rsAddress = db.OpenRecordset("qryMergeCat")
35 Case Else
36 Exit Function
37 End Select
38 Set rsReturnAddress = db.OpenRecordset("qryRtAdd")
39 Set rsOutput = db.OpenRecordset(strQry) ' >>>>> not sure about
this...
40
41 Do Until rsOutput.EOF '>>>> should this be Do Until rsEntity.EOF ??
42
43 '>>>>>> value of strNameAddressBlock (and other strings) not iterating
through recordset
44 strNameAddressBlock = IIf(IsNull(rsEntity!Prefix), "",
rsEntity!Prefix & " ") & _
45 IIf(IsNull(rsEntity!FirstName), "", rsEntity!FirstName) &
_
46 IIf(IsNull(rsEntity!MiddleName), "", " " &
rsEntity!MiddleName) & _
47 " " & rsEntity!LastName & IIf(IsNull(rsEntity!Suffix), "",
", " & rsEntity!Suffix) & _
48 IIf(IsNull(rsEntity!Company), "", Chr(13) & Chr(10) &
Chr(9) & Chr(9) & rsEntity!Company) & _
49 IIf(IsNull(rsAddress!Address1), "", Chr(13) & Chr(10) &
Chr(9) & Chr(9) & rsAddress!Address1) & _
50 IIf(IsNull(rsAddress!Address2), "", Chr(13) & Chr(10) &
Chr(9) & Chr(9) & rsAddress!Address2) & _
51 IIf(IsNull(rsAddress!Address3), "", Chr(13) & Chr(10) &
Chr(9) & Chr(9)) & rsAddress!Address3 & _
52 IIf(IsNull(rsAddress!City), "", Chr(13) & Chr(10) & Chr(9)
& Chr(9) & rsAddress!City) & _
53 IIf(IsNull(rsAddress!State), "", ", " & rsAddress!State) &
_
54 IIf(IsNull(rsAddress!Zipcode), "", " " &
rsAddress!Zipcode)
55
56 strReturnAddress = (code omitted)
57 strFirstName = (code omitted)
58 strLastName = (code omitted)
59 strFullName = (code omitted)
60 strSalutation = (code omitted)
61 strAddressOnly = (code omitted)
62
63 With objWord
64 On Error Resume Next 'skip over bookmarks that don't exist in
selected template
65 .Documents.Add Template:=strTemplate, NewTemplate:=False,
DocumentType:=0
66 .Selection.GoTo what:=wdGoToBookmark, Name:="NameAddressBlock"
67 .Selection.TypeText Text:=strNameAddressBlock
68 .Selection.GoTo what:=wdGoToBookmark, Name:="ReturnAddress"
69 .Selection.TypeText Text:=strReturnAddress
70 .Selection.GoTo what:=wdGoToBookmark, Name:="FirstName"
71 .Selection.TypeText Text:=strFirstName
72 .Selection.GoTo what:=wdGoToBookmark, Name:="LastName"
73 .Selection.TypeText Text:=strLastName
74 .Selection.GoTo what:=wdGoToBookmark, Name:="FullName"
75 .Selection.TypeText Text:=strFullName
76 .Selection.GoTo what:=wdGoToBookmark, Name:="Salutation"
77 .Selection.TypeText Text:=strSalutation
78 .Selection.GoTo what:=wdGoToBookmark, Name:="AddressOnly"
79 .Selection.TypeText Text:=strAddressOnly
80 .Application.NormalTemplate.Saved = True
81 End With
82 On Error GoTo 0
83 If strRsDef = "0" Then GoTo Finish
84 rsOutput.MoveNext
85 Loop
86 Finish: 'close objects (code omitted)
87 End Function



"Tom van Stiphout" <tom7744@no.spam.cox.net> wrote in message
news:e7srsvgqgomut9hqntbbld5d28j26018q2@4ax.com...[color=blue]
> On 3 Dec 2003 13:51:56 GMT, Keith Wilby
> <keith.wilby@AwayWithYerCrap.com> wrote:
>
> MoveFirst is not needed. A new recordset is either at EOF if no rows,
> or at the first record.
> But the fellow needs MoveNext, as well as the Loop instruction at the
> bottom of the loop. The code as presented will not compile.
>
> -Tom.
>
>
>[color=green]
> >"deko" <dje422@hotmail.com> wrote:
> >[color=darkred]
> >> it just uses the first
> >> name in the recordset for all 10 (or whatever) documents...
> >>
> >> Is there some other code I need to get the string to iterate with each
> >> name?
> >>[/color]
> >
> >I'm no expert, but I'm surprised you're getting anything because you've
> >opened the recordset but not set the pointer to any record. I think you
> >need to use MoveFirst and MoveNext statements as appropriate.
> >
> >Regards,
> >Keith.[/color]
>
>[/color]

re: Iteration problem with Do Until rstOutput.EOF

On Wed, 03 Dec 2003 20:07:58 GMT, dXXXfenton@bway.net.invalid (David
W. Fenton) wrote:

I'll allow it :-), but be sure to first test for a non-empty
recordset. Otherwise MoveFirst will fail.
-Tom.

[color=blue]
>tom7744@no.spam.cox.net (Tom van Stiphout) wrote in
><e7srsvgqgomut9hqntbbld5d28j26018q2@4ax.com>:
>[color=green]
>>On 3 Dec 2003 13:51:56 GMT, Keith Wilby
>><keith.wilby@AwayWithYerCrap.com> wrote:
>>
>>MoveFirst is not needed. A new recordset is either at EOF if no
>>rows, or at the first record.
>>But the fellow needs MoveNext, as well as the Loop instruction at
>>the bottom of the loop. The code as presented will not compile.[/color]
>
>MoveFirst may be unnecessary, but I always explicitly include it,
>anyway, just so it's clear where we're starting from.[/color]

re: Iteration problem with Do Until rstOutput.EOF

On Wed, 03 Dec 2003 20:07:58 GMT in comp.databases.ms-access,
dXXXfenton@bway.net.invalid (David W. Fenton) wrote:
[color=blue]
>tom7744@no.spam.cox.net (Tom van Stiphout) wrote in
><e7srsvgqgomut9hqntbbld5d28j26018q2@4ax.com>:
>[color=green]
>>On 3 Dec 2003 13:51:56 GMT, Keith Wilby
>><keith.wilby@AwayWithYerCrap.com> wrote:
>>
>>MoveFirst is not needed. A new recordset is either at EOF if no
>>rows, or at the first record.
>>But the fellow needs MoveNext, as well as the Loop instruction at
>>the bottom of the loop. The code as presented will not compile.[/color]
>
>MoveFirst may be unnecessary, but I always explicitly include it,
>anyway, just so it's clear where we're starting from.[/color]

I had a situation recently where the client has requested something
unatrual for a database to do, in an MTO or requisition they want a
start and end item number so they enter start item 1, end item 20, qty
20. This will later be split down upon reciept and the individual item
given a UID, the last part being nothing I've not done before of
course it's the first part is peculiar to the way they work.

No if they enter item 1 to 20, you can't have say item 11 elsewhere,
etc, in the beforeupdate I created a recordsetclone and looped
through, I always had put .MoveFirst in the past but this time I
didn't (laziness I guess) and it would never trap the first attempt
after opening the form, upon debugging I found it looped just one
record so it had started at the last record.

I'll not be making that mistake again.

--
A)bort, R)etry, I)nfluence with large hammer.

re: Iteration problem with Do Until rstOutput.EOF

tom7744@no.spam.cox.net (Tom van Stiphout) wrote in
<gv8tsvct9oq4t5vvljkf39t3k2ggdbgk0i@4ax.com>:
[color=blue]
>On Wed, 03 Dec 2003 20:07:58 GMT, dXXXfenton@bway.net.invalid
>(David W. Fenton) wrote:
>
>I'll allow it :-), but be sure to first test for a non-empty
>recordset. Otherwise MoveFirst will fail.[/color]

Well, I assumed that in my answer.

I never check for anything but .RecordCount <> 0 (assuming I'm
going to move through the recordset).

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc