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