Using MS Access to open Word doc; need to bold/underline a string

[Perineum]

I open a Word template from Access using VBA. The Word template has form fields. One of the form fields called "Background" is only supposed to be displayed when the text box in the form in Access has something in it. If not, the form field and label should not be displayed in the Word doc. I am Using MS Access and Word 2010. I tried collapsing a bookmark but it did not work so instead, I create the Label called Background and the blank lines in VBA code in Access:



.Bookmarks("txtbackground").Range.Fields(1).Result .Text = vbCr + "Background" + vbCr + vbCr + CleanString(Me.Text466) + vbCr

I found this code online to bold and underline the word Background.

Sub BoldUnderline()
Dim oRng As Word.Range
Set oRng = ActiveDocument.Range
With oRng.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "<Background>"
.Replacement.Text = "^&"
.MatchWildcards = True
.Format = True
.Replacement.Font.Underline = wdUnderlineSingle
.Execute Replace:=wdReplaceAll
End With
End Sub

It worked the first time I used it. Every attempt since has failed. Then I changed it from a "Sub" to a "Function". First attempt it worked. Every subsequent attempt failed. I call the function from another function:

Call BoldUnderline()

The brackets disappear after I type them in. I've tried adding a string to pass but it doesn't work. Here is the function where I create the Word doc and where I call the BoldUnderline function.
Function Fillwordform()
Dim appword As Word.Application
Dim doc As Word.Document
Dim str1, str2 As String
On Error Resume Next
Error.Clear
Path = "C:\Users\....Template6.docm"
Set appword = GetObject(, "word.application")
If Err.Number <> 0 Then
Set appword = New Word.Application
appword.Visible = True
End If
Set doc = appword.Documents.Open(Path, , False)
With doc
.FormFields("txttopdate").Result = Me.Text423
.Bookmarks("txtdescription").Range.Fields(1).Resul t.Text = CleanString(Me.Description)
.
.
.
.Bookmarks("txtbackground").Range.Fields(1).Result .Text = vbCr + "Background" + vbCr + vbCr + CleanString(Me.Text466) + vbCr

Dim check As String
Call BoldUnderline(check)

.Visible = True
.Activate
End With
Set doc = Nothing
Set appword = Nothing
End Function

How do I get the function to run every time? Any help would be greatly appreciated. thanks

[John_G]

When you say "... Every subsequent attempt failed." what do you mean?

Do you get an error message, no error but nothing happens, or what?

Just saying "it doesn't work" does not give us very must to go by. More information, please.

[Perineum]

No error message. On the first attempt, the word Background was underlined and bolded in the Word doc. On every subsequent attempt, Background does not get bolded or underlined, no error.

thanks

[John_G]

I recall I had all kinds of problems using Word from Access.

One thing you might try is explicitly close the Word document, and explicitly terminate the Word application, in addition to setting the objects to Nothing:

Doc.Close wdDoNotSaveChanges
Set Doc = Nothing
Appword.Quit
Set appword = Nothing

I found that if I didn't do that there might be an MS Word application running as a process that you didn't see unless you opened task manager to kill it.


You say it only works in the first attempt; if you close Access and Word, then restart your Access application, does it work again, but only "the first time"?

[Perineum]

Thanks for the reply. If I close Access and Word and try again, it will not work. No error messages.

I added your code to the function Fillwordform. I got errors on "Doc.Close wdDoNotSaveChanges" and "Appword.Quit" saying 'Run-time error 91; Object variable or With block variable not set'. I remmed out those 2 lines. The first time it worked partially and "Background" was underlined but not bold (I added some code since then to bold it). On subsequent attempts, like before, it did not work...no errors. I also tried your code in the function BoldUnderline and in different spots in the first function but nothing worked....no errors either.

Any more ideas would be greatly appreciated.

thanks

[Perineum]

