Making a letter from the current record

This macro shows how to take the data from the current record in a Database and generate a letter in Write. The macro is broken into manageable junks in the description below. To see the macro in full, with tips on how to use it with your own data, see Create letter from database - code listing.

1. Initialize the macro

Sub MakeLetterFromDB

Dim myflds(9), lfld, s, tb, appWrite, mydoc, i 

 

lastfld = 8 

myflds(0) = "CustTitle" 

myflds(1) = "Firstname" 

myflds(2) = "Lastname" 

myflds(3) = "Company" 

myflds(4) = "Add1" 

myflds(5) = "Add2"  

myflds(6) = "Add3"  

myflds(7) = "Add4"  

myflds(8) = "Pcode"  

The first line declares in advance all the variables that are going to used (good practice). Note the declaration of an array myflds with 9 elements, that holds the names of the fields to be used for the address block – obviously, this will need adjustment for your own database.

The variable lastfld is just a reminder that any loops addressing the myflds array will need to stop at 8 (arrays are zero based).

2. Get "hold off" the current table

Set tb = ActiveDataObject 

ActiveDataObject returns the current table or query or relation and the line above saves a reference to this in the tb variable. This makes the macro as general as possible – no table name or type of object (table/query/relation) need be specified.

You could replace this with a specific table if necessary. For example

Set tb = ActiveDatabase.Tables("mytable")

would also do the job, providing mytable was open at the time (and pointing to the actual record you want to use).

3. Make an address block

s = "" 

For i = 0 To lastfld 

txt = tb.Fields(myflds(i)).Value 

If Len(txt) > 0 Then 

s = s & txt 

If i < 2 Then 

s = s & " " 

Else 

s = s & vbCr 

End If 

End If 

Next 

The code above loops through the fields listed in part 1 and adds them to a variable s. It checks to see if the fields is blank (in which case it's skipped) and also takes account of the first line of the address block, the code: If i < 2 then tests for this. All other address lines are added to s along with a new line (vbCr).

4. Ask the user if he wants to continue

i = MsgBox("Make a letter with this record?" & _ 

vbCr & vbCr & s, vbOKCancel, _ 

"Ability Database Macro Message") 

If i = vbCancel Then 

Exit Sub 

End If 

This shows a message box with the address block and asks the user to continue or not. If the user selects the cancel button, the macro is terminated.

5. Open a new write document and drop in the text

Set appWrite = CreateObject("AbilityWrite.Application") 

Set mydoc = appWrite.Documents.Add("NORMAL") 

mydoc.Text.Insert 0, s & vbCr & vbCr  

mydoc.Selection.End = mydoc.Text.Count 

mydoc.Selection.Start = Selection.End 

 

appWrite.Activate 

 

End Sub

The first two lines start Write and create a new document. The third line drops in the text.

The fourth and fifth line makes sure the cursor is at the end of the document and finally the Write application is made visible by the Activate command so the user can finish off the letter.