Mail Merge from Access

[ridders]

I’ve been using Albert Kallal's excellent MergeSingleWord for almost a year &
it works like a treat. This is used in a school database to send home
individual letters to parents.

I’m now trying to use the MergeAllWord feature e.g. for letter to whole of a
year group & am having problems:

First I get a “Too few parameters. Expected 1†message followed by error
message “2497: The action or method requires a Report Name argumentâ€
As a result a GuiWordTemplate form isn’t loaded

The code I am using in GoMergeAll is:
________________________________________
Function GoMergeAll()

On Error GoTo Err_GoMergeAll

Me.Refresh

strSQL = " SELECT PupilData.PupilID, PupilData.Surname, PupilData.Forename,
PupilData.Gender, " & _
" PupilData.DateOfBirth, PupilData.YearGroup, PupilData.TutorGroup,
PupilData.Active," & _
" StudentAddresses.[Parental Salutation], StudentAddresses.AddressBlock,
Teachers.TeacherID, " & _
" Teachers!Title & Left(Teachers!Forename,1) & Teachers!Surname AS
TeacherName" & _
" FROM Teachers, PupilData INNER JOIN StudentAddresses ON
PupilData.PupilID = StudentAddresses.PupilID" & _
" WHERE (((PupilData.YearGroup) = [Forms]![SelectReview]![LstYears]) AND
((PupilData.Active) = Yes) AND ((Teachers.TeacherID) = GetCurrentTeacher()))"
& _
" ORDER BY PupilData.Surname, PupilData.Forename;"

Set mydb = CurrentDb
If Nz(strSQL, "") = "" Then Exit Function
Set myset = mydb.OpenRecordset(strSQL)

With myset
If .RecordCount > 0 Then
.MoveLast
.MoveFirst
.MoveNext
'.Close
End If
End With

MergeAllWord ("SELECT * FROM " & strSQL), "Templates"

Exit_GoMergeAll:
Exit Function

Err_GoMergeAll:
MsgBox err.Description
Resume Exit_GoMergeAll

End Function

________________________________________
The SQL works fine for an individual record

I have 2 Word templates in the Templates folder which the MergeAllWord
routine is referencing

I have tried ‘remmimg out’ the Set mydb section but then no data is created
for the merge
I thought I had got rid of the parameter error by declaring Dim mydb As
Database in this module (though it is already defined as a global variable).
However, reopening the database has caused this error to recur.
I have also tried remming the With...End With section but with no luck
I also can’t solve the 2497 error.

Please can anyone point me in the right direction?
Thanks for your help in advance

Colin Riddington

 

[Albert D. Kallal]

You can't use parameters in the actual query. So, save the query WITHOUT the
forms parameters

Then go:

dim strWhere as string
dim strSql as string

strWhere = "(yearGroup = " & me!ListYears & ") and (Active = Yes)" & _
" and (TeacherID = " & getCurrentTeacher() & ")"


strSql = "select * from MyQuery where " & strWhere & _
" order by Surname,Forename"

debug.print strSql

MergeAllWord strSql,"Templates"

So, as a general rule, just built the sql in a query (but without any
parameters). Then, add the parameters as above...

And, if YearGroup is a string, then you have to put quotes around it:

strWhere = "(yearGroup = '" & me!ListYears & "') and (Active = Yes)" & _
" and (TeacherID = " & getCurrentTeacher() & ")"

Note that the debug.print strSql will allow you to see the sql generated in
the debug window. In fact, cut and past the sql that displays in the debug
window, and paste it into a temp query you create...does it run???

Note that the above is only 3 lnes of code...a LOT less then what you
posted....

 

[A a r o n K e m p f]

you need A c c e s s D a t a P r o j e c t s

 

[C Hayes]

I've been using Mr. Kallal's code also, great asset.

Does anyone know how to access if from a toolbar button?