Hi all,

I have a rather complex and outdated mail merge code for my contact management database.  And it's broken. 

For single selection mail merge letters, it works fine.  It pulls the data from multiple forms, fires up word, merges the doc, and then logs the letter.  Done.

But the multiple merge option causes the program to flicker in an eternal loop.  When I unlock the program, and look at the merge data .txt file, it's created the headers, but not supplied the data.  So the problem is in the data loop somehow.

I've tried commenting things out and WriteLn-ing different key points.  But I'm going crosseyed.  Help?

// This is pasted from Inside Sesame 2005-04 & Modified - 2005-06-08 BW

var vFileHandle as Int		   // For writing merge data source file for Word
var vHeader as String		   // Data source file header line
var vData as String		   // Data source file data line
var n as Int		 	   // General purpose counter var
var vSelectedDoc as String 	   // The selected Word doc
var vOneOrAll as String		   // Include record(s) option
var vGoPrint as String		   // Printing flag
var vNames as string		   // Form LE names from vNamesPath text file
var vStartingRec = @ResultSetCurrentPosition()
var vOK as Int 			   // added var for new @Shell to do a DOS Dir, 11 Jan 2005
var vLttrInfo as string		   // letter data for DBLttrLog0 field
var vLttr as string		   // selected document name without the .doc ext

Var vNewNoteNo as String
var vNewNote as String
var vCount as Int
var vNewFileID as Int

var vLEs as String = @StringArrayElementList()  // All LE's on current form

var vDocList as string		// The document list converted to stringarray if nec.

var vDataPath as String  = "C:\Sesame\WordMrg\DataMergeDB.txt"
var vNamesPath as String = "C:\Sesame\WordMrg\LEListDB.txt"
var vDocsPath as String = "C:\Sesame\WordMrg\"
var vDocListPath as String = "C:\Sesame\WordMrg\DocListDB.txt"

// Command Line for Directory command  // this does not need to be modified.

var vDir as string = "dir /b /o:n " + vDocsPath + "*.doc >" + vDocListPath

// Ray ADDED 2010-12-24
// BW Fiddled 2011-01-10

var vStaffLEs as String = "StaffName;StaffTitle;StaffEmail"
var vClientLEs as String = "CltName;CltTel1;CltAdd1;CltAdd2;CltCity;CltProv;CltPCod"

Function AddStaffInfo(vDBStaffID as String) as String
var vVals1 as String
var vReturn as String

	vVals1 = @XLookupSourceList(@FN, vDBStaffID, "Staff Screen!StaffRef", vStaffLEs)
	If vVals1 <> ""
	{
		vReturn = vReturn + @AccessStringArray(vVals1, 1) + "^"
			 + @AccessStringArray(vVals1, 2) + "^"
			 + @AccessStringArray(vVals1, 3) + "^"
	}
	Else
	{
		vReturn = vReturn + "^^^"
	}
	Return(vReturn)

End Function

Function AddClientInfo(vDBClientID as String) as String
var vVals2 as String
var vReturn as String

	vVals2 = @XLookupSourceList(@FN, vDBClientID, "Sales Screen!CltNo", vClientLEs)

	If vVals2 <> ""
	{
		vReturn = vReturn + @AccessStringArray(vVals2, 1) + "^"
			 + @AccessStringArray(vVals2, 2) + "^"
			 + @AccessStringArray(vVals2, 3) + "^"
			 + @AccessStringArray(vVals2, 4) + "^"
			 + @AccessStringArray(vVals2, 5) + "^"
			 + @AccessStringArray(vVals2, 6) + "^"
			 + @AccessStringArray(vVals2, 7) + "^"
	}
	Else
	{
		vReturn = vReturn + "^^^^^^^"
	}
	Return(vReturn)

End Function

SUBROUTINE PrintCurrentRecordOnly()

var n as Int
Var i as Int
Var vLEName as String

vGoPrint = "Yes"

For n = 1 to @CountStringArray(vNames)
	vLEName = @AccessStringArray(vNames,n)
	vHeader = vHeader + vLEName + "^"
	vData  = vData + @(vLEName) + "^"

	If vLEName = "DBColl#" Then
	{
		For i = 1 to @CountStringArray(vStaffLEs)
			vHeader += @AccessStringArray(vStaffLEs, i) + "^"
		Next
		vData = vData + AddStaffInfo(@(vLEName))
	}

	If vLEName = "DBCltNo" Then
	{
		For i = 1 to @CountStringArray(vClientLEs)
			vHeader += @AccessStringArray(vClientLEs, i) + "^"
		Next
		vData = vData + AddClientInfo(@(vLEName))
	}

Next

vData   = @Replace(vData, @Newline(), "  ")

vFileHandle = fileOpen(vDataPath)
fileSeek(vFileHandle, 0)
fileWriteLn(vFileHandle, vHeader + @NewLine() + vData)
fileClose(vFileHandle)

END SUBROUTINE

SUBROUTINE PrintAllRetrievedRecords()

var n as Int
var i as Int
Var vLEName as String

If @Askuser("Merge Print " + vSelectedDoc + " for these " + @ResultSetTotal() + " retrieved records?",
"( If not, click on NO to cancel this task, press F7 for a new search,"," and retrieve the records you DO want to print. )")
Then
{

	vGoPrint = "Yes"

	For n = 1 to @CountStringArray(vNames)
		vHeader += @AccessStringArray(vNames,n) + "^"
	Next

	For n = 1 to @CountStringArray(vStaffLEs)
		vHeader += @AccessStringArray(vStaffLEs,n) + "^"
	Next

	vFileHandle = fileOpen(vDataPath)
	fileSeek(vFileHandle, 0)
	fileWriteLn(vFileHandle, vHeader)

	For n = 1 to @ResultSetTotal()
		ResultSetCurrentPosition(n)

		For i = 1 to @CountStringArray(vNames)
			vLEName = @AccessStringArray(vNames,i)
			vData += @(vLEName) + "^"

			If vLEName = "DBColl#" Then
			{
				For i = 1 to @CountStringArray(vStaffLEs)
					vHeader += @AccessStringArray(vStaffLEs, i) + "^"
				Next
				vData = vData + AddStaffInfo(@(vLEName))
			}

			If vLEName = "DBCltNo" Then
			{
				For i = 1 to @CountStringArray(vClientLEs)
					vHeader += @AccessStringArray(vClientLEs, i) + "^"
				Next
				vData = vData + AddClientInfo(@(vLEName))
			}

		Next

		vData  = @Replace(vData, @Newline(), "  ")
		FileWriteLn(vFileHandle, vData)

		@Msg("Processed Record " + n + " of " + @ResultSetTotal())
		vData = ""

	Next

	fileClose(vFileHandle)

	// Rewind to record where it all started
	ResultSetCurrentPosition(vStartingRec)
}

