Get a Single Record From a Form/Query to a Word Document.

[Guest]

Ok Easy right. I guess I'm using the wrong code or something.

I created this test database with 4 fields, ID, FirstName, LastName,
PhoneNumber. ID is the Primary.
I have a Query caled Testing Query.
My problem is that I want to take the data displayed on the Form at that
particular moment, which uses the Query, and export it to the Word Document
via Mail Merge.

I got the Mail Merge to Work, but It always merges the 1st Record in the
table, not the current record.

Everytime I get things situated and I hit my "Print" Button, it modifies my
Query and then I can only see whatever record I happened to have been on at
that time from then on in the form.

I'd also like to make it print automatically, that would be a plus.

Here's what I found on the net and I hacked it up and came up with this:
(Nevermind the creators comments, that helped me figure things out lol)

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 a zip code in a text box on the form;
' the query's criteria is set to pull records for
'that zip code

Dim strID As String
strID = Forms!Testing!ID
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 * FROM Testing WHERE Testing.ID = " & strID & ";"

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

Call SetQuery("Testing Query", 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 = "Custom Labels " & Format(CStr(Date), "MMM dd yyyy")
'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(strDocumentName 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 = "C:\Documents and
Settings\minfinger\Desktop\Mitel Labels"
'use your directory and folder name above
Dim oApp As Object
' Make Word visible so that if any errors occur,
' you can close the instance of Word manually
Set oApp = CreateObject("Word.Application")
oApp.Visible = True
oApp.Documents.Open (strDir & strDocumentName)

'*optional code to merge to a new document, save the merged document, and
close the template goes here*

'release the variables
Set oApp = Nothing

Exit Sub
WordError:
MsgBox "Err #" & Err.Number & " occurred." & Err.Description,
vbOKOnly, "Word Error"
oApp.Quit
End Sub

 

[Albert D. Kallal]

I have a working sample sends the current record to word.

It takes ONE line of code to word enable any ms-access form with my code
example...

You can find the super easy word merge here

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