asked on

Deleting Bookmarks and their line in Word from Access

Im trying to fill a Word document using bookmarks and getting their information from access. This is what I have. There are multiple lines that I would need the possibility of deleting but when I do that it messes with formatting completely. When doing just one line in here it seems to work but also places a one at the beginning of the next line. So I guess what I am asking is why is there a one be placed? How come when I try to delete multiple lines it messes with the formatting? Below is the VBA i have so far

Private Sub Command207_Click()
    Dim wApp As Word.Application
    Dim wDoc As Word.Document
    Dim rs As DAO.recordset
    
    Set wApp = New Word.Application
    Set wDoc = wApp.Documents.Open("W:\Administration\Temp Work\Garnett_Brandon\Maybe this will work.docx")
    wApp.Visible = True
    
   'Put query for prof title and designation up here
    
    Set rs = CurrentDb.OpenRecordset("Select JobTitle, CompanyName FROM qry_frmContactMain_RelatedCompanies", dbOpenDynaset, dbSeeChanges)
    wDoc.Bookmarks("LastName").Range.Text = IIf(Me!LastName > "", " " & Me!LastName, "")
    wDoc.Bookmarks("MiddleName").Range.Text = IIf(Me!MiddleName > "", " " & Me!MiddleName, "")
    wDoc.Bookmarks("FirstName").Range.Text = IIf(Me!FirstName > "", Me!FirstName, "")
    
    'wDoc.Bookmarks("JobTitle").Select
    wDoc.Bookmarks("JobTitle").Range.Text = IIf(rs!JobTitle > "", rs!JobTitle, "")
    
    'wDoc.Bookmarks("CompanyName").Select
    wDoc.Bookmarks("CompanyName").Range.Text = IIf(rs!CompanyName > "", rs!CompanyName, "")
    
    Set rs = CurrentDb.OpenRecordset("Select Building, Street, HousingType, HousingNumber, Unit, Floor, PObox, NonPObox, City, State, postalcode FROM qry_frmContactMain_AddressList WHERE MailTo = ""mailto""", dbOpenDynaset, dbSeeChanges)
    
    wDoc.Bookmarks("HousingNumber").Range.Text = IIf(rs!HousingNumber > "", " " & rs!HousingNumber, "")
    wDoc.Bookmarks("HousingType").Range.Text = IIf(rs!HousingType > "", ", " & rs!HousingType, "")
    wDoc.Bookmarks("Floor").Range.Text = IIf(rs!Floor > "", ", Floor " & rs!Floor, "")
    wDoc.Bookmarks("StreetName").Range.Text = IIf(rs!Street > "", rs!Street, "")
    
    wDoc.Bookmarks("BuildingName").Select
    wDoc.Bookmarks("BuildingName").Range.Text = IIf(rs!Building > "", rs!Building, Selection.Bookmarks("\Line").Range.Delete)
    
    
    wDoc.Bookmarks("postalcode").Range.Text = IIf(rs!postalcode > "", " " & rs!postalcode, "")
    wDoc.Bookmarks("State").Range.Text = IIf(rs!State > "", ", " & rs!State, "")
    wDoc.Bookmarks("City").Range.Text = IIf(rs!City > "", rs!City, "")
    
End Sub

Open in new window

SOLUTION

GrahamSkan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Brandon Garnett

ASKER

Here is an image of the 1 showing up after I remove the empty building name line that is above it.

Here is that same contact with the three deletes as you can see bookmarks are missing that should of been deleted. I am sure I am doing something dumb I just cant seem to see it.

Here is the code with 3 deletes

