Reply
 
Thread Tools Display Modes
  #1   Report Post  
carl
 
Posts: n/a
Default get data from Access

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



  #2   Report Post  
Peter Jamieson
 
Posts: n/a
Default

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

"carl" wrote in message
...
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





  #3   Report Post  
Peter Jamieson
 
Posts: n/a
Default

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
"carl" wrote in message
...
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





Reply
Thread Tools
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
formatting numeric data from Access merged into a Word table LacieMoon Mailmerge 11 December 5th 06 11:12 PM
ODBC Iseries access - Record 1 contains too few data Renzo_F Mailmerge 3 June 1st 05 03:04 PM
MailMerge Word 2003 to Access 2000 query blank data BCool Mailmerge 1 January 15th 05 01:31 AM
Mailmerge data source in an Access database will not open Colin C Mailmerge 3 December 31st 04 01:01 AM
Using as secured Access database as the data source for a mail mer Carmine Mailmerge 1 December 10th 04 06:25 PM


All times are GMT +1. The time now is 02:23 PM.

Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Word"