Exporting a Parameter query to a word template

[GrahamF]

Hi Folks,

I'm new to VBA so if I'm asking something simple please forgive.

Here's the story I've got a Parameter query that I use to generate a report in Access.

What I would like to do is the following:

1. Have a user click on a button to run the paramater query.

2. After the parameter query is entered that the information is exported to a word template and a new document is opened containing the query/report information exported from the database.

I've set up a word template with bookmarks that I wish to use for the query/report, the bookmark names are the same as the tables & fields on my query but I've been unable to find out how to link them up from Access.


Is this possible and would anyone have an example of some code.


Thanks in advance.

Graham.

[lynn_victoria]

Not a problem. The easiest way is to go to www.helenfeddema.com (http://www.helenfeddema.com) and download the example Merging to Word four ways example. It is great, and I use it to merge to Word.
It is very clear, but if you need help, please ask.
victoria

[GrahamF]

Lynn thanks for that, sorry about the late reply I was out off site yesterday I'll visit the above site today and if I need help I'll ask.

[GrahamF]

Hi Victoria,:hi:

Thank you for the link the material was V.Helpfull, however I still can't set my parameter query as the data record that I want my report to be generated from, I've tried other code found on the net but with each one I still get errors.

When I run the query from Access I have to enter a staff members name into the parameter box which checks if the staff member listed in a field called Staff_Name has any outstanding training scheduled. The Query is called OutstandingTrainingRecordForStaffMember

Could you please tell me how I set the query as the recordset for the report, once referenced I'll have to extract the following data from the query for the report, Staff_Name Depart(Department), Course_Name, Start_Date and End_Date.

To extract this info I'm guessing that I set reference to them as such
DIM strCourse_Name As String

Again Thank you for the link the help you've given me so far.


Graham.

(p.s. I may be tomorrow before I get to reply but will do so asap.)

[Pat Hartman]

To use a query as the RecordSource for a report, simply open the report in design view and change the RecordSource propety to select the appropriate query.

Word (and other applications) cannot see queries that rely on VBA or Access specific objects such as user defined functions or form fields. You either need to run them as make-table queries and reference the temp table from Word or you need to completely automate the interaction from Access to Word so that the query with the "Access" object runs within Access and then the data is passed piecemeal to Word.

[GrahamF]

Hi Pat,

I have already set the query as the recordsource for the report in Access.
What I'm looking for is to design a procedure that runs when the query is run and the report is generated in Access so that the information generated by the query is pushed out into a word template.

Can this be done?

I won't be able to use make table queries as the users will not have rights to the Access Tables.

Thanks for your input,


Graham

[GrahamF]

Hi folks,


I've changed track abit and was wondering if you could give me abit of help.

I'm now running the query I need for my report through a form. On the form, I have a button behind which the following code is located:











Private Sub Command10_Click()



On Error GoTo ErrorHandler



Dim docs As Word.Documents

Dim strWordTemplate As String

Dim strDocsPath As String

Dim strTemplatePath As String

Dim prps As Object

Dim strShortDate As String

Dim strLongDate As String

Dim strTest As String

Dim strSaveName As String

Dim strTestFile As String

Dim intSaveNameFail As Boolean

Dim i As Integer

Dim strSaveNamePath As String



'Check for required address information

strTest = Nz(Me![StaffName])

If strTest = "" Then

MsgBox "Can't send letter -- no staff names!"

GoTo ErrorHandlerExit

End If



strTest = Nz(Me![CourseName])

If strTest = "" Then

MsgBox "Can't send letter -- no course!"

GoTo ErrorHandlerExit

End If



strTest = Nz(Me![Depart])

If strTest = "" Then

MsgBox "Can't send letter -- no department!"

GoTo ErrorHandlerExit

End If



strTest = Nz(Me![StartDate])



strTest = Nz(Me![EndDate])





'Set global Word application variable; if Word is not running,

'the error handler defaults to CreateObject

Set pappWord = GetObject(, "Word.Application")



strLongDate = Format(Date, "mmmm d, yyyy")

strShortDate = Format(Date, "m-d-yyyy")

strSaveName = "Letter to " & Me![StaffName] & " " & Me![Depart]

strSaveName = strSaveName & " on " & strShortDate & ".doc"

strDocsPath = pappWord.Options.DefaultFilePath(wdDocumentsPath) & "\"

strTemplatePath = pappWord.Options.DefaultFilePath(wdUserTemplatesPath)

strWordTemplate = strTemplatePath & "\" & "Future Staff Training.dot"



'Check for existence of template in template folder,

'and exit if not found

strTestFile = Nz(Dir(strWordTemplate))

Debug.Print "Test file: " & strTestFile

If strTestFile = "" Then

MsgBox strWordTemplate & " template not found; can't create letter"

GoTo ErrorHandlerExit

End If



'Check for existence of previously saved letter in documents folder,

'and append an incremented number to save name if found

i = 2

intSaveNameFail = True

Do While intSaveNameFail

strSaveNamePath = strDocsPath & strSaveName

Debug.Print "Proposed save name and path: " _

& vbCrLf & strSaveNamePath

strTestFile = Nz(Dir(strSaveNamePath))

Debug.Print "Test file: " & strTestFile

If strTestFile = strSaveName Then

Debug.Print "Save name already used: " & strSaveName



'Create new save name with incremented number

intSaveNameFail = True

strSaveName = "Letter " & CStr(i) & " to " & _

Me![StaffName] & " " & Me![Depart]

strSaveName = strSaveName & " on " & strShortDate & ".doc"

strSaveNamePath = strDocsPath & strSaveName

Debug.Print "New save name and path: " _

& vbCrLf & strSaveNamePath

i = i + 1

Else

Debug.Print "Save name not used: " & strSaveName

intSaveNameFail = False

End If

Loop



Set docs = pappWord.Documents

docs.Add strWordTemplate



Set prps = pappWord.ActiveDocument.CustomDocumentProperties

prps.Item("StaffName").Value = Nz(Me![StaffName])

prps.Item("Course").Value = Nz(Me![CourseName])

prps.Item("Department").Value = Nz(Me![Depart])

prps.Item("StartDate").Value = Nz(Me![StartDate])

prps.Item("EndDate").Value = Nz(Me![EndDate])





With pappWord

.Visible = True

.Selection.WholeStory

.Selection.Fields.Update

Debug.Print "Going to save as " & strSaveName

.ActiveDocument.SaveAs strSaveName

.Activate

.Selection.EndKey Unit:=wdStory

End With



ErrorHandlerExit:

Set pappWord = Nothing

Exit Sub



ErrorHandler:

'Word is not running; open Word with CreateObject

If Err.Number = 429 Then

Set pappWord = CreateObject("Word.Application")

Resume Next

Else

MsgBox "Error No: " & Err.Number & "; Description: "

Resume ErrorHandlerExit

End If

End Sub



The code works fine if there is only one record in the form, What I need to know is how would I code the procedure so that it loops through the form and collects more than one record (If there) and pass it to the word template.

Any help taken with thanks.

Graham