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

Brandon GarnettAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GrahamSkanRetiredCommented:
'messes with formatting ' could means many things.
I think we need to see a sample of the document (the template) before the run and one after to see the effect..

I've put your code into a snippet box using the 'Code' tool from the edit tool bar.
0
Brandon GarnettAuthor Commented:
1-shows-up.PNGHere 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.
MultiDel.PNG
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

0
GrahamSkanRetiredCommented:
It seems that
Selection.Bookmarks("\Line").Range.Delete

Open in new window

returns a value of 1, so that is what the Iif statement resolves to.

You could create a procedure to fill in each line so:
Sub FillData(wDoc As Document, strText As String, strBookMarkName As String)
    If strText = "" Then
        wDoc.Bookmarks(strBookMarkName).Select
        Selection.Bookmarks("\Line").Range.Delete
    Else
        wDoc.Bookmarks(strBookMarkName).Range.Text = strText
    End If
End Sub

Open in new window


and call it with code like:
   
FillData wDoc, Me!LastName, "LastName"

Open in new window

0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
this might be extraneous info because I am not sure what Graham's "\Line" is doing ...
another method, since bookmarks can only be in one place, is to search for the bookmark, then insert the text just before it. This leaves the bookmark so the spot can still be found with GoTo (Ctrl-G).  This also requires that the bookmark does not have any text in it (since it won't be replaced -- unless that is ok).  
   with oDoc 'document variable
         With .Goto(What:=-1, Name:=strBookMarkName)   'substituted constant value for late-binding automation
            .InsertBefore rs!myFieldname
         End With
   end with

Open in new window

have an awesome day,
crystal
0
GrahamSkanRetiredCommented:
Prompted by crystal (strive4peace) - Microsoft MVP, Access elsewhere, I have decided that this would be better than my first suggestion. It doesn't use Selection and specifically deletes the paragraph as opposed to the line. If the paragraph already has enough text, it will overflow to more than one line so the earlier method would leave the rest of the text undeleted.

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

2
Brandon GarnettAuthor Commented:
Ok I am trying to put in the updated solution and I'm getting an error mismatch.PNG
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

0
GrahamSkanRetiredCommented:
Does that happen on line 15?
It is difficult to test because there needs to be a database with a query or table to match as well as a Word temp;late with the appropriate bookmarks.
Incidentally, it looks like the sort of task that can be done with Mail Merge
0
GrahamSkanRetiredCommented:
We have to specify the Word library for the the Document, because there is also a Document class in Access.
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

I now notice that the data comes from two queries as well as from fields on a form, which would make it difficult to implement with Mail Merge.
0
Brandon GarnettAuthor Commented:
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

0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Hi Brandon,

if there is no value, it cannot be compared to anything, or assigned to anything.  There are other issues ... just that perhaps you didn't see them because there is not no data there.

rather than this:
IIf(rs!Building > "", rs!Building, ...

do this:
rs!Building & "" <> ""
or
not IsNull( rs!Building )

~~~
before the second "Set rs = ...", the first rs should be closed.
   rs.close

Open in new window


~~~
instead of using CurrentDb, it is better to set a database variable to CurrentDb and continue to use that.
   dim db as dao.database
   set db = CurrentDb
   '... code like db.OpenRecordset
   'clean up
   set db = nothing

Open in new window


to make Graham's procedure more general, for any kind of data, or no data, perhaps make the data passed a variant like this:
Sub FillData(wDoc As Word.Document, vText As Variant, strBookMarkName As String)
    If IsNull(vText)  Then
        wDoc.Bookmarks(strBookMarkName).Range.Paragraphs.First.Range.Delete
    Else
        wDoc.Bookmarks(strBookMarkName).Range.Text = vText
    End If
End Sub

Open in new window

for late binding:
Sub FillData(wDoc As Object, vText As Variant, strBookMarkName As String)

Open in new window


Then perhaps use this procedure for all the data?

have an awesome day,
crystal
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Brandon GarnettAuthor Commented:
Awesome! Thanks for both of your help. Seems to be working now
0
Brandon GarnettAuthor Commented:
Thanks again
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.