Ok, I add 3 of the 4 lines of your code (see below). I run it and get the same error as described above. I rem out the second line: appword.Quit. Then I run it and it works....Background is bolded and underlined. Subsequent attempts fail, no errors. Then I add back "appword.Quit", it gives an error, I rem out that line, and it will work on the first attempt.
' this function creates the DG Briefing
Function Fillwordform(Path As String)
Dim appword As Word.Application
Dim doc As Word.Document
Dim str1, str2 As String

Set doc = Nothing
appword.Quit
Set appword = Nothing

I added a line for the bold to work:

.Replacement.Font.Underline = wdUnderlineSingle
.Replacement.Font.Bold = wdToggle

Is there something else I can do with appword to make this work permanently?

thanks

[Thompyt]

You might want to add and try for closing the application:

Word.Application.Quit

[John_G]

One thing you might want to check is that the procedure which runs properly the first time is not saving the changed data so that when run later, the search string is not found. You are opening the template file Template6.docm, then making changes to it in your VBA code. What happens next?

Preventing changes from being saved is why I use Doc.Close wdDoNotSaveChanges.

[Perineum]

Thanks for the replies. I tried your suggestions in various places. It either did nothing, or closed the document when I didn't want it to. No errors. After the doc is created, it goes to the function and either adds the bold/underline or not, then it just goes back to the last few lines of the function Fillwordform.

I think I found the problem. I added some watches, stepped through the code and found that when the function works the first time (after fixing the error message as described previously), the value for "ActiveDocument.Range" is <Object variable or With block variable not set>. On subsequent attempts when it does not work, the value is <the remote machine does not exist or is unavailable>. I read that this has to do with unqualified references to Word objects. See code snippet below. I've tried adding Appword, Doc, and Path before ActiveDocument.Range but nothing worked. Any ideas?

Sub BoldUnderline(boldund As String)
Dim oRng As Word.Range
Set oRng = ActiveDocument.Range 'when not working, this is the last line before it exits Sub

many thanks!

[Perineum]

I accessed the DB remotely, connection was disrupted, I lost all the code. I went back to an old version and copied in snippets from this forum and after 3 hours, got the DB working again as it was. Brutal. Anyway, still stuck.

John_G asked: "You say it only works in the first attempt; if you close Access and Word, then restart your Access application, does it work again, but only "the first time"?"

I tried this before but it didn't work. Now, when I close Word and Access, then open up Access and try again, it works the "first time". When I just close Word, but not Access, then create the Word doc, the bold/underline does not work.

So, it seems to have something to do with an unqualified reference for ActiveDocument and/or Access processes running or something. I don't want to close Access each time the Word doc is created.

Any help would be greatly appreciated.

[Perineum]

Solved! I did some googling and found out that ActiveDocument is a sub-object of Word. You have to specify the parent object. So instead of

Set oRng = ActiveDocument.Range
With oRng.Find

I used the variable "doc" instead. In the function Fillwordform the variable was set: Dim doc As Word.Document
I inserted doc instead of ActiveDocument and moved the Sub BoldUnderline to inside the function Fillwordform

.Bookmarks("txtbackground").Range.Fields(1).Result .Text = vbCr + "Background" + vbCr + vbCr + CleanString(Me.Text466) + vbCr
.Bookmarks("txtactionstaken").Range.Fields(1).Resu lt.Text = CleanString(Me.Text337)
.Bookmarks("txtnextsteps").Range.Fields(1).Result. Text = CleanString(Me.Text357)
.FormFields("txtpreparedby").Result = Me.Combo441
.FormFields("txtapprovedby").Result = Me.approvedby
.FormFields("txtprepareddate").Result = Me.Text471

Dim oRng As Word.Range
Set oRng = doc.Range
With oRng.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "<Background>"
.Replacement.Text = "^&"
.MatchWildcards = True
.Format = True
.Replacement.Font.Underline = wdUnderlineSingle
.Replacement.Font.Bold = wdToggle
.Execute Replace:=wdReplaceAll
End With

thanks to those who gave suggestions....it helped me find the solution...