Private Sub Command207_Click()
    Dim wApp As Word.Application
    Dim wDoc As Word.Document
    Dim rs As DAO.recordset
    
    Set wApp = New Word.Application
    Set wDoc = wApp.Documents.Open("W:\Administration\Temp Work\Garnett_Brandon\Maybe this will work.docx")
    wApp.Visible = True

    Set rs = CurrentDb.OpenRecordset("Select JobTitle, CompanyName FROM qry_frmContactMain_RelatedCompanies", dbOpenDynaset, dbSeeChanges)
    wDoc.Bookmarks("LastName").Range.Text = IIf(Me!LastName > "", " " & Me!LastName, "")
    wDoc.Bookmarks("MiddleName").Range.Text = IIf(Me!MiddleName > "", " " & Me!MiddleName, "")
    wDoc.Bookmarks("FirstName").Range.Text = IIf(Me!FirstName > "", Me!FirstName, "")
    
    wDoc.Bookmarks("JobTitle").Select
    wDoc.Bookmarks("JobTitle").Range.Text = IIf(rs!JobTitle > "", rs!JobTitle, Selection.Bookmarks("\Line").Range.Delete)
    
    wDoc.Bookmarks("CompanyName").Select
    wDoc.Bookmarks("CompanyName").Range.Text = IIf(rs!CompanyName > "", rs!CompanyName, Selection.Bookmarks("\Line").Range.Delete)
    
    Set rs = CurrentDb.OpenRecordset("Select Building, Street, HousingType, HousingNumber, Unit, Floor, PObox, NonPObox, City, State, postalcode FROM qry_frmContactMain_AddressList WHERE MailTo = ""mailto""", dbOpenDynaset, dbSeeChanges)
    
    wDoc.Bookmarks("HousingNumber").Range.Text = IIf(rs!HousingNumber > "", " " & rs!HousingNumber, "")
    wDoc.Bookmarks("HousingType").Range.Text = IIf(rs!HousingType > "", ", " & rs!HousingType, "")
    wDoc.Bookmarks("Floor").Range.Text = IIf(rs!Floor > "", ", Floor " & rs!Floor, "")
    wDoc.Bookmarks("StreetName").Range.Text = IIf(rs!Street > "", rs!Street, "")
    
    wDoc.Bookmarks("BuildingName").Select
    wDoc.Bookmarks("BuildingName").Range.Text = IIf(rs!Building > "", rs!Building, Selection.Bookmarks("\Line").Range.Delete)
    'wDoc.Bookmarks("BuildingName").Range.Text = IIf(rs!Building > "", rs!Building, wDoc.Bookmarks("BuildingName").Range.Paragraphs(1).Range.Delete)
    
    wDoc.Bookmarks("postalcode").Range.Text = IIf(rs!postalcode > "", " " & rs!postalcode, "")
    wDoc.Bookmarks("State").Range.Text = IIf(rs!State > "", ", " & rs!State, "")
    wDoc.Bookmarks("City").Range.Text = IIf(rs!City > "", rs!City, "")
    
End Sub

Open in new window

SOLUTION

GrahamSkan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

crystal (strive4peace) - Microsoft MVP, Access

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

GrahamSkan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Brandon Garnett

ASKER

Ok I am trying to put in the updated solution and I'm getting an error

Here is the updated code

Private Sub Command207_Click()
    Dim wApp As Word.Application
    Dim wDoc As Word.Document
    Dim rs As DAO.recordset
    
    Set wApp = New Word.Application
    Set wDoc = wApp.Documents.Open("W:\Administration\Temp Work\Garnett_Brandon\Maybe this will work.docx")
    wApp.Visible = True

    Set rs = CurrentDb.OpenRecordset("Select JobTitle, CompanyName FROM qry_frmContactMain_RelatedCompanies", dbOpenDynaset, dbSeeChanges)
    wDoc.Bookmarks("LastName").Range.Text = IIf(Me!LastName > "", " " & Me!LastName, "")
    wDoc.Bookmarks("MiddleName").Range.Text = IIf(Me!MiddleName > "", " " & Me!MiddleName, "")
    wDoc.Bookmarks("FirstName").Range.Text = IIf(Me!FirstName > "", Me!FirstName, "")
    
    FillData wDoc, rs!JobTitle, "JobTitle"
    'wDoc.Bookmarks("JobTitle").Select
    'wDoc.Bookmarks("JobTitle").Range.Text = IIf(rs!JobTitle > "", rs!JobTitle, "")
    FillData wDoc, rs!CompanyName, "CompanyName"
    'wDoc.Bookmarks("CompanyName").Select
    'wDoc.Bookmarks("CompanyName").Range.Text = IIf(rs!CompanyName > "", rs!CompanyName, "")
    
    Set rs = CurrentDb.OpenRecordset("Select Building, Street, HousingType, HousingNumber, Unit, Floor, PObox, NonPObox, City, State, postalcode FROM qry_frmContactMain_AddressList WHERE MailTo = ""mailto""", dbOpenDynaset, dbSeeChanges)
    
    wDoc.Bookmarks("HousingNumber").Range.Text = IIf(rs!HousingNumber > "", " " & rs!HousingNumber, "")
    wDoc.Bookmarks("HousingType").Range.Text = IIf(rs!HousingType > "", ", " & rs!HousingType, "")
    wDoc.Bookmarks("Floor").Range.Text = IIf(rs!Floor > "", ", Floor " & rs!Floor, "")
    wDoc.Bookmarks("StreetName").Range.Text = IIf(rs!Street > "", rs!Street, "")
    
    FillData wDoc, rs!BuildingName, "BuildingName"
   ' wDoc.Bookmarks("BuildingName").Select
    'wDoc.Bookmarks("BuildingName").Range.Text = IIf(rs!Building > "", rs!Building, Selection.Bookmarks("\Line").Range.Delete)
    'wDoc.Bookmarks("BuildingName").Range.Text = IIf(rs!Building > "", rs!Building, wDoc.Bookmarks("BuildingName").Range.Paragraphs(1).Range.Delete)
    
    wDoc.Bookmarks("postalcode").Range.Text = IIf(rs!postalcode > "", " " & rs!postalcode, "")
    wDoc.Bookmarks("State").Range.Text = IIf(rs!State > "", ", " & rs!State, "")
    wDoc.Bookmarks("City").Range.Text = IIf(rs!City > "", rs!City, "")
    
