Mailmerge headaches

[Joseppi]

First of all, let me apologize up front if this should be two separate threads.

I have a complicated database that I use for my section to record and manage all of the IS Security incidents at work.

As part of our day-to-day operations, we are required by policy to submit a formal MS-Word report for each incident. The template for this report has been issued to us and we are mandated to use it.

I have managed to capture all of the required data in an access 2003 database and need to now perform a mail-merge. Within my main database, I have created a Make-Table query to export individual records to a separate database in order to use it as the data source.

Dilemma # 1:

Access 2003 and Word 2003 seems to include a security patch that automatically breaks the data-source link so my users now have to re-connect each and every-time they use the mail-merge function.

If I open my mail merge template directly from Word, it connects automatically to the data-source but if I try to invoke the mail-merge from my main database, it breaks the connection.

Is there a way to successfully program the opening of the data-source?

Here is the code I have been playing with but with very limited success:
Private Sub cmdISSIH_Click()
On Error GoTo Err_ISSIH_Click

Dim stDocName As String
Dim oApp As Object
stDocName = "qryISSIH"
DoCmd.SetWarnings False
DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.SetWarnings True
Set oApp = CreateObject("Word.Application")
oApp.ChangeFileOpenDirectory "N:\Database\Mail Merge Templates"
oApp.Documents.Open "N:\Database\Mail Merge Templates\ISSIR2.doc"
'oApp.ActiveDocument.MailMerge.OpenDataSource
'Name:="N:\Database\Mailmerge.mdb", _
'LinkToSource:=True, AddToRecentFiles:=False, _
'Connection:="TABLE tblMailMerge", _
'SQLStatement:="SELECT * FROM `tblMailMerge`", _
'SubType:=wdMergeSubTypeAccess
oApp.Visible = True

Exit_ISSIH_Click:
Exit Sub

Err_ISSIH_Click:
MsgBox Err.Description
Resume Exit_ISSIH_Click
End Sub


Dilemma # 2

The word template that we have to complete uses a lot of check-boxes.

My table uses Yes/No fields and the input form is setup in a similar fashion but the data stored in the table is a -1 or True and O or False. When brought into the form as a merged field, what I get in place of the check-boxes is the word true or false.

How do you convert a True value to a checked box and/or a False Value to an empty box?

I look forward to some enlightenment

 

[shadow9449]

You may have answered this but is there a reason they demand a Word document rather than a report that you can generate in the Access application and then export as RTF (which would probably look similar to the Word document and you can open it in Word) or as a PDF. In general, I like submitting documents as PDF.

SHADOW

 

[Joseppi]

What can I say? It's government department and they want things done their way.

 

[shadow9449]

What can I say? It's government department and they want things done their way.

You should have said that it's government! That way nothing needs to make sense.

How about exporting a report as RTF? That way it's openable by Word.

SHADOW

 

[Joseppi]

After reading your question, I started designing the form as an Access report with the intent of exporting it into RTF format for further editing and filing purposes.

Question:
Is there a way to automate the process so that a user can click on a command button on the main form and have the report automatically saved into a pre-specified folder with a defined filename based on the case number and report-type?

Example 1:

User fills out Case # 09-0500 and selects the Initial Report Box. User then clicks on the Create Report Button and it automatically generates the report and saves it into the defined folder (N:\Incidents\ISSIs\) as 09-0500_initial.rtf

Example 2:

User revises Case # 09-0500 and changes the Initial Report Box to Final Report. User then clicks on the Create Report Button and it automatically generates the report and saves it into the defined folder (N:\Incidents\ISSIs\) as 09-0500_final.rtf

Notes:
FieldName for Case # is [CaseNumber]
FormName is frmIncidents
Query used to generate data is qryISSIH

Thanks in advance.

 

[shadow9449]

Do a search for the OutputTo command and all the questions will be answered.

SHADOW