Unable to Repeat Eent Procedure Without DB Restart (1 Viewer)

[ro801]

hi
I've got this code working just the way i want (with help from some clever guys on here). There is one tiny issue however, whenever i run it and it completes successfully; i'm unable to repeat it without first having to close the DB. Can anyone see anything i should add (or take away)? If not, can i build a close app function onto the end of it? (Not ideal but would prevent my colleaugues becoming frustrated with it.
This is the code i'm using:

Private Sub InitialLetter_Click()
On Error GoTo MergeButton_Err
Dim objWord As word.Application

'Start Microsoft Word.
Set objWord = CreateObject("Word.Application")
With objWord
'Make the application visible.
.Visible = True
objWord.Activate

'Open the document.
.Documents.Open ("C:\Users\OR\Desktop\Initial Document.doc")

'Move to each bookmark and insert text from the form.
.ActiveDocument.Bookmarks("Titlea").Select
.Selection.Text = (CStr(Forms!DCCVFORM!TITLE))
.ActiveDocument.Bookmarks("FirstName").Select
.Selection.Text = (CStr(Forms!DCCVFORM!FirstName))
.ActiveDocument.Bookmarks("SurNamea").Select
.Selection.Text = (CStr(Forms!DCCVFORM!SurName))
.ActiveDocument.Bookmarks("ADD1").Select
.Selection.Text = (CStr(Forms!DCCVFORM!ADDRESS1))
.ActiveDocument.Bookmarks("TOWN").Select
.Selection.Text = (CStr(Forms!DCCVFORM!ADDRESSTOWN))
.ActiveDocument.Bookmarks("COUNTY").Select
.Selection.Text = (CStr(Forms!DCCVFORM!ADDRESSCOUNTY))
.ActiveDocument.Bookmarks("POSTCODE").Select
.Selection.Text = (CStr(Forms!DCCVFORM!POSTCODE))

.ActiveDocument.Bookmarks("DCCVUN").Select
.Selection.Text = (CStr(Forms!DCCVFORM!DCCVUN))
.ActiveDocument.Bookmarks.Add Name:="DCCVUN", Range:=Selection.Range
.ActiveDocument.Bookmarks("DCCVCASENUMBER").Select
.Selection.Text = (CStr(Forms!DCCVFORM!DCCVCASENUMBER))
.ActiveDocument.Bookmarks.Add Name:="DCCVCASENUMBER", Range:=Selection.Range
.ActiveDocument.Bookmarks("UN").Select
.Selection.Text = (CStr(Forms!DCCVFORM!UN))
.ActiveDocument.Bookmarks("NHSNO").Select
.Selection.Text = (CStr(Forms!DCCVFORM!NHSNo))
.ActiveDocument.Bookmarks("DOB").Select
.Selection.Text = (CStr(Forms!DCCVFORM!DOB))

.ActiveDocument.Bookmarks("TITLE").Select
.Selection.Text = (CStr(Forms!DCCVFORM!TITLE))
.ActiveDocument.Bookmarks("SURNAME").Select
.Selection.Text = (CStr(Forms!DCCVFORM!SurName))

.ActiveDocument.Bookmarks("GP").Select
.Selection.Text = (CStr(Forms!DCCVFORM!GP))
.ActiveDocument.Bookmarks("GPFIRSTLINE").Select
.Selection.Text = (CStr(Forms!DCCVFORM!GPFIRSTLINE))
.ActiveDocument.Bookmarks("GPSECONDLINE").Select
.Selection.Text = (CStr(Forms!DCCVFORM!GPSECONDLINE))
.ActiveDocument.Bookmarks("GPTOWN").Select
.Selection.Text = (CStr(Forms!DCCVFORM!GPTOWN))
.ActiveDocument.Bookmarks("GPCOUNTY").Select
.Selection.Text = (CStr(Forms!DCCVFORM!GPCOUNTY))
.ActiveDocument.Bookmarks("GPPOSTCODE").Select
.Selection.Text = (CStr(Forms!DCCVFORM!GPPOSTCODE))

Dim pStr As String
pStr = "C:\Users\OR\Desktop\"
pStr = pStr + ActiveDocument.Bookmarks("DCCVUN").Range.Text
pStr = pStr + "-"
pStr = pStr + ActiveDocument.Bookmarks("DCCVCASENUMBER").Range.Text
pStr = pStr + "-Initial Letter"
pStr = pStr + "-"
pStr = pStr & Format(Now(), "ddmmyy-hhnnss")
ActiveDocument.SaveAs FileName:=pStr

MergeButton_Err:
'If a field on the form is empty, remove the bookmark text, and
'continue.
If Err.Number = 94 Then
objWord.Selection.Text = ""
Resume Next
End If

Exit Sub
End With
End Sub

Thanks in advance

 

[ro801]

Just to clarify; when i attempt to repeat the event; the document opens OK but hangs on 'DCCVUN' and will not complete. I have to close the Word document without saving it, close the DB, restart and redo the event... this time it works great.
Look forward to hearing from someone

 

[Beetle]

You need to close the Word object before you exit the procedure;

objWord.Close
Set objWord = Nothing

 

[ro801]

You need to close the Word object before you exit the procedure;

objWord.Close
Set objWord = Nothing

Thanks for your reply
I think i successfully quit the word instance. It still didn't help. I think the error occurs as i try to re-bookmark the fields in order to use those fields in the filename using:
.ActiveDocument.Bookmarks.Add Name:="DCCVCASENUMBER", Range:=Selection.Range

and

.ActiveDocument.Bookmarks.Add Name:="DCCVUN", Range:=Selection.Range

respectively.

If i take these out, i can successfully reopen successive word documents, but obviously i won't get the filename i want.
Can anyone suggest a fix for this? Its frustrating to say the least

 

[sparks80]

Hi,

I am not sure if this will resolve the problem but I cannot see anywhere that you close the document. I would be inclined to use ActiveDocument.Close after you save it.

Secondly I think you have to use Application.Quit rather than Application.Close.

try this:

pStr = pStr & Format(Now(), "ddmmyy-hhnnss")
ActiveDocument.SaveAs FileName:=pStr

ActiveDocument.Close SaveChanges:=False
objWord.Quit
Set objWord = Nothing

I tried this from Word, but I think automation from Access should work the same way.

 

[ro801]

Hi,

I am not sure if this will resolve the problem but I cannot see anywhere that you close the document. I would be inclined to use ActiveDocument.Close after you save it.

Secondly I think you have to use Application.Quit rather than Application.Close.

try this:

pStr = pStr & Format(Now(), "ddmmyy-hhnnss")
ActiveDocument.SaveAs FileName:=pStr
 
ActiveDocument.Close SaveChanges:=False
objWord.Quit
Set objWord = Nothing

I tried this from Word, but I think automation from Access should work the same way.

Many thanks for your response
I tried:
objWord.Quit
Set objWord = Nothing

The same thing happened, i don't think the issue lies here. I think the issue lies with either re-inserting the bookmark and referencing it in the filename or re-using 'DCCVUN' and/or 'DCCVCASENUMBER' -both of which are linked in a one-to-many relationship. As it stands this is only resolved with closing and re-opening Access; i have tried refreshing the form and closing and re-opening the form to no avail. Arghhh this is typical as i was liking how it was working before this! Any other suggestions?

 

[sparks80]

Hi,

Having a closer look at the code I am a bit puzzled. It looks like you select a bookmark and change the text which makes sense. The next line appears to be trying to create a new bookmark with the same name.

Could you explain what you are trying to do here and maybe we can find an alternative way, or prevent the error that is preventing the code from running again???

' This is the bit I don't understand!!
.ActiveDocument.Bookmarks("DCCVUN").Select
.Selection.Text = (CStr(Forms!DCCVFORM!DCCVUN))
.ActiveDocument.Bookmarks.Add Name:="DCCVUN", Range:=Selection.Range

 

[ro801]

Hi,

Having a closer look at the code I am a bit puzzled. It looks like you select a bookmark and change the text which makes sense. The next line appears to be trying to create a new bookmark with the same name.

Could you explain what you are trying to do here and maybe we can find an alternative way, or prevent the error that is preventing the code from running again???

' This is the bit I don't understand!!
.ActiveDocument.Bookmarks("DCCVUN").Select
.Selection.Text = (CStr(Forms!DCCVFORM!DCCVUN))
.ActiveDocument.Bookmarks.Add Name:="DCCVUN", Range:=Selection.Range

Thanks again for your time on this.
I wanted to reference a bookmark, change it's content, then reinstate the bookmark in order to reference it later in the code for use in the the filename. The end result of the code for example (when working properly) is a filename 123456-33-InitialLetter-Date-Time.
The DCCVUN and DCCVCASENUMBER fields are vital for ensuring patient specific yet unique filenames.
Many Thanks

 

[sparks80]

Hi,

I have searched on Google and found this, which does what you are after. The only way this differs from your code is that it creates a range object first, and then changes the text of the range object, rather than selection.

I had not appreciated that the bookmark is deleted when overwriting the text, so you definitely need to add a new bookmark if you want it to remain. It is possible that using a defined range rather than the selection will help, but I'm not convinced!

Credit for the code goes to Dave Rado at word.mvps.org, and the web page is here: http://word.mvps.org/faqs/macrosvba/InsertingTextAtBookmark.htm

[COLOR=#00007f]Dim[/COLOR] BMRange [COLOR=#00007f] As Range[/COLOR]  
 [COLOR=#007f00]'Identify current Bookmark range and insert text  
[/COLOR]  [COLOR=#00007f]  Set[/COLOR] BMRange = ActiveDocument.Bookmarks("MyBookmark").Range  
 BMRange.Text = "Hello world"  
 [COLOR=#007f00]'Re-insert the bookmark  
[/COLOR]  ActiveDocument.Bookmarks.Add "MyBookmark", BMRange

Or alternatively you can use BMRange.Text to create the filename later in your code, and this may negate the need to re-instate the bookmark if I have understood correctly.