End Sub

Sub FillData(wDoc As Document, strText As String, strBookMarkName As String)
    If strText = "" Then
        wDoc.Bookmarks(strBookMarkName).Range.Paragraphs.First.Range.Delete
    Else
        wDoc.Bookmarks(strBookMarkName).Range.Text = strText
    End If
End Sub

Open in new window

SOLUTION

GrahamSkan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

GrahamSkan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Brandon Garnett

ASKER

Ok I have updated the code and it works when the information is there to fill it but when it got to rs!Building that was empty and it had an error stating invalid use of Null

Private Sub Command207_Click()
    Dim wApp As Word.Application
    Dim wDoc As Word.Document
    Dim rs As DAO.recordset
    
    Set wApp = New Word.Application
    Set wDoc = wApp.Documents.Open("W:\Administration\Temp Work\Garnett_Brandon\Maybe this will work.docx")
    wApp.Visible = True

    Set rs = CurrentDb.OpenRecordset("Select JobTitle, CompanyName FROM qry_frmContactMain_RelatedCompanies", dbOpenDynaset, dbSeeChanges)
    wDoc.Bookmarks("LastName").Range.Text = IIf(Me!LastName > "", " " & Me!LastName, "")
    wDoc.Bookmarks("MiddleName").Range.Text = IIf(Me!MiddleName > "", " " & Me!MiddleName, "")
    wDoc.Bookmarks("FirstName").Range.Text = IIf(Me!FirstName > "", Me!FirstName, "")
    
    FillData wDoc, rs!JobTitle, "JobTitle"
    'wDoc.Bookmarks("JobTitle").Select
    'wDoc.Bookmarks("JobTitle").Range.Text = IIf(rs!JobTitle > "", rs!JobTitle, "")
    FillData wDoc, rs!CompanyName, "CompanyName"
    'wDoc.Bookmarks("CompanyName").Select
    'wDoc.Bookmarks("CompanyName").Range.Text = IIf(rs!CompanyName > "", rs!CompanyName, "")
    
    Set rs = CurrentDb.OpenRecordset("Select Building, Street, HousingType, HousingNumber, Unit, Floor, PObox, NonPObox, City, State, postalcode FROM qry_frmContactMain_AddressList WHERE MailTo = ""mailto""", dbOpenDynaset, dbSeeChanges)
    
    wDoc.Bookmarks("HousingNumber").Range.Text = IIf(rs!HousingNumber > "", " " & rs!HousingNumber, "")
    wDoc.Bookmarks("HousingType").Range.Text = IIf(rs!HousingType > "", ", " & rs!HousingType, "")
    wDoc.Bookmarks("Floor").Range.Text = IIf(rs!Floor > "", ", Floor " & rs!Floor, "")
    wDoc.Bookmarks("StreetName").Range.Text = IIf(rs!Street > "", rs!Street, "")
    
-----> FillData wDoc, rs!Building, "BuildingName"
   ' wDoc.Bookmarks("BuildingName").Select
    'wDoc.Bookmarks("BuildingName").Range.Text = IIf(rs!Building > "", rs!Building, Selection.Bookmarks("\Line").Range.Delete)
    'wDoc.Bookmarks("BuildingName").Range.Text = IIf(rs!Building > "", rs!Building, wDoc.Bookmarks("BuildingName").Range.Paragraphs(1).Range.Delete)
    
    wDoc.Bookmarks("postalcode").Range.Text = IIf(rs!postalcode > "", " " & rs!postalcode, "")
    wDoc.Bookmarks("State").Range.Text = IIf(rs!State > "", ", " & rs!State, "")
    wDoc.Bookmarks("City").Range.Text = IIf(rs!City > "", rs!City, "")
    
End Sub

Sub FillData(wDoc As Word.Document, strText As String, strBookMarkName As String)
    If strText = "" Then
        wDoc.Bookmarks(strBookMarkName).Range.Paragraphs.First.Range.Delete
    Else
        wDoc.Bookmarks(strBookMarkName).Range.Text = strText
    End If
End Sub

Open in new window

ASKER CERTIFIED SOLUTION

crystal (strive4peace) - Microsoft MVP, Access

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Brandon Garnett

ASKER

Awesome! Thanks for both of your help. Seems to be working now

Brandon Garnett

ASKER

Thanks again

crystal (strive4peace) - Microsoft MVP, Access

Hi Brandon,

Good to hear! You're welcome

btw, the object variables need to be closed/quit/released at the end of the procedure.

for instance:

on error resume next
rs.close
set rs=nothing
set db=nothing

wDoc.close
set wDoc=nothing

wApp.Quit
set wApp =nothing

have an awesome day,
crystal