Hi Folks

Im using Word & Access 2003, im merging letters from the db - all works ok except that I have to remove nulls from the address line.

So when the bookmark is returned as empty in the letter, we want everyting to move up a line - pls email if thats not making sence !

I have coiped the merge code below.

If you can help = thanks in advance !


Sub mergeletter(doc As String)
' Open a letter in Word and insert text from Access DB

Dim dbs As Database, rstqry_lett As Recordset
Dim intPages As Integer, strMessage As String
Dim StrSQL As String
Dim fileref As String
Dim oApp As Object

fileref = Forms!frm_complaints!tbl_ref_no

StrSQL = "SELECT tbl_complaints.tbl_Client_Name, tbl_complaints.tbl_client_Add1," & _
"tbl_complaints.tbl_client_Add2, tbl_complaints.tbl_client_Add3, " & _
"tbl_complaints.tbl_client_Town, tbl_complaints.tbl_client_County," & _
"tbl_complaints.tbl_pcode, tbl_complaints.tbl_Matter_Num" & _
" FROM tbl_complaints" & _
" WHERE [tbl_complaints]![tbl_ref_no] = " & fileref

' Open a recordset based on the QryApplicantLetters query.
Set dbs = CurrentDb()
Set rstqry_lett = dbs.OpenRecordset(StrSQL)

' If no one has open issues, display a message and exit.
If rstqry_lett.RecordCount = 0 Then
MsgBox "There is no letter to be printed."
Exit Sub
End If

' Switch to Microsoft Word so it won't go away when you finish.
On Error Resume Next
AppActivate "Microsoft Word"

' If Word isn't running, start and activate it.
If Err Then
Shell "C:\Program Files\Microsoft Office\Office\WINWORD.EXE", _
AppActivate "Microsoft Word"
End If
On Error GoTo 0

' Get an Application object so you can automate Word.
' Set oApp = GetObject(, "Word.Application")

Set oApp = CreateObject("Word.Application")
oApp.Visible = True
' Open a document

'Dim letter As New oApp.Document
oApp.Documents.Add doc

'Insert the fields from the Databse

'For Each bm In oApp.activedocument.Bookmarks
' MsgBox bm
' Next bm
oApp.Selection.Goto what:=wd

For Each BM In oApp.activedocument.bookmarks
BM.Range.Text = Nz(rstqry_lett.Fields(BM))
Next BM
'oApp.activedocument.Bookmarks.Item("name").Range. Text = rstqry_lett!tbl_Client_Name
End Sub