END SUBROUTINE

/*
// THESE lines are commented out,
//  but can be activated if you need to DEBUG.

WriteLn("vDataPath = " + vDataPath)
WriteLn("vNamesPath = " + vNamesPath)
WriteLn("vDocsPath = " + vDocsPath)
WriteLn("vDocListPath = " + vDocListPath)
WriteLn("vDir = " + vDir)
*/


// BEGIN MAIN PROGRAM

// Get the list of specified LE's on this form (for the data source file).
// Turn it into String Array by replacing C/R's with semicolons

vNames = @Insert(vNamesPath)
vNames = @Replace(vNames, @NewLine(), ";")

// If file missing or empty, bring everything to grinding halt.

If vNames = "" Then
@Msgbox(vNamesPath + " file missing!","LE Names for merge file unavailable.","Can't proceed.")
Else
{

// Added by TJM 8 Jan 05 to report any LE names in
// vNames that are not represented on current form

For n = 1 to @CountStringArray(vNames)

	If @Instr(vLEs, @AccessStringArray(vNames, n)) = 0 Then
	{
	WriteLn("LE name '" + @AccessStringArray(vNames, n) + "' not in current form.")
	WriteLn("(Ref. File: " + vNamesPath + ")" + @NewLine())
	}

Next

// Build Word doc selection menu

// Set position of picklist to the left of and above the command button
PopupSelectPosition(4,@Xpos(ThisElement)-125,@Ypos(ThisElement)-50)

// generate current list of docs
vOK=@shell(vDir)

// Convert the document list from one-per-line to semi-colon separated if nec
vDocList = @insert(vDocListPath)
vDocList = @Replace(vDocList, @NewLine(), ";")

vSelectedDoc = @PopupMenu(vDocList, "SELECT MERGE DOCUMENT" )

If vSelectedDoc <> "" Then

{

// Build which record(s) menu
// Prevents Users from printing batch letters -- 2005/10/31 BW

vOneOrAll = @PopupMenu("THIS Record Only;ALL Retrieved Records","SELECT RECORD(S) TO MERGE PRINT")

if vOneOrAll = "All Retrieved Records" and @group = "Users" then
{
	@msgbox("You do not have access to batch letter printing!","","")
	vOneOrAll = ""
}

// Reset Popup position
PopupSelectPosition(0,99,99)

If vSelectedDoc <> "" and vOneOrAll <> "" Then
{

	// Delete exsting data file. New one will be generated.
	If FileExists(vDocsPath + vSelectedDoc) Then
	{
	fileDelete(vDataPath)

	If vOneOrAll = "THIS Record Only" Then
		PrintCurrentRecordOnly()
	Else
		PrintAllRetrievedRecords()

	If vGoPrint = "Yes" Then
//	n = @Shell("START Winword /mMerge_to_New_Doc " + @Chr(34) + vDocsPath + vselectedDoc + @Chr(34))

	n = @ASynchShell(vDocsPath + vselectedDoc)
	}

	Else
	@Msgbox(vDocsPath + vSelectedDoc + " doesn't exist.","","Aw shucks!")
}
}
}


// Log Letter sent

if vOneOrAll = "THIS Record Only" then
{
	vLttr = @replace(vSelectedDoc,".doc","")
	vLttrInfo = @Serverdate() + " -- " + vLttr + " -- " + DBAdd1 + " " + DBAdd2 + " " + DBCity + " " + DBProv + " " + DBPCod
	if @Len(VLttrInfo) >60 then vLttrInfo = @left(vLttrInfo,60) + " ... "
	DBLttrLog0 = vLttrInfo + @NewLine() + DBLttrLog0

	DBNote0 = "Sent " + vLttr + " -- " + DBAdd1 + " " + DBAdd2 + " " + DBCity + " " + DBProv + " " + DBPCod
}

vCount = @FormResultSetTotal("Note Data")

If DBNote0 <> "" then
{	If vCount = 1 Then
	{	If @FormFieldValue("Note Data", "Note", 1) <> "" Then
		{	vNewNoteNo = @FormNewRecord("Note Data")
		}
		Else
		{	vNewNoteNo = 1
		}
	}
	Else
	{	vNewNoteNo = @FormNewRecord("Note Data")
	}
}
Else
{	If DBNote0 = "" THEN @MsgBox ("", "You Cannot Enter a Blank Note!","")
}

gFileID = @ToNumber(@GlobalValue("gFileID")) +1
vNewFileID = gFileID
GlobalValue("gFileID", vNewFileID)

FormFieldValue("Note Data","NoteRef",vNewNoteNo,DBID)
FormFieldValue("Note Data","NoteDate",vNewNoteNo,@ServerDate())
FormFieldValue("Note Data","NoteTime",vNewNoteNo,@ServerTime())
FormFieldValue("Note Data","Note",vNewNoteNo,DBNote0)
FormFieldValue("Note Data","NoteID",vNewNoteNo,vNewFileID)
FormFieldValue("Note Data","NoteSig",vNewNoteNo,@UserID)

vNewNote = @FormFieldValue("Note Data","Note",vNewNoteNo)


ForceRedraw()

gPosted = 1

DBNote0 = ""

 

In fact, I did notice that!

However, I'm a bit stumped on how to insert references to external fields.  I've played a bit, and had no luck. 

For example, I need data from the staff form to be inserted on each customer file.  I also need client data from the client form.

I'd be happy to rebuild this from 2.x WordMrg, but how do I insert an external series of values?

Thanks!
Blair

Okay ... so I set up a test database, and plugged in WordMerge 2.x.  On it's own, it runs fine. 

