merge current record

[jongooligan]

Using the following code to merge current record from an Access 97 form to a Word doc. It works fine except where there is no data in some of the address_line fields when it leaves the postcode orphaned by as many as three lines. Any ideas how to ignore empty fields and move the postcode to the line after the last populated adress_line?

Public Sub C12DocMerge(lngAppID As Long)

Dim dbs As Database
Dim strSQL As String
Dim rstMerge As Recordset
Dim AppWord As Word.Application
strSQL = vbNullString

' Return reference to current database.
Set dbs = CurrentDb

'Populate strSQL with details from current record
strSQL = "SELECT tblApplicant.ApplicantId, tblApplicant.App_Forename, "
strSQL = strSQL & "tblApplicant.App_Surname, tblApplicant.AccountNo, "
strSQL = strSQL & "tblAddress.Address_Line_1, tblAddress.Address_Line_2, "
strSQL = strSQL & "tblAddress.Address_Line_3, tblAddress.Address_Line_4, "
strSQL = strSQL & "tblAddress.Address_Line_5, tblAddress.Post_Code "
strSQL = strSQL & "FROM tblApplicant INNER JOIN "
strSQL = strSQL & "(tblAddress INNER JOIN tblAddressLink "
strSQL = strSQL & "ON tblAddress.AddressId = tblAddressLink.AddressId) "
strSQL = strSQL & "ON tblApplicant.ApplicantId = tblAddressLink.ApplicantId "
strSQL = strSQL & "WHERE (tblApplicant.ApplicantID =" & lngAppID & ")"

' Create Recordset object.
Set rstMerge = dbs.OpenRecordset(strSQL)

'Get an Application object to open Word.
Set AppWord = CreateObject("Word.Application")

With AppWord
.Visible = True
.WindowState = wdWindowStateMaximize
.Documents.Add "\\C\C12\DearSomebody.dot", False
.ActiveDocument.ShowSpellingErrors = False
.Selection.Goto wdGoToBookmark, Name:="Forename"
.Selection.TypeText rstMerge![App_Forename]
.Selection.Goto wdGoToBookmark, Name:="Surname"
.Selection.TypeText rstMerge![App_Surname]
.Selection.Goto wdGoToBookmark, Name:="Add1"
.Selection.TypeText rstMerge![Address_Line_1]
.Selection.Goto wdGoToBookmark, Name:="Add2"
.Selection.TypeText rstMerge![Address_Line_2]
.Selection.Goto wdGoToBookmark, Name:="Add3"
.Selection.TypeText rstMerge![Address_Line_3]
.Selection.Goto wdGoToBookmark, Name:="Add4"
.Selection.TypeText rstMerge![Address_Line_4]
.Selection.Goto wdGoToBookmark, Name:="Add5"
.Selection.TypeText rstMerge![Address_Line_5]
.Selection.Goto wdGoToBookmark, Name:="PostCode"
.Selection.TypeText rstMerge![Post_Code]


End With

Thanks in anticipation

 

[Smart]

Where you populate StrSql you could popukate the address lines seperately and use an if statement
strSQL = strSQL & "tblAddress.Address_Line_1, "
if nz([tblAddress.Address_Line_2],"") <> "" then
strSQL = strSQL & "tblAddress.Address_Line_2, "
end if

if nz([tblAddress.Address_Line_3],"") <> "" then
strSQL = strSQL & "tblAddress.Address_Line_3, "
end if

if nz([tblAddress.Address_Line_4],"") <> "" then
strSQL = strSQL & "tblAddress.Address_Line_4, "
end if

if nz([tblAddress.Address_Line_5],"") <> "" then
strSQL = strSQL & "tblAddress.Address_Line_5, "
end if

strSQL = strSQL & "tblAddress.Post_Code "

 

[jongooligan]

Thanks for this. Will give it a go on Monday.