Iteration problem with Do Until rstOutput.EOF

[deko]

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.OpenRecordse t("qryMailMerge ")
Do Until rstOutput.EOF
strBookmark01 = rstData!Name
With objWord
.Selection.GoTo what:=wdGoToBoo kmark, Name="Bookmark0 1"
.Slection.TypeT ext Text:=strBookma rk01
End With

[Keith Wilby]

"deko" <dj****@hotmail .com> wrote:

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?

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.

[Tom van Stiphout]

On 3 Dec 2003 13:51:56 GMT, Keith Wilby
<ke*********@Aw ayWithYerCrap.c om> 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.

"deko" <dj****@hotmail .com> wrote:

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?

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.

[Keith Wilby]

Tom van Stiphout <to*****@no.spa m.cox.net> wrote:

MoveFirst is not needed. A new recordset is either at EOF if no rows,
or at the first record.

Thanks Tom, duly noted :-)

[deko]

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

"Tom van Stiphout" <to*****@no.spa m.cox.net> wrote in message
news:e7******** *************** *********@4ax.c om...

On 3 Dec 2003 13:51:56 GMT, Keith Wilby
<ke*********@Aw ayWithYerCrap.c om> 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.

"deko" <dj****@hotmail .com> wrote:

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?

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.

[David W. Fenton]

to*****@no.spam .cox.net (Tom van Stiphout) wrote in
<e7************ *************** *****@4ax.com>:

On 3 Dec 2003 13:51:56 GMT, Keith Wilby
<ke*********@A wayWithYerCrap. 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.

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

[deko]

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 strReturnAddres s As String
4 Dim strFirstName
5 Dim strLastName
6 Dim strFullName As String
7 Dim strSalutation As String
8 Dim strNameAddressB lock 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.Applicatio n
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.OpenRecordse t("SELECT ... code omitted)
26 Set rsAddress = db.OpenRecordse t("SELECT ... code omitted)
27 Case 1
28 strQry = DLookup("Search Qry", "tblOutput" )
29 Set rsEntity = db.OpenRecordse t("SELECT ... code omitted)
30 Set rsAddress = db.OpenRecordse t("SELECT ... code omitted)
31 Case 2
32 strQry = "qryMergeCa t"
33 Set rsEntity = db.OpenRecordse t("qryMergeCat" )
34 Set rsAddress = db.OpenRecordse t("qryMergeCat" )
35 Case Else
36 Exit Function
37 End Select
38 Set rsReturnAddress = db.OpenRecordse t("qryRtAdd")
39 Set rsOutput = db.OpenRecordse t(strQry) ' >>>>> not sure about
this...
40
41 Do Until rsOutput.EOF '>>>> should this be Do Until rsEntity.EOF ??
42
43 '>>>>>> value of strNameAddressB lock (and other strings) not iterating
through recordset
44 strNameAddressB lock = IIf(IsNull(rsEn tity!Prefix), "",
rsEntity!Prefix & " ") & _
45 IIf(IsNull(rsEn tity!FirstName) , "", rsEntity!FirstN ame) &
_
46 IIf(IsNull(rsEn tity!MiddleName ), "", " " &
rsEntity!Middle Name) & _
47 " " & rsEntity!LastNa me & IIf(IsNull(rsEn tity!Suffix), "",
", " & rsEntity!Suffix ) & _
48 IIf(IsNull(rsEn tity!Company), "", Chr(13) & Chr(10) &
Chr(9) & Chr(9) & rsEntity!Compan y) & _
49 IIf(IsNull(rsAd dress!Address1) , "", Chr(13) & Chr(10) &
Chr(9) & Chr(9) & rsAddress!Addre ss1) & _
50 IIf(IsNull(rsAd dress!Address2) , "", Chr(13) & Chr(10) &
Chr(9) & Chr(9) & rsAddress!Addre ss2) & _
51 IIf(IsNull(rsAd dress!Address3) , "", Chr(13) & Chr(10) &
Chr(9) & Chr(9)) & rsAddress!Addre ss3 & _
52 IIf(IsNull(rsAd dress!City), "", Chr(13) & Chr(10) & Chr(9)
& Chr(9) & rsAddress!City) & _
53 IIf(IsNull(rsAd dress!State), "", ", " & rsAddress!State ) &
_
54 IIf(IsNull(rsAd dress!Zipcode), "", " " &
rsAddress!Zipco de)
55
56 strReturnAddres s = (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:=strTe mplate, NewTemplate:=Fa lse,
DocumentType:=0
66 .Selection.GoTo what:=wdGoToBoo kmark, Name:="NameAddr essBlock"
67 .Selection.Type Text Text:=strNameAd dressBlock
68 .Selection.GoTo what:=wdGoToBoo kmark, Name:="ReturnAd dress"
69 .Selection.Type Text Text:=strReturn Address
70 .Selection.GoTo what:=wdGoToBoo kmark, Name:="FirstNam e"
71 .Selection.Type Text Text:=strFirstN ame
72 .Selection.GoTo what:=wdGoToBoo kmark, Name:="LastName "
73 .Selection.Type Text Text:=strLastNa me
74 .Selection.GoTo what:=wdGoToBoo kmark, Name:="FullName "
75 .Selection.Type Text Text:=strFullNa me
76 .Selection.GoTo what:=wdGoToBoo kmark, Name:="Salutati on"
77 .Selection.Type Text Text:=strSaluta tion
78 .Selection.GoTo what:=wdGoToBoo kmark, Name:="AddressO nly"
79 .Selection.Type Text Text:=strAddres sOnly
80 .Application.No rmalTemplate.Sa ved = True
81 End With
82 On Error GoTo 0
83 If strRsDef = "0" Then GoTo Finish
84 rsOutput.MoveNe xt
85 Loop
86 Finish: 'close objects (code omitted)
87 End Function

"Tom van Stiphout" <to*****@no.spa m.cox.net> wrote in message
news:e7******** *************** *********@4ax.c om...

On 3 Dec 2003 13:51:56 GMT, Keith Wilby
<ke*********@Aw ayWithYerCrap.c om> 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.

"deko" <dj****@hotmail .com> wrote:

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?

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.

[Tom van Stiphout]

On Wed, 03 Dec 2003 20:07:58 GMT, dX********@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.

to*****@no.spa m.cox.net (Tom van Stiphout) wrote in
<e7*********** *************** ******@4ax.com> :

On 3 Dec 2003 13:51:56 GMT, Keith Wilby
<ke*********@ 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.

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

[Trevor Best]

On Wed, 03 Dec 2003 20:07:58 GMT in comp.databases. ms-access,
dX********@bway .net.invalid (David W. Fenton) wrote:

to*****@no.spa m.cox.net (Tom van Stiphout) wrote in
<e7*********** *************** ******@4ax.com> :

On 3 Dec 2003 13:51:56 GMT, Keith Wilby
<ke*********@ 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.

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

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.

[David W. Fenton]

to*****@no.spam .cox.net (Tom van Stiphout) wrote in
<gv************ *************** *****@4ax.com>:

On Wed, 03 Dec 2003 20:07:58 GMT, dX********@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.

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