I started to insert my queries.  My modifications to the code are in green below.  However, still the same issue -- single mail merge works great, multiple record mail merge, not so much.

Through some judicious WriteLn poking, it appears to get clogged on the Header section of the PrintAllRecords routine.  When I look at the merge text field (after I un-perpetual-loop Sesame), the header has not added my extra fields as I've attempted.  I've marked my inserted code in red to show you what I'm trying to do.

Can anyone explain why my inserted code isn't working?

Thanks ...

// WordMerge 2.44 November 2008
// Written for use with MS Word(tm) Word Processor
// The following 3 variables are user-modifiable ..

var vDataPath as String  = "Docs\MergeData.txt"      // Merge data source file for Word (output file)
                                         // THIS FILE GETS CREATED AUTOMATICALLY
                                         // can optionally be a full path such as
                                         // "C:\Sesame2\Docs\MergeData.txt"

var vDocType as string = ".DOCx;.DOC"                  // document types (file extensions) 
                                         // For Office 2007, added DOCx to document types

var vStartPath as string = "docs"                  // folder to start in, relative to Sesame's Start In folder,
                                         // normally c:\sesame2
                                                  // Can optionally be a full path such as "C:\Docs\MergeDocs"

// ************** Variables for Program (Do Not Modify) ******************
var vFileHandle as Int         // For writing merge data source file for Word
var vHeader as String          // Data source file header line
var vData as String            // Data source file data line
var n as Int                  // General purpose counter var
var vSelectedDoc as String     // The selected Word doc
var vOneOrAll as String        // Include record(s) option
var vNames as string            // Form LE names of LEs with editable data (not static text, etc)
var vStartingRec as INT      // Current record number
var vProceed as INT            // Used to determine whether to run Word
var vVal as string            // Used to hold One data element
var vName as string            // name of an LE on the form for merge
var nn as Int                  // general purpose counter

var vLEs as String            // All LE's on current form

// BW Addition 2011-12-20

var vStaffLEs as String = "StaffName;StaffTitle;StaffEmail"
var vClientLEs as String = "CltName;CltTel1;CltAdd1;CltAdd2;CltCity;CltProv;CltPCod"
// The following are used in the GetDoc() subroutine that selects the doc to merge with
var vPath as String          // Currrent docs path (changes as user navigates)
var vFileTypes as String      // File types to find, such as .doc and .pdf
var vExtensions as String       // Permitted file extensions
var vList as String          // List of folders & files in vPath
var vFolderFlag as Int       // Tells program that user selected a subfolder
var vFolders as String       // Subfolders in selected folder
var vDocs as String          // Available doc files of all desired types in selected folder
var vItem as String          // Item selected by user
                       // vPath and vItem are assigned to vSelectedDoc for the actual Merge

// ******************************************************************
SUBROUTINE CheckData()
// This next line gets rid of embedded carriage returns in the data fields. Replaces with two spaces.
//  This is necessary otherwise the merge will fail
     vData   = @Replace(vData, @Newline(), "  ")

// This next line gets rid of double quotes in the data fields. Puts in 2 x single quotes.
//  This is necessary otherwise the merge will fail ('cannot open source document')
     vData = @Replace(vData, @Chr(34), @Chr(39) + @Chr(39)  )

// This next line checks for an umlauted Y in data fields, resulting from mal-formed yes/no values from Q&A. Replaces with zero-length string.
     vData = @Replace(vData, @Chr(255), "" )
END SUBROUTINE            

// ******************************************************************

SUBROUTINE MergeFields()      // to take all elements on the form, and generate a list of those that can contain data - not statics, not Command buttons, etc
var n as Int
var nn as Int

vLEs = @StringArrayElementList()  // All LE's on current form

For n = 1 to @CountStringArray(vLEs)
     vName = @AccessStringArray(vLEs, n)
     SetThisElement(vName)
     nn = @ElementType(ThisElement)
     If nn > 999 and nn < 1009      // if in this range, element contains editable data
           vNames = @AppendStringArray(vNames, vName)      // add to the list of LEs to be exported for the Mailmerge
     UnSetThisElement()
Next

END SUBROUTINE            

// ******************************************************************

Subroutine GET_DOC()

var vTemp as String
var n as Int

vList = @LocalListDirectory(vPath)

// Isolate subfolders (items that have no "." in them)
vFolders = @SearchStringArray(vList, "/..\...")

vTemp = ""

// Add boldface and "+" sign to folder names
For n = 1 to @CountStringArray(vFolders)
vTemp = @AppendStringArray(vTemp, "@b" + "+" + @AccessStringArray(vFolders, n))
Next

vFolders = vTemp      
     
vFileTypes = @Replace(vExtensions,".","...")
// Isolate document type filenames
vDocs = @SearchStringArray(vList, vFileTypes)
vDocs = @SortStringArray(vDocs, 0)

PopupSelectPosition(4, @XPos(ThisElement), @YPos(ThisElement))

If vFolderFlag = 1 // User selected a subfolder
{
vItem = @PopupChoiceList("@b[BACK]" + ";" + vFolders + ";" + vDocs,
"SELECT FOLDER or DOCUMENT")
vFolderFlag = 0
}
Else
{
vItem = @PopupChoiceList(vFolders + ";" + vDocs, "SELECT FOLDER or DOCUMENT")
}

If vItem = "@b[BACK]"
{
SetStringArraySeparator("\")
vPath = @TrimStringRight(@ReplaceStringArray(vPath, "",
@CountStringArray(vPath)), "\")
RestoreStringArraySeparator()
If @Instr(vPath, "\") > 0 And vPath <> vStartPath Then vFolderFlag = 1
GET_DOC()
}
Else If vItem <> ""
{
If @FindStringArray(vExtensions, @Right(vItem, 4)) = -1

{
vFolderFlag = 1
vPath = vPath + "\" + @Replace(vItem, "@b+", "")
GET_DOC()
}
// user selected a document file

}

End Subroutine            

// BW Added Functions 2011-12-20

Function AddStaffInfo(vDBStaffID as String) as String
var vVals1 as String
var vReturn as String

     vVals1 = @XLookupSourceList(@FN, vDBStaffID, "Staff Screen!StaffRef", vStaffLEs)
     If vVals1 <> ""
     {
           vReturn = vReturn + @AccessStringArray(vVals1, 1) + "^"
                  + @AccessStringArray(vVals1, 2) + "^"
                  + @AccessStringArray(vVals1, 3) + "^"
     }
     Else
     {
           vReturn = vReturn + "^^^"
     }
     Return(vReturn)

End Function

Function AddClientInfo(vDBClientID as String) as String
var vVals2 as String
var vReturn as String

     vVals2 = @XLookupSourceList(@FN, vDBClientID, "Sales Screen!CltNo", vClientLEs)

     If vVals2 <> ""
     {
           vReturn = vReturn + @AccessStringArray(vVals2, 1) + "^"
                  + @AccessStringArray(vVals2, 2) + "^"
                  + @AccessStringArray(vVals2, 3) + "^"
                  + @AccessStringArray(vVals2, 4) + "^"
                  + @AccessStringArray(vVals2, 5) + "^"
                  + @AccessStringArray(vVals2, 6) + "^"
                  + @AccessStringArray(vVals2, 7) + "^"
     }
     Else
     {
           vReturn = vReturn + "^^^^^^^"
     }
     Return(vReturn)

End Function
// ******************************************************************

SUBROUTINE PrintCurrentRecordOnly()
var n as Int
Var i as Int

For n = 1 to @CountStringArray(vNames)
     vHeader = vHeader + @AccessStringArray(vNames,n) + "^"
        vVal  = @(@AccessStringArray(vNames,n))      // the one piece of data from an element
     // Convert if money, number, date, time, bool, image
     vName = @AccessStringArray(vNames, n)
     If vVal <> ""
     {      
     SetThisElement(vName)
     vVal = @AsFormattedByLE(ThisElement, 0, vVal)
     }

        vData  = vData + vVal + "^"
     UnSetThisElement()

     If vName = "DBColl#" Then
     {
           For i = 1 to @CountStringArray(vStaffLEs)
                 vHeader += @AccessStringArray(vStaffLEs, i) + "^"
           Next
           vData = vData + AddStaffInfo(@(vName))
     }

     If vName = "DBCltNo" Then
     {
           For i = 1 to @CountStringArray(vClientLEs)
                 vHeader += @AccessStringArray(vClientLEs, i) + "^"
           Next
           vData = vData + AddClientInfo(@(vName))
     }
Next

CheckData()      // Run subroutine to remove any of 3 non-permitted characters

vFileHandle = FileOpen(vDataPath)
IF vFileHandle <0            // IF FILE CANNOT BE OPENED
THEN      
{
     @MsgBox( "  FILE CANNOT BE OPENED.","  THIS IS PROBABLY BECAUSE A MERGE DOCUMENT ATTACHED TO IT IS ALREADY OPENED.","  CLOSE THE MERGE DOCUMENT(S) AND TRY AGAIN.")
}
ELSE
{
     FileSeek(vFileHandle, 0)
     FileWriteLn(vFileHandle, vHeader + @NewLine() + vData)
     FileClose(vFileHandle)
     vProceed = 1
}

END SUBROUTINE

// ******************************************************************

SUBROUTINE PrintAllRetrievedRecords()

var n as Int
var i as Int

vStartingRec = @ResultSetCurrentPosition()

IF @Askuser("Merge Print " + vSelectedDoc + " for these " + @ResultSetTotal() + " retrieved records?",
"( If not, click on NO to cancel this task, press F7 for a new search,"," and retrieve the records you DO want to print. )")
THEN
{
     For n = 1 to @CountStringArray(vNames)
           vHeader = vHeader+ @AccessStringArray(vNames,n) + "^"
     Next

     vFileHandle = fileOpen(vDataPath)
     IF vFileHandle <0            // IF FILE CANNOT BE OPENED
     THEN      
     {
           @MsgBox( "  FILE CANNOT BE OPENED.","  THIS IS PROBABLY BECAUSE A MERGE DOCUMENT ATTACHED TO IT IS ALREADY OPENED.","  CLOSE THE MERGE DOCUMENT(S) AND TRY AGAIN.")
     }
     ELSE
     {
           FileSeek(vFileHandle, 0)
           FileWriteLn(vFileHandle, vHeader)

           For n = 1 to @ResultSetTotal()
                 ResultSetCurrentPosition(n)

                 For i = 1 to @CountStringArray(vNames)
                 // Convert if money, number, date, time, bool, image
                 vName = @AccessStringArray(vNames, i)
                   vVal = @(@AccessStringArray(vNames,i))
                 If vVal <> ""
                 {      
                 SetThisElement(vName)
                 vVal = @AsFormattedByLE(ThisElement, 0, vVal)
                 }
                 UnSetThisElement()

//BW ADDED 2011-12-20

     If vName = "DBColl#" Then
     {
           For i = 1 to @CountStringArray(vStaffLEs)
                 vHeader += @AccessStringArray(vStaffLEs, i) + "^"
           Next
           vData = vData + AddStaffInfo(@(vName))
     }

     If vName = "DBCltNo" Then
     {
           For i = 1 to @CountStringArray(vClientLEs)
                 vHeader += @AccessStringArray(vClientLEs, i) + "^"
           Next
           vData = vData + AddClientInfo(@(vName))
     }


                       vData = vData +  vVal + "^"

                       CheckData()      // Run subroutine to remove any of 3 non-permitted characters

                 Next

                 FileWriteLn(vFileHandle, vData)

                 @Msg("Processed Record " + n + " of " + @ResultSetTotal())
                 vData = ""
     
           Next

           FileClose(vFileHandle)
           vProceed = 1

           // Rewind to record where it all started
           ResultSetCurrentPosition(vStartingRec)
     }  // END CONDITIONAL FILE CANNOT BE OPENED
}  // END CONDITIONAL DO YOU WANT TO PROCEED WITH THIS MERGE

END SUBROUTINE

//  - - - - - - -  E N D   O F   V A R I B L E   D E C L A R A T I O N S  - - - - - - - - - - - -


// BEGIN MAIN PROGRAM

FormCommit("")

// Jan 2008 - generate the list of LEs for export for merge via MergeFields() subroutine
  MergeFields()            // generates a stringarray list of all editable LEs on the form - Export all editable data for merge

// Use GetDoc subroutine for user to select the document
vExtensions = vDocType            // set the extensions the GetDoc() subroutine looks for
vPath = vStartPath
GET_DOC()

// GetDoc ends up with vItem - make that the selected doc
vSelectedDoc = vPath + "\" + vItem

// Set position of picklist to nearly over the command button
PopupSelectPosition(4,@Xpos(ThisElement)+10,@Ypos(ThisElement))


IF @RT(vSelectedDoc, 1) <> "\" and vSelectedDoc <> "" Then
{
// Build which record(s) menu
     vOneOrAll = @PopupMenu("THIS Record Only;ALL Retrieved Records", "SELECT RECORD(S) TO MERGE PRINT")

// Reset Popup position
     PopupSelectPosition(0,99,99)

     IF vSelectedDoc <> "" and vOneOrAll <> "" Then
     {      
     // Delete existing data file. New one will be generated.
           IF FileExists(vSelectedDoc) Then
           {
                 FileDelete(vDataPath)      

                 IF vOneOrAll = "THIS Record Only" Then
                       PrintCurrentRecordOnly()
                 Else
                       PrintAllRetrievedRecords()
                 IF vProceed = 1 THEN
                 {
                       n = @ASynchShell(vselectedDoc)
                 }

           }  // END CONDITIONAL SELECTED DOCUMENT EXISTS

           ELSE
           @Msgbox(vSelectedDoc + " doesn't exist.","","Aw shucks!")

     }  // END CONDITIONAL ONE OR ALL HAS BEEN SELECTED

} // END CONDITIONAL A DOCUMENT HAS BEEN SELECTED

Okay, I managed to fix the header with the code below.

Now what I am stuck with is inserting code that says if I hit a Staff Value or a Client Value, run my AddStaffInfo or AddClientInfo function ...

SUBROUTINE PrintAllRetrievedRecords()

var n as Int
var i as Int

vStartingRec = @ResultSetCurrentPosition()

IF @Askuser("Merge Print " + vSelectedDoc + " for these " + @ResultSetTotal() + " retrieved records?",
"( If not, click on NO to cancel this task, press F7 for a new search,"," and retrieve the records you DO want to print. )")
THEN
{
     For n = 1 to @CountStringArray(vNames)
           vHeader = vHeader+ @AccessStringArray(vNames,n) + "^"
     Next

     vFileHandle = fileOpen(vDataPath)
     IF vFileHandle <0            // IF FILE CANNOT BE OPENED
     THEN
     {
           @MsgBox( "  FILE CANNOT BE OPENED.","  THIS IS PROBABLY BECAUSE A MERGE DOCUMENT ATTACHED TO IT IS ALREADY OPENED.","  CLOSE THE MERGE DOCUMENT(S) AND TRY AGAIN.")
     }
     ELSE
     {
           FileSeek(vFileHandle, 0)

// BW HEADER WORKS!

                 For i = 1 to @CountStringArray(vStaffLEs)
                 vHeader += @AccessStringArray(vStaffLEs, i) + "^"
                 Next

                 For i = 1 to @CountStringArray(vClientLEs)
                 vHeader += @AccessStringArray(vClientLEs, i) + "^"
                 Next

         FileWriteLn(vFileHandle, vHeader)

           For n = 1 to @ResultSetTotal()
                 ResultSetCurrentPosition(n)

                 For i = 1 to @CountStringArray(vNames)
                 // Convert if money, number, date, time, bool, image
                 vName = @AccessStringArray(vNames, i)


                   vVal = @(@AccessStringArray(vNames,i))
                 If vVal <> ""
                 {
                 SetThisElement(vName)
                 vVal = @AsFormattedByLE(ThisElement, 0, vVal)
 

So close! 

For some reason, I am adding an extra "^" in my string, and it's building the MergeData.txt file, and pulling from multiple records, but it's grabbing data one field to the right ... grrr ...

SUBROUTINE PrintAllRetrievedRecords()

var n as Int
var i as Int

vStartingRec = @ResultSetCurrentPosition()

IF @Askuser("Merge Print " + vSelectedDoc + " for these " + @ResultSetTotal() + " retrieved records?",
"( If not, click on NO to cancel this task, press F7 for a new search,"," and retrieve the records you DO want to print. )")
THEN
{
     For n = 1 to @CountStringArray(vNames)
	   vHeaderField = @AccessStringArray(vNames,n)
           vHeader = vHeader+ @AccessStringArray(vNames,n) + "^"

	   // ***
           // BW Header Attempt #2

	   if vHeaderField = "DBColl#"
		{
                 	For i = 1 to @CountStringArray(vStaffLEs)
	                vHeader += @AccessStringArray(vStaffLEs, i) + "^"
                 	Next
		}

 	   If vHeaderField = "DBCltNo" Then
 		{
                 	For i = 1 to @CountStringArray(vClientLEs)
                 	vHeader += @AccessStringArray(vClientLEs, i) + "^"
                 	Next
		}

     Next

     vFileHandle = fileOpen(vDataPath)
     IF vFileHandle <0            // IF FILE CANNOT BE OPENED
     THEN
     {
           @MsgBox( "  FILE CANNOT BE OPENED.","  THIS IS PROBABLY BECAUSE A MERGE DOCUMENT ATTACHED TO IT IS ALREADY OPENED.","  CLOSE THE MERGE DOCUMENT(S) AND TRY AGAIN.")
     }
     ELSE
     {
           FileSeek(vFileHandle, 0)
           FileWriteLn(vFileHandle, vHeader)

           For n = 1 to @ResultSetTotal()
                 ResultSetCurrentPosition(n)

                 For i = 1 to @CountStringArray(vNames)
                 // Convert if money, number, date, time, bool, image
                 vName = @AccessStringArray(vNames, i)
                   vVal = @(@AccessStringArray(vNames,i))
                 If vVal <> ""
                 {
                 SetThisElement(vName)
                 vVal = @AsFormattedByLE(ThisElement, 0, vVal)
                 }
                 UnSetThisElement()

		 // **
		 // BW Data Merge Attempt #2

                 If vName = "DBColl#" Then
                 {
                 	vData = vData + AddStaffInfo(@(vName))
     		 }

     		 If vName = "DBCltNo" Then
     		 {
           		vData = vData + AddClientInfo(@(vName))
     		 }

                       vData = vData +  vVal + "^"

                       CheckData()      // Run subroutine to remove any of 3 non-permitted characters

                 Next

                 FileWriteLn(vFileHandle, vData)

                 @Msg("Processed Record " + n + " of " + @ResultSetTotal())
                 vData = ""

           Next

           FileClose(vFileHandle)
           vProceed = 1

           // Rewind to record where it all started
           ResultSetCurrentPosition(vStartingRec)
     }  // END CONDITIONAL FILE CANNOT BE OPENED
}  // END CONDITIONAL DO YOU WANT TO PROCEED WITH THIS MERGE

END SUBROUTINE
 

BWETTLAUFER wrote on Dec 22^nd, 2011 at 5:29pm:


C'mon, Blair, you can do this! 

Look at your code and move the part that inserts the values using the subroutine to where it needs to go to do what you want. Look at the code line-by-line and, when you get to the part where you want the values inserted, put the code there.

Your conditionals are logically off.

1. DBNote0 is only getting set if you are printing a single record, which is why you're getting the error.

2. Even if DBNote0 is blank and you don't create a new note record, you are still using FormFieldValue to set the values in the record you did not create. This is not a good plan.   

Okay ... between turkey-induced-comas, I managed to get it to work.  I suppose I could make my coding a little more tight or elegant, but by futzing around, I managed to build this.  If adding external fields, or creating log notes is helpful, please feel free to build on what I have created ...

// WordMerge 2.44 November 2008
// Written for use with MS Word(tm) Word Processor
// The following 3 variables are user-modifiable ..

var vDataPath as String  = "Docs\MergeData.txt"      // Merge data source file for Word (output file)
                                         // THIS FILE GETS CREATED AUTOMATICALLY
                                         // can optionally be a full path such as
                                         // "C:\Sesame2\Docs\MergeData.txt"

var vDocType as string = ".DOCx;.DOC"                  // document types (file extensions)
                                         // For Office 2007, added DOCx to document types

var vStartPath as string = "docs"                  // folder to start in, relative to Sesame's Start In folder,
                                         // normally c:\sesame2
                                                  // Can optionally be a full path such as "C:\Docs\MergeDocs"

// ************** Variables for Program (Do Not Modify) ******************
var vFileHandle as Int         // For writing merge data source file for Word
var vHeader as String          // Data source file header line
var vData as String            // Data source file data line
var n as Int                  // General purpose counter var
var vSelectedDoc as String     // The selected Word doc
var vOneOrAll as String        // Include record(s) option
var vNames as string            // Form LE names of LEs with editable data (not static text, etc)
var vStartingRec as INT      // Current record number
var vProceed as INT            // Used to determine whether to run Word
var vVal as string            // Used to hold One data element
var vName as string            // name of an LE on the form for merge
var nn as Int                  // general purpose counter

var vLEs as String            // All LE's on current form

// BW Addition 2011-12-20

var gFileID as int
var vHeaderField as String
var vNewFileID as int
var vNote as string
var vLttr as String
var vLttrDesc as String
var vLttrInfo as String
var vCount as int
var vNewNoteNo as int
var vNewNote as string
var vLog as string
var vStaffLEs as String = "StaffName;StaffTitle;StaffEmail"
var vClientLEs as String = "CltName;CltTel1;CltAdd1;CltAdd2;CltCity;CltProv;CltPCod"

// The following are used in the GetDoc() subroutine that selects the doc to merge with
var vPath as String          // Currrent docs path (changes as user navigates)
var vFileTypes as String      // File types to find, such as .doc and .pdf
var vExtensions as String       // Permitted file extensions
var vList as String          // List of folders & files in vPath
var vFolderFlag as Int       // Tells program that user selected a subfolder
var vFolders as String       // Subfolders in selected folder
var vDocs as String          // Available doc files of all desired types in selected folder
var vItem as String          // Item selected by user
                       // vPath and vItem are assigned to vSelectedDoc for the actual Merge

// ******************************************************************

SUBROUTINE CheckData()

// This next line gets rid of embedded carriage returns in the data fields. Replaces with two spaces.
//  This is necessary otherwise the merge will fail
     vData   = @Replace(vData, @Newline(), "  ")

// This next line gets rid of double quotes in the data fields. Puts in 2 x single quotes.
//  This is necessary otherwise the merge will fail ('cannot open source document')
     vData = @Replace(vData, @Chr(34), @Chr(39) + @Chr(39)  )

// This next line checks for an umlauted Y in data fields, resulting from mal-formed yes/no values from Q&A. Replaces with zero-length string.
     vData = @Replace(vData, @Chr(255), "" )

END SUBROUTINE

// ******************************************************************

SUBROUTINE MergeFields()      // to take all elements on the form, and generate a list of those that can contain data - not statics, not Command buttons, etc

var n as Int
var nn as Int

vLEs = @StringArrayElementList()  // All LE's on current form

For n = 1 to @CountStringArray(vLEs)
     vName = @AccessStringArray(vLEs, n)
     SetThisElement(vName)
     nn = @ElementType(ThisElement)
     If nn > 999 and nn < 1009      // if in this range, element contains editable data
           vNames = @AppendStringArray(vNames, vName)      // add to the list of LEs to be exported for the Mailmerge
     UnSetThisElement()
Next

END SUBROUTINE

// ******************************************************************

Subroutine GET_DOC()

var vTemp as String
var n as Int

vList = @LocalListDirectory(vPath)

// Isolate subfolders (items that have no "." in them)
vFolders = @SearchStringArray(vList, "/..\...")

vTemp = ""

// Add boldface and "+" sign to folder names
For n = 1 to @CountStringArray(vFolders)
vTemp = @AppendStringArray(vTemp, "@b" + "+" + @AccessStringArray(vFolders, n))
Next

vFolders = vTemp

vFileTypes = @Replace(vExtensions,".","...")
// Isolate document type filenames
vDocs = @SearchStringArray(vList, vFileTypes)
vDocs = @SortStringArray(vDocs, 0)

PopupSelectPosition(4, @XPos(ThisElement), @YPos(ThisElement))

If vFolderFlag = 1 // User selected a subfolder
{
vItem = @PopupChoiceList("@b[BACK]" + ";" + vFolders + ";" + vDocs,
"SELECT FOLDER or DOCUMENT")
vFolderFlag = 0
}
Else
{
vItem = @PopupChoiceList(vFolders + ";" + vDocs, "SELECT FOLDER or DOCUMENT")
}

If vItem = "@b[BACK]"
{
SetStringArraySeparator("\")
vPath = @TrimStringRight(@ReplaceStringArray(vPath, "",
@CountStringArray(vPath)), "\")
RestoreStringArraySeparator()
If @Instr(vPath, "\") > 0 And vPath <> vStartPath Then vFolderFlag = 1
GET_DOC()
}
Else If vItem <> ""
{
If @FindStringArray(vExtensions, @Right(vItem, 4)) = -1

{
vFolderFlag = 1
vPath = vPath + "\" + @Replace(vItem, "@b+", "")
GET_DOC()
}
// user selected a document file

}

END SUBROUTINE

// ******************************************************************

// BW Added Functions 2011-12-20

Function AddStaffInfo(vDBStaffID as String) as String
var vVals1 as String
var vReturn as String

     vVals1 = @XLookupSourceList(@FN, vDBStaffID, "Staff Screen!StaffRef", vStaffLEs)
     If vVals1 <> ""
     {
           vReturn = vReturn + @AccessStringArray(vVals1, 1) + "^"
                  + @AccessStringArray(vVals1, 2) + "^"
                  + @AccessStringArray(vVals1, 3) + "^"
     }
     Else
     {
           vReturn = vReturn + "^^^"
     }
     Return(vReturn)

End Function

// ******************************************************************

Function AddClientInfo(vDBClientID as String) as String

var vVals2 as String
var vReturn as String

     vVals2 = @XLookupSourceList(@FN, vDBClientID, "Sales Screen!CltNo", vClientLEs)

     If vVals2 <> ""
     {
           vReturn = vReturn + @AccessStringArray(vVals2, 1) + "^"
                  + @AccessStringArray(vVals2, 2) + "^"
                  + @AccessStringArray(vVals2, 3) + "^"
                  + @AccessStringArray(vVals2, 4) + "^"
                  + @AccessStringArray(vVals2, 5) + "^"
                  + @AccessStringArray(vVals2, 6) + "^"
                  + @AccessStringArray(vVals2, 7) + "^"
     }
     Else
     {
           vReturn = vReturn + "^^^^^^^"
     }
     Return(vReturn)

End Function

// ******************************************************************

SUBROUTINE PrintCurrentRecordOnly()
var n as Int
Var i as Int

For n = 1 to @CountStringArray(vNames)
     vHeader = vHeader + @AccessStringArray(vNames,n) + "^"
        vVal  = @(@AccessStringArray(vNames,n))      // the one piece of data from an element
     // Convert if money, number, date, time, bool, image
     vName = @AccessStringArray(vNames, n)
     If vVal <> ""
     {
     SetThisElement(vName)
     vVal = @AsFormattedByLE(ThisElement, 0, vVal)
     }

        vData  = vData + vVal + "^"
     UnSetThisElement()

     If vName = "DBColl#" Then
     {
           For i = 1 to @CountStringArray(vStaffLEs)
                 vHeader += @AccessStringArray(vStaffLEs, i) + "^"
           Next
           vData = vData + AddStaffInfo(@(vName))
     }

     If vName = "DBCltNo" Then
     {
           For i = 1 to @CountStringArray(vClientLEs)
                 vHeader += @AccessStringArray(vClientLEs, i) + "^"
           Next
           vData = vData + AddClientInfo(@(vName))
     }

Next

CheckData()      // Run subroutine to remove any of 3 non-permitted characters

vFileHandle = FileOpen(vDataPath)
IF vFileHandle <0            // IF FILE CANNOT BE OPENED
THEN
{
     @MsgBox( "  FILE CANNOT BE OPENED.","  THIS IS PROBABLY BECAUSE A MERGE DOCUMENT ATTACHED TO IT IS ALREADY OPENED.","  CLOSE THE MERGE DOCUMENT(S) AND TRY AGAIN.")
}
ELSE
{
     FileSeek(vFileHandle, 0)
     FileWriteLn(vFileHandle, vHeader + @NewLine() + vData)
     FileClose(vFileHandle)
     vProceed = 1
}

// BW SINGLE NOTE LOG ATTEMPT #2

nn = @FormNewRecord("Note Data")
vLttrDesc = vSelectedDoc
vNote = "Sent " + vLttrDesc + " -- " + DBAdd1 + " " + DBAdd2 + " " + DBCity + " " + DBProv + " " + DBPCod
vLog = @left((@Serverdate() + " " + vLttrDesc + " -- " + DBAdd1 + " " + DBAdd2 + " " + DBCity + " " + DBProv + " " + DBPCod),80)
vNote = @replace(vNote,".doc","")
vNote = @replace(vNote,"docs\","")
vNote = @replace(vNote,"DB\","")

vLog = @replace(vLog,".doc","")
vLog = @replace(vLog,"docs\","")
vLog = @replace(vLog,"DB\","")

gFileID = @ToNumber(@GlobalValue("gFileID")) +1
vNewFileID = gFileID
GlobalValue("gFileID", vNewFileID)

FormFieldValue("Note Data", "NoteRef", nn, DBID)
FormFieldValue("Note Data", "NoteDate", nn, @ServerDate())
FormFieldValue("Note Data", "NoteTime", nn, @ServerTime())
FormFieldValue("Note Data", "NoteSig", nn, @UserID)
FormFieldValue("Note Data", "Note", nn, vNote)

DBLttrLog0 = vLog + @NewLine() + DBLttrLog0

END SUBROUTINE

// ******************************************************************

SUBROUTINE PrintAllRetrievedRecords()

var n as Int
var i as Int

vStartingRec = @ResultSetCurrentPosition()

IF @Askuser("Merge Print " + vSelectedDoc + " for these " + @ResultSetTotal() + " retrieved records?",
"( If not, click on NO to cancel this task, press F7 for a new search,"," and retrieve the records you DO want to print. )")
THEN
{
     For n = 1 to @CountStringArray(vNames)
	   vHeaderField = @AccessStringArray(vNames,n)
           vHeader = vHeader+ @AccessStringArray(vNames,n) + "^"

	   // ***
           // BW Header Attempt #2

	   if vHeaderField = "DBColl#"
		{
                 	For i = 1 to @CountStringArray(vStaffLEs)
	                vHeader += @AccessStringArray(vStaffLEs, i) + "^"
                 	Next
		}

 	   If vHeaderField = "DBCltNo" Then
 		{
                 	For i = 1 to @CountStringArray(vClientLEs)
                 	vHeader += @AccessStringArray(vClientLEs, i) + "^"
                 	Next
		}

     Next

     vFileHandle = fileOpen(vDataPath)
     IF vFileHandle <0            // IF FILE CANNOT BE OPENED
     THEN
     {
           @MsgBox( "  FILE CANNOT BE OPENED.","  THIS IS PROBABLY BECAUSE A MERGE DOCUMENT ATTACHED TO IT IS ALREADY OPENED.","  CLOSE THE MERGE DOCUMENT(S) AND TRY AGAIN.")
     }
     ELSE
     {
           FileSeek(vFileHandle, 0)
           FileWriteLn(vFileHandle, vHeader)

           For n = 1 to @ResultSetTotal()
                 ResultSetCurrentPosition(n)

                 For i = 1 to @CountStringArray(vNames)
                 // Convert if money, number, date, time, bool, image
                 vName = @AccessStringArray(vNames, i)
                   vVal = @(@AccessStringArray(vNames,i))
                 If vVal <> ""
                 {
                 SetThisElement(vName)
                 vVal = @AsFormattedByLE(ThisElement, 0, vVal)
                 }
                 UnSetThisElement()

                       vData = vData +  vVal + "^"

		 // **
		 // BW Data Merge Attempt #3

                 If vName = "DBColl#" Then
                 {
                 	vData = vData + AddStaffInfo(@(vName))
     		 }

     		 If vName = "DBCltNo" Then
     		 {
           		vData = vData + AddClientInfo(@(vName))
     		 }


                       CheckData()      // Run subroutine to remove any of 3 non-permitted characters

                 Next

// BW MASS NOTE LOG ATTEMPT #2

nn = @FormNewRecord("Note Data")
vLttrDesc = vSelectedDoc
vNote = "Sent " + vLttrDesc + " -- " + DBAdd1 + " " + DBAdd2 + " " + DBCity + " " + DBProv + " " + DBPCod
vLog = @left((@Serverdate() + " " + vLttrDesc + " -- " + DBAdd1 + " " + DBAdd2 + " " + DBCity + " " + DBProv + " " + DBPCod),80)
vNote = @replace(vNote,".doc","")
vNote = @replace(vNote,"docs\","")
vNote = @replace(vNote,"DB\","")

vLog = @replace(vLog,".doc","")
vLog = @replace(vLog,"docs\","")
vLog = @replace(vLog,"DB\","")

gFileID = @ToNumber(@GlobalValue("gFileID")) +1
vNewFileID = gFileID
GlobalValue("gFileID", vNewFileID)

FormFieldValue("Note Data", "NoteRef", nn, DBID)
FormFieldValue("Note Data", "NoteDate", nn, @ServerDate())
FormFieldValue("Note Data", "NoteTime", nn, @ServerTime())
FormFieldValue("Note Data", "NoteSig", nn, @UserID)
FormFieldValue("Note Data", "Note", nn, vNote)

DBLttrLog0 = vLog + @NewLine() + DBLttrLog0


                 FileWriteLn(vFileHandle, vData)

                 @Msg("Processed Record " + n + " of " + @ResultSetTotal())
                 vData = ""

           Next

           FileClose(vFileHandle)
           vProceed = 1

           // Rewind to record where it all started
           ResultSetCurrentPosition(vStartingRec)
     }  // END CONDITIONAL FILE CANNOT BE OPENED
}  // END CONDITIONAL DO YOU WANT TO PROCEED WITH THIS MERGE

END SUBROUTINE

//  - - - - - - -  E N D   O F   V A R I B L E   D E C L A R A T I O N S  - - - - - - - - - - - -


// BEGIN MAIN PROGRAM

FormCommit("")

// Jan 2008 - generate the list of LEs for export for merge via MergeFields() subroutine
  MergeFields()            // generates a stringarray list of all editable LEs on the form - Export all editable data for merge

// Use GetDoc subroutine for user to select the document
vExtensions = vDocType            // set the extensions the GetDoc() subroutine looks for
vPath = vStartPath
GET_DOC()

// GetDoc ends up with vItem - make that the selected doc
vSelectedDoc = vPath + "\" + vItem

// Set position of picklist to nearly over the command button
PopupSelectPosition(4,@Xpos(ThisElement)+10,@Ypos(ThisElement))


IF @RT(vSelectedDoc, 1) <> "\" and vSelectedDoc <> "" Then
{
// Build which record(s) menu
     vOneOrAll = @PopupMenu("THIS Record Only;ALL Retrieved Records", "SELECT RECORD(S) TO MERGE PRINT")

// Prevents Users from printing batch letters -- 2005/10/31 BW

if vOneOrAll = "All Retrieved Records" and @group = "Users" then
{
	@msgbox("You do not have access to batch letter printing!","","")
	vOneOrAll = ""
}

// Reset Popup position
     PopupSelectPosition(0,99,99)

     IF vSelectedDoc <> "" and vOneOrAll <> "" Then
     {
     // Delete existing data file. New one will be generated.
           IF FileExists(vSelectedDoc) Then
           {
                 FileDelete(vDataPath)

                 IF vOneOrAll = "THIS Record Only" Then
                       PrintCurrentRecordOnly()
                 Else
                       PrintAllRetrievedRecords()
                 IF vProceed = 1 THEN
                 {
                       n = @ASynchShell(vselectedDoc)
                 }

           }  // END CONDITIONAL SELECTED DOCUMENT EXISTS

           ELSE
           @Msgbox(vSelectedDoc + " doesn't exist.","","Aw shucks!")

     }  // END CONDITIONAL ONE OR ALL HAS BEEN SELECTED

} // END CONDITIONAL A DOCUMENT HAS BEEN SELECTED