SaveAs Code Maybe? Mail Merge

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rodger - 07 Jun 2007 00:12 GMT

Hello All,

I am not sure this is the correct form, if not maybe someone can tell me
where I should post.

Here is my goal.

I have an Access database that opens up several or just one Word Document(s)
depending on which document(s) they select. I have my queries in Access as
the Data Source. Depending on which icon the user click the report either
opens in Word or prints directly to the printer. This all works great as
long as only one user opens Word or Prints. If more than one user tries to
open Word or Print they get errors that access cannot open the temp table I
create at the time the user opens/prints the document. I had two thoughts
that could fix this issue, but I am not sure as I am writing this that they
will work.

Option 1: Make the Word Documents a Template. I must say I did try this
but when I run my code it just open the Template and not a new document.

Option 2: Somehow have the code do a SaveAs before it opens the Document.
But again the document still has a connection to the database.

So maybe I need a third option.

Option 3: Actually perform the mail merge so the new document does not have
a connection to the database. If I do this I want to also Save the new
document with maybe the Customers Name and the Document Name. OK if this is
the option that I need to do. Does anyone have an example?

TIA,
Rodger

Reply to this Message

Cindy M. - 08 Jun 2007 11:03 GMT

Hi Rodger,

> I have an Access database that opens up several or just one Word Document(s)
> depending on which document(s) they select. I have my queries in Access as
[quoted text clipped - 9 lines]
> but when I run my code it just open the Template and not a new document.
>

Documents.Add instead of Documents.Open will create a new document from the
template. But I don't think that will solve your issue.

> Option 2: Somehow have the code do a SaveAs before it opens the Document.
> But again the document still has a connection to the database.
[quoted text clipped - 5 lines]
> document with maybe the Customers Name and the Document Name. OK if this is
> the option that I need to do. Does anyone have an example?

This may be possible, but the issue can still occur if two users try to work at
the same time.

I think the problem is the permissions in your database. Can you tell me what
kind of connection method is used to link the data source to the documnts? If
you can't, tell us the version of Word being used and show us the
OpenDataSource method you're using.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail :-)

Reply to this Message

Rodger - 11 Jun 2007 01:22 GMT

I am using DDE to open the Word Documents. Access 2000 format and Word XP.

I think what I need to do is just have Access open the word Document and
preform the mail merge to a new document then there would not be a "hold" on
the document or database when another user runs the reports or goes into
access to add or edit a record.

'START CODE
'******************************************************************************************************
Private Sub myReports(myView)

On Error GoTo Err_myReports

Dim ReportPath
Dim oApp As Object, DocName, x, myDB
Dim dbsCurrent As Database, dbsPath As String
Dim myMessage, myStyle, myTitle, myResponse
Dim myQRY7, myQRYRealEstateAppraisal, myQRY1244_Step1, myQRY1244_Step2,
myQRY1244B_Step1
Dim myQRYTrackingSheet, myQRY_TEMP_OWNER_GUARANTOR
Dim myID

Set myDB = CurrentDb()

myID = [Forms]![frm_Reports]![cmbLoan]

If IsNull(Me.cmbLoan) Then
myMessage = "Please select a loan"
myTitle = "No Selection"
myStyle = vbCritical + vbOKOnly
myResponse = MsgBox(myMessage, myStyle, myTitle)
Exit Sub
Else
DoCmd.SetWarnings False
Call updateTEMP_OWNERS
***********************************
There are a lot of SQL statements here that I deleted as they are not part
of this question.
***********************************
DoCmd.SetWarnings True
End If

If IsLoaded("frm_setup") Then
Else
DoCmd.OpenForm "frm_setup", acNormal, , , , acHidden
End If

ReportPath = Forms![frm_Setup]![SET_CURRENT_DATABASE]

Set oApp = CreateObject("Word.Application")

'PRINT REPORTS
'EXHIBIT 1

If Me.ckbExhibit1 = -1 Then
Call updateTEMP_OWNERS

oApp.Documents.Open FileName:="" & Chr(34) & ReportPath &
"\Exhibit 1.doc" & Chr(34)
If myView = 1 Then
oApp.PrintOut
Else
oApp.Visible = True
End If
End If

'**************************************************************************************************
'END CODE

I repeat the above lines for the rest of the reports that are on the form
that this is called from.

What I need to know is how to sent the Document to a new mail merege and
also if I send it to the printer does it close its self? Or do I need to
close oApp?

> Hi Rodger,
>
[quoted text clipped - 54 lines]
> reply
> in the newsgroup and not by e-mail :-)

Reply to this Message

Doug Robbins - Word MVP - 11 Jun 2007 04:36 GMT

See how it is done in the database that you can down load by clicking on the
appropriate link in the
Super Easy Word Merge item of fellow MVP Albert Kallal's website at:

http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html

Signature

Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

>I am using DDE to open the Word Documents. Access 2000 format and Word XP.
>
[quoted text clipped - 132 lines]
>> reply
>> in the newsgroup and not by e-mail :-)

Reply to this Message

Cindy M. - 11 Jun 2007 20:47 GMT

Hi Rodger,

> I am using DDE to open the Word Documents. Access 2000 format and Word XP.
>
> I think what I need to do is just have Access open the word Document and
> preform the mail merge to a new document then there would not be a "hold" on
> the document or database when another user runs the reports or goes into
> access to add or edit a record.

Mmm, I wouldn't be so sure...

When you use DDE, Word will tend to open another instance of the Access
application. And if it's doing that in "Exclusive" mode, that would certainly
explain the behavior you're seeing. (I'm assuming that, otherwise, the program
is in "Shared" mode.)

For these reason, as well as speed, it would probably make more sense to use an
ODBC connection method.

In addition, rather opening the main merge documents, I'd duplicate the *.doc
files (use the Documents.Add method). This way, you don't run the risk of one
user locking another one out. Close the main merge document you create this way
without saving once the merge has executed.

Dim mainMergeDoc as Word.Document
Set mainMergeDoc = oApp.Documents.Add(FileName:="" & Chr(34) & ReportPath &
"\Exhibit 1.doc" & Chr(34))

'do other stuff. Execute the merge. Then
mainMergeDoc.Close SaveChanges:=wdDoNotSaveChanges
Set mainMergeDoc = Nothing

What you don't want to do is this:
oApp.PrintOut

This will not execute the merge. At this point, the main merge document is - at
best - in "preview" mode. You should set the mainMergeDoc.MailMerge.Destination
to a new document or the printer. And then you should execute the merge. At
this point, if you have exclusive access to Word (you can be sure the user
isn't going to try to do something and no other documents are open), then

wordApp.ActiveDocument

is going to be the merge result document if you've chosen a new document as the
.Destination. Of course, if you send directly to the printer, you don't have to
worry about this.

You can continue to use oApp until all your reports are processed. Then
oApp.Quit SaveChanges:=wdDoNotSaveChanges
Set oApp = Nothing

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail :-)

Reply to this Message