get data from Access

[carl]

I have a macro that needs a value from field in a MSAccess97 database.
The marco needs to run until EOF. I am using MSWord97.
This is what I have:

With ActiveDocument.MailMerge
.OpenDataSource Name:=strPath & strDatabase, _
Connection:="Query qryEVM"
.DataSource.QueryString = strSQL
Do Until EOF 'run macro until end of records
strProjectNo = .DataSource.DataFields("Project_No").Value
Call DbMergeSummaries(strProjectNo)
'how to move next record???
Loop
End With

 

[Peter Jamieson]

The records in the data source don't have all the "file-like" methods and
properties you might expect, and the ones that do exist may not work exactly
the way you might hope, either. I have suggested code along the following
lines in the past - it could probably be better structured, but some of the
code is there on the assumption that DbMergeSummaries is actually doing a
merge on the current document (in which case Word may change the value of
ActiveRecord).

Dim iSourceRecord As Long
Dim bEndOfData As Boolean
With ActiveDocument.MailMerge
.OpenDataSource Name:=strPath & strDatabase, _
Connection:="Query qryEVM"
.DataSource.QueryString = strSQL
iSourceRecord = 1
bEndOfData = False
Do Until bEndOfData
.DataSource.ActiveRecord = iSourceRecord

' if we have gone past the end (and possibly, if there are no records)
' then the Activerecord will not be what we have just tried to set it to

If .DataSource.ActiveRecord <> iSourceRecord Then
bEndOfData = True
Else
strProjectNo = .DataSource.DataFields("Project_No").Value
.DataSource.FirstRecord = iSourceRecord
.DataSource.LastRecord = iSourceRecord
Call DbMergeSummaries(strProjectNo)
iSourceRecord = iSourceRecord + 1
End If
Loop

Peter Jamieson

 

[Peter Jamieson]

In addition to my other post, I meant to say that you could always consider
using something like ADO (maybe it needs to be DAO with Access 97) to
process a controlling loop, but only if your OpenDataSource query is
retrieving all the data you actually need for the merge - otherwise, the
only way to get data from DAO into your document(s) is to stuff it in
yourself.

Peter Jamieson