Mail Merge help using Access query

[Bobbinbobwhite]

Not an expert at either or Access or VBA, but attempting to learn as I go...

I am using the Albert Kallal Word Mail Merge code and have gotten stuck. I am using a stored/saved query to open the form. The recordset that query creates is the one I need to use for the mail merge "ALL" records. I have been unsuccessful in replacing the strSQL with my stored query or defining the current record set to use instead of a strSQL. I have been searching on this site and other locations for a few days and been unable to figure it out on my own. Any links to existing posts or guidance would be greatly appreciated.

**Solution was to create a temp table and use that in the select statement below**


Private Sub cmdMergeAll_Click()

Me.Refresh
MergeAllWord ("select * from Contacts")

Note that you can use a condition in the above sql

End Sub

Which calls:

Public Function MergeAllWord(strSQL As String, _
Optional strDir As String = "Word", _
Optional bolFullPath As Boolean = False, _
Optional strOutPutDoc As String, _
Optional bolShowDelete As Boolean = True)


Original code link:
http://www.kallal.ca/msaccess/msaccess.html

 

[pbaldy]

Two thoughts:

MergeAllWord ("QueryName")

MergeAllWord ("select * from QueryName")

 

[Bobbinbobwhite]

I tried both of those..... get errors on both stating that the Sql is incorrect or no data was created. Error message is generated by the called code and states: No Data was created for this merge Make sure the sql is correct sql was "qyrInvoices" or sql was "select * from qryInvoices"

The code is looking for strSQL can you use the query instead of a table in the from portion of the select statement?

 

[Gasman]

I have just created a query called Query3 which has

SELECT Contacts.*, Contacts.ContactID
FROM Contacts
WHERE (((Contacts.ContactID)=2));

and used

Mergeallword("Query3")

in the immediate window.
That allowed me to move to selecting the word template.?
then tried

Mergeallword("SELECT Contacts.*, Contacts.ContactID FROM Contacts WHERE (((Contacts.ContactID)=2))")

again in the immediate window and that worked as before.
Even

Mergeallword("SELECT * from query3")

which still worked.

So start by making sure your query produces data and work from there.?

HTH

I tired both of those..... get errors on both stating that the Sql is incorrect or no data was created. Error message is generated by the called code and states: No Data was created for this merge Make sure the sql is correct sql was "qyrInvoices" or sql was "select * from qryInvoices"

The code is looking for strSQ can you use the query instead of a table in the from portion of the select statement?

 

[Bobbinbobwhite]

I know the query generates data because that is what opens the form the command button this code is the event onclick for.

This is a query that I created using the Access query tool and have not converted to vba because it has several joins and parameters.

As you may have guessed, I am in a little over my head here

 

[Gasman]

Well option two is out, the string would be too unweildy.
Option 1 and 3 are essentially the same.
What happens if you use the immediate window as I did.?

Can you zip and post the db?

I know the query generates data because that is what opens the form the command button this code is the event onclick for.

This is a query that I created using the Access query tool and have not converted to vba because it has several joins and parameters.

As you may have guessed, I am in a little over my head here

 

[pbaldy]

If a recordset is being opened on it, form parameters would throw an error. Easiest solution is to wrap each form reference in the Eval() function.

 

[Gasman]

It is

Set rstOutput = CurrentDb.OpenRecordset(strSql, , dbSeeChanges)

If a recordset is being opened on it, form parameters would throw an error. Easiest solution is to wrap each form reference in the Eval() function.

 

[isladogs]

At least 10 years ago, I added Albert Kallal's code to one of my apps.
I had trouble with it at first & emailed him to get a few pointers to solve the issues.
Once I got it working, its been trouble free since ...so I've forgotten the intricacies of his code ...

I just looked at my MergeAllWord function. The header section is

Public Function MergeAllWord(strSQL As String, _
                     Optional strDir As String = "Word[COLOR="Red"][B]\[/B][/COLOR]", _
                     Optional bolFullPath As Boolean = False, _
                     Optional strOutPutDoc As String) As Boolean

Notice the backslash shown in RED
Of course he may have updated it since but its worth checking

I then have code like this:

strSQL = "SELECT * FROM tblLetterTextTEMP;"

MergeAllWord strSQL, "MergeTemplates"

Or in another instance ....!

strSQL = "SELECT PupilData.Surname,PupilData.Forename, PupilData.YearGroup, PupilData.TutorGroup, " & _
    " PDetentionLists.DetentionID, PDetentions.DetentionType, PDetentions.DetentionDate, PDetentions.StartTime, PDetentions.EndTime," & _
    " Format([PDetentions]![DetentionDate],'dddd d mmm yyyy') AS DetDate, PDetentions.Year, PDetentions.Dept," & _
    " PDetentions.House, Faculties.Description AS Subject, PDetentions.RoomID, PDetentionLists.RecordNumber, " & _
    " PDetentionLists.Present, PDetentionLists.WorkToBeDone, PDetentionLists.WorkCompleted, " & _
    " PDetentionLists.Workstation, PDetentionLists.PupilID, PDetentionLists.GivenBy," & _
    " [Teachers]![Title] & ' ' & [Teachers]![Initials] & ' ' & [Teachers]![Surname] AS TeacherName," & _
    " PDetentionLists.Comment, PDetentionLists.DetentionTransferredTo, PDetentionLists.BehaviourLevel," & _
    " PDetentionLists.Moved, PDetentionLists.FollowUp, PDetentionLists.Excluded, PDetentionLists.Reason," & _
    " PDetentionLists.Letter, StudentAddresses.[Parental Salutation], StudentAddresses.AddressLine," & _
    " StudentAddresses.AddressBlock " & _
    " FROM Faculties RIGHT JOIN ((((PupilData INNER JOIN PDetentionLists ON PupilData.PupilID = PDetentionLists.PupilID)" & _
    " INNER JOIN PDetentions ON PDetentionLists.DetentionID = PDetentions.DetentionID)" & _
    " INNER JOIN Teachers ON PDetentionLists.GivenBy = Teachers.TeacherID)" & _
    " INNER JOIN StudentAddresses ON PupilData.PupilID = StudentAddresses.PupilID)" & _
    " ON Faculties.FacultyID = PDetentions.Dept" & _
    " WHERE (((PDetentionLists.DetentionID)= " & intDetention & ") AND ((PDetentionLists.Letter)=Yes));"
         
        
MergeAllWord strSQL, "DetentionTemplates"

SIMPLES!!!

I don't use a recordset anywhere with this feature

 

[Bobbinbobwhite]

I have tried to copy and "fix" the sql query to the vb code to use, but get errors there too!!

I have attached the db for you to look at. The form that I am trying to use this code on is "qyrInvoices" sorry, I haven't renamed it yet... it has the same name as the query that I used the wizard to create the new form. :banghead:Removed attachment after solution found

The criteria I recommend when opening the form is start date 01/01/2017, end date today, Payment type 1

Thanks!!

 

[pbaldy]

I don't use a recordset anywhere with this feature

Albert's code does.

 

[isladogs]

True.
I meant in the code I use applying this feature....

 

[pbaldy]

My point is if you pass a query name to it that has parameters, you'll get the usual "Too few parameters..." error. The Eval() function I mentioned is the simplest way I know of around that.

 

[Bobbinbobwhite]

If a recordset is being opened on it, form parameters would throw an error. Easiest solution is to wrap each form reference in the Eval() function.

Not sure what you mean here....

 

[pbaldy]

Not sure what you mean here....

You can wrap parameters in the Eval() function:

Eval('Forms!FormName.TextboxName')

 

[Bobbinbobwhite]

Unfortunately all of the parameters are from queries not the form, should I still consider using Eval?

 

[isladogs]

Unfortunately all of the parameters are from queries not the form, should I still consider using Eval?

I don't do so.

 

[pbaldy]

Most of us use forms to gather user input, you have a lot more control. You can try it though:

Eval('[Enter blah]')

 

[Bobbinbobwhite]

I finally decided to use a temp table to hold the results of the query and do a select statement on that table to make the mail merge code work.