Access Record to Mail Merge using Cmd Button

[abby2741]

I recently found this code in the FAQs. But I am over my head in deciphering why my code is getting hung up at opening Word. I hit the "merge it" command button from a db record to send corresponding query info to a Word merge doc. The only thing that happens is Word is opened (no new doc just the application itself).
And then when I go back into access and look at the query which is the data source for the merge doc, Parameter error comes up for a few fields.
I am trying to self-learn access and really do look to this site for most of my little problems, but this one is bigger than me right now!

Thanks for you help and support!
I have two tables: Log & RPSEmployees
1 query (Cover Letter Today) which criteria is Date() and enters employee details based on 1 field in Log table and is the source data for WORD

Here is a copy of the code I have:

Option Compare Database
Option Explicit
Private Sub SetQuery(strQueryName As String, strSQL As String)
On Error GoTo ErrorHandler
'set the query from which the merge
' document will pull its info
Dim qdfNewQueryDef As QueryDef
Set qdfNewQueryDef = CurrentDb.QueryDefs(strQueryName)
qdfNewQueryDef.SQL = strSQL
qdfNewQueryDef.Close
RefreshDatabaseWindow
Exit Sub
ErrorHandler:
MsgBox "Error #" & Err.Number & " occurred. " & Err.Description, vbOKOnly, "Error"
Exit Sub
End Sub

Private Sub cmdMergeIt_Click()
'creates an SQL statement to be used in the query def
On Error GoTo ErrorHandler

' user enters today's date in a text box on the form;
' the query's criteria is set to pull records for
' todays date

Dim strToday As String
strToday = txtToday.Value
Dim strSQL As String
'replace the SQL statement below with the SQL statement
'from your query. This sample shows how to use single quotes
'to incorporate string values from the form's fields
'into the SQL statement. For dates, use # instead of the
'single quotes

strSQL = "SELECT Log.Title, Log.FirstName, Log.LastName, Log.Company, Log.Address1, Log.Date, Log.Address2, Log.CityStateZip FROM Log WHERE Log.Today = # " & strToday & " # "

Dim strDocumentName As String
'name of the Word template document
strDocumentName = "K:\v1.doc"
'use your template document name above

Call SetQuery("Cover Letter Today", strSQL)
'use your query name above
Dim strNewName As String
'name to use when saving
'the merged document
'this next line of code makes the document name pattern
'like this: Custom Labels January 11, 2005.doc

strNewName = "Log.AdminNo" & "Cover Letter" & Format(CStr(Date), "mmddyyyy")
'use your file name pattern above
Call OpenMergedDoc(strDocumentName, strSQL, strNewName)
Exit Sub
ErrorHandler:
MsgBox "Error #" & Err.Number & " occurred. " & Err.Description, vbOKOnly, "Error"
Exit Sub
End Sub

Private Sub OpenMergedDoc(strDocName As String, strSQL As String, strMergedDocName As String)
On Error GoTo WordError
'opens an instance of Word, opens a merge template which has its data source
'already linked to a query in the database,
'optional code merges the template to a new document,
'saves the merged file with a descriptive name,
'then closes the merge template
'Set the directory for any labels generated
Const strDir As String = "S: \Contacts"
'use your directory and folder name above
Dim objWord As New Word.Application

Dim objDoc As Word.Document
objWord.Application.Visible = True
Set objDoc = objWord.Documents.Open(strDir & strDocName)

' Make Word visible so that if any errors occur,
' you can close the instance of Word manually

objWord.Application.Visible = True
'*optional code to merge to a new document, save the merged document,
'and close the template goes here*
'release the variables

Set objWord = Nothing
Set objDoc = Nothing
Exit Sub
WordError:
MsgBox "Err #" & Err.Number & " occurred." & Err.Description, vbOKOnly, "Word Error"
objWord.Quit
End Sub

 

[PHV]

You're trying to open a document named "S: \ContactsK:\v1.doc"


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886