Blank data causes crash with word bookmarks.

[RichardP1978]

OK, I am learning loads.

I have made it so when a user clicks a certain button, it starts word up, fills in name and address of the recipient, and fills in who the letter is in reference to. Works a treat UNTIL

a field tries to be inserted in to the bookmark, the code crashes out to debug, and stops filling in rest of word bookmarks.

I have done a search and cant really find an answer to my problems.

I need the code to omit blank fields. It would also be great if it could 'close' up the text in word, so if an address only has 3 lines, when most are 4, there are no gaps inbetween.

Any suggestions.

I think there are many ways of doing the bookmarks, but I am doing mine using the following code

objWord.ActiveDocument.Bookmarks("To").Select
objWord.Selection.Text = Forms![frmletterMenu]![GPName]

 

[Bat17]

Not sure this is the best way but it works for me

    Set doc = objWord.Documents.Add(strPath & "Initial Enquiry Letter Template.dot")
    
    
    doc.BookMarks("CompanyName").select
    strTemp = rs!CompanyName
    objWord.Selection.TypeText strTemp
    doc.BookMarks("CompanyName2").select
    objWord.Selection.TypeText strTemp
    doc.BookMarks("CompanyAddress").select
    If Not IsNull(rs!AddressLine1) Then
        strTemp = rs!AddressLine1
        objWord.Selection.TypeText strTemp
    End If
    If Not IsNull(rs!AddressLine2) Then
        objWord.Selection.TypeParagraph
        strTemp = rs!AddressLine2
        objWord.Selection.TypeText strTemp
    End If
    If Not IsNull(rs!AddressLine3) Then
        objWord.Selection.TypeParagraph
        strTemp = rs!AddressLine3
        objWord.Selection.TypeText strTemp
    End If
    If Not IsNull(rs!postTown) Then
        objWord.Selection.TypeParagraph
        strTemp = rs!postTown
        objWord.Selection.TypeText strTemp
    End If
    If Not IsNull(rs!postCounty) Then
        objWord.Selection.TypeParagraph
        strTemp = rs!postCounty
        objWord.Selection.TypeText strTemp
    End If
    If Not IsNull(rs!postCountry) Then
        objWord.Selection.TypeParagraph
        strTemp = rs!postCountry
        objWord.Selection.TypeText strTemp
    End If
    If Not IsNull(rs!postCode) Then
        objWord.Selection.TypeParagraph
        strTemp = rs!postCode
        objWord.Selection.TypeText strTemp
    End If

    
    doc.BookMarks("SendDate").select
    objWord.Selection.TypeText Format(Date, "dd MMM YYYY")

Just one bookmark for the Address, the objWord.Selection.TypeParagraph adds a line break for the next line.

HTH

Peter

 

[RichardP1978]

I think I can see how that works, but I cant work out quite how to use that with my field names.

This is my code for the whole thing, which does work, just not removing the spaces in what would be [tblGPSurgerys.Address3], and [tblGPSurgerys.Address4]

Not sure on how to integrate what Peter suggests in to my code. I dont understand if I require rs! or not (I assume that is code for recordset?


Private Sub GPLetter_Click()

Set objWord = CreateObject("Word.Application")
objWord.Visible = True 'True is visible

objWord.Documents.Add ("F:\ClientDatabase\LetterHeadAccess.dot")

objWord.ActiveDocument.Bookmarks("To").Select
objWord.Selection.Text = Forms![frmletterMenu]![GPName]

objWord.ActiveDocument.Bookmarks("Location").Select
objWord.Selection.Text = Forms![frmletterMenu]![GPSurgery]

objWord.ActiveDocument.Bookmarks("Add1").Select
objWord.Selection.Text = Forms![frmletterMenu]![tblGPSurgerys.Address1] & Chr(13) & Forms![frmletterMenu]![tblGPSurgerys.Address2] & Chr(13) & Forms![frmletterMenu]![tblGPSurgerys.Address3] & Chr(13) & Forms![frmletterMenu]![tblGPSurgerys.Address4] & Chr(13) & Forms![frmletterMenu]![tblGPSurgerys.PostCode]


Set objWord = Nothing

End Sub

 

[Bat17]

The rs! is becuse I am working from a record set not a form but I found that

objWord.Selection.TypeText rs!AddressLine3
Did not work which is why I used 'strTemp', you may need

objWord.ActiveDocument.Bookmarks("To").Select
strTemp = Forms![frmletterMenu]![GPName]
objWord.Selection.Text = strTemp

HTH

Peter

 

[chrisdedobb]

Try this

Here is a sample of your code and how to implement what you are trying to do:

if isnull(Forms![frmletterMenu]![GPName]) = true then
objWord.ActiveDocument.Bookmarks("To").Select
objWord.Selection.Text = ""
else
objWord.ActiveDocument.Bookmarks("To").Select
objWord.Selection.Text = Forms![frmletterMenu]![GPName]
end if

Let me know if this works!

 

[RichardP1978]

Peters idea crashes out with type mismatch when there is no data.

Chris's idea works without errors, but does not omit the blanks.






Although I am not over concerned with this right now as I was due to be paid today and I havent been so they can whistle for any urgency on my part! lol

 

[RichardP1978]

OK solved it

The part of chris's code where it just would print a "" if address4 was blank, I actually put the postcode there. Its always going to be address4 that has got the potential to be blank. If Add4 is not blank, it will do a carriage return and then put post code in.

So thanks guys, you have helped me learn something again.