I am merging an access 2000 db record to a word 2000 / 97 word doc - the word doc has bookmarks inserted in the doc to populate the fields.

On the address field there are some blanks in the address lines - i need to suppress the blanks - is there a way ??

Thanks !


Private Sub Command41_Click()
' Open letter Registered claimant in Word and insert text - used by menu command.

Dim dbs As Database
Dim appWord As Word.Application
Dim intPages As Integer, strMessage As String
Dim StrSQL As String
Dim fileref As String

On Error Resume Next
Set appWord = GetObject(, "Word.application")
If Err = 429 Then
Set appWord = New Word.Application
Err = 0
End If

fileref = Me.Client_ID

'StrSQL = "SELECT tbl_Client_info.* FROM tbl_Client_info;"

StrSQL = "SELECT tbl_Client_info.* FROM tbl_client_info WHERE tbl_client_info.client_id = " & fileref

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

' If no one has open issues, display a message and exit.
If rsttbl_Client_Info.RecordCount = 0 Then
DisplayMessage "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 appWord = GetObject(, "Word.Application")

' Open a document based on the memo template, turn off the
' automatic spell check, and move to the MemoToLine bookmark.

appWord.Documents.Add "N:\NELSYS\AWC\Sandon Doc Database\Registered Claimant.doc"
'appWord.ActiveDocument.ShowSpellingErrors = False

' Loop through the recordset returned by the query, inserting
' the name of each field into the document.

appWord.Selection.GoTo wdGoToBookmark, Name:="title"
appWord.Selection.TypeText Nz(rsttbl_Client_Info!Title, " ")
appWord.Selection.GoTo wdGoToBookmark, Name:="Initial"
appWord.Selection.TypeText Nz(rsttbl_Client_Info!Initials, " ")
appWord.Selection.GoTo wdGoToBookmark, Name:="surname"
appWord.Selection.TypeText Nz(rsttbl_Client_Info!Surname, " ")
appWord.Selection.GoTo wdGoToBookmark, Name:="title2"
appWord.Selection.TypeText Nz(rsttbl_Client_Info!Title, " ")
appWord.Selection.GoTo wdGoToBookmark, Name:="surname2"
appWord.Selection.TypeText Nz(rsttbl_Client_Info!Surname, " ")
appWord.Selection.GoTo wdGoToBookmark, Name:="Add1"
appWord.Selection.TypeText Nz(rsttbl_Client_Info!Add1, " ")
appWord.Selection.GoTo wdGoToBookmark, Name:="Add2"
appWord.Selection.TypeText Nz(rsttbl_Client_Info!Add2, " ")
appWord.Selection.GoTo wdGoToBookmark, Name:="Add3"
appWord.Selection.TypeText Nz(rsttbl_Client_Info!Add3, " ")
appWord.Selection.GoTo wdGoToBookmark, Name:="Add4"
appWord.Selection.TypeText Nz(rsttbl_Client_Info!Add4, " ")
appWord.Selection.GoTo wdGoToBookmark, Name:="Postcode"
appWord.Selection.TypeText Nz(rsttbl_Client_Info!PostCode, " ")
appWord.Selection.GoTo wdGoToBookmark, Name:="Client_ID"
appWord.Selection.TypeText Nz(rsttbl_Client_Info!Client_ID, " ")

appWord.Selection.GoTo wdGoToBookmark, Name:="Client_ID"

'Return to Access.
AppActivate "Sandon"

'Get the number of pages in the memo, ask the user whether
' to print it, and then tell Word to print it.
intPages = appWord.Selection.Information(wdNumberOfPagesInDoc ument)
strMessage = "The Letter is complete would you like to Preview?"
If Confirm(strMessage) Then
AppActivate "Microsoft Word"
End If

End Sub