Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Send Query results to Create Word Letter

Status
Not open for further replies.

rccline

Technical User
Jun 13, 2002
341
US

In caps, below, following the comment marker, I have set forth the problems encounted trying to run this VB code as it applies to my problem.

THE OBJECT IS TO PRINT A LETTER, BASED UPON A WORDTEMPLATE WITH MULTIPLE FIELDS FOR EACH CONTACT IN THE QUERY.

REF: Microsoft Access 2000 Visual Basic for Applications. p.299 (cited below as A2K)





Code:
Option Compare Database

Code:
Option Explicit

Code:
Public Function CreateWordLetter()
'Open a Letter Template in Word and instert text - used by Menu command.

Code:
Dim rstContacts As New ADODB.Recordset
'Open a recordset based on the QryContacts query.
rstContacts.Open "QryContacts", _
    CurrentProject.Connection, adOpenKeyset, adLockOptimistic

'FIRST QUESTION: A2K DOES NOT DEFINE ADLOCKOPTIMISIC
'WHAT DOES IT REFER TO?



Code:
Dim appWord As New Word.Application
    'The object variable (appWord) refers to Word and its commands,
    'Word.Application is available becaure re established a ref to
      ' Word's object library (tools/references).
    'code is added just before EndFunction
    
    'If no contacts are returned in Query, display a message and exit.
    If rstContacts.RecordCount = 0 Then
        MsgBox "No Contacts match Query."
        End If
        
    'Open a dOcument based on the word template, turn off spell check
    'move to the ContactToLine bookmark, and display work.

    With appWord
        .Documents.Add "C:\Letter - RCC_db.dot"
        .ActiveDocument.ShowSpellingErrors = False
        .Selection.GoTo wdGoToBookmark, Name:="LetterToLine"
        .Visible = True
        End With
        
        'Loop through the recordset returned by the query, inserting
        'the name of each contact into the document
        Do Until rstContacts.EOF
            appWord.Selection.TypeText rstContacts![FirstName] & ", "
            appWord.Selection.TypeText rstContacts![LastName] & ";   "
            appWord.Selection.TypeText rstContacts![Address] & " "
            appWord.Selection.TypeText rstContacts![City] & ", "
            appWord.Selection.TypeText rstContacts![StateOrProvince]
            rstContacts.MoveNext
        Loop
    
End Function

'QUESTION TWO: (A)HOW DO I SET THE RECORDCOUNT TO COUNT A NULL SET? AND (B) WHAT IS THE BEST WAY TO PRINT THE DIFFERENT FIELDS WITHIN THE QUERY?THE FUNCTION BREAKS DOWN AT THE LOOP. THERE IS PROBALY A BETTER WAY TO CALL THE VARIOUS FIELDS. THIS IS THE ONLY WAY I COULD FIGURE OUT HOW TO CALL THE VARIOUS FIELDS. ALL WOULD PRINT EXCEPT FOR [STATEORPROVINCE]. IT HAD A NULL SET. I THINK THIS STOPPED THE RECORDCOUNTER. I DO NOT KNOW HOW TO SET THE COUNTER NZ.

'THIRD, ALL THE RECORDS RETURNED BY THE QUERY PRINT ON ONE WORD.DOC HOW DO I SEND THE QUERY RESULTS TO WORD.DOC SUCH THAT A NEW LETTER IS CREATED FOR EACH RECORD IN THE QUERY? THE OBJECT IS TO PRINT A LETTER, BASED UPON A WORDTEMPLATE WITH MULTIPLE FIELDS FOR EACH CONTACT IN THE QUERY.

THANK YOU.

ROBERT

 
I just discovered why I was getting a Null error.

The last line of code:
appWord.Selection.TypeText rstContacts![StateOrProvince]needed to have: & " "

No clue why.

As originally stated, the question asked how to create a new document for each record. A better solution would be to create a new section with page break for each record; as in a MailMerge document. Search for MailMerge on this forum returned no records.

How do I create a Merge of fields with my wordtemplate from my query?

Thanks,

robert


 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top