Mail merge from query problem

[nq]

I have a "query by form" that returns a list of people, relevant data fields and their individual email addresses. From the form, I can specify several parameters to send to the query to retrieve specific data. I can then view, export to Word and Excel. This all works correctly.

I also have a need to directly email the people with their specific data from the query. I have searched the forums and found relevant code as shown below:

Public Function SendEmail() As Boolean
Dim olApp As Outlook.Application
Dim oMail As Outlook.MailItem
Dim db As DAO.Database
Dim rec As DAO.Recordset
Dim bodytxt As String
Dim docQuery As String

On Error GoTo HandleErr
Set db = CurrentDb

docQuery = "qryName"

Set rec = db.OpenRecordset(docQuery, dbOpenSnapshot)

On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
etc., etc.

When I click the form button to send the email, the following error is returned:
3061:Too few paramters. Expected 2
I presumed that this was due to the parameter query since there are two paramters on the form that users can change. Removing all paramters from the query solved the problem. However, I need paramters so that I can email different data sets depending on selections on the form.
Any ideas would be greatly appreciated

Nigel.

 

[PHV]

Have a look at the Parameters collection of the DAO.QueryDef object and at the Eval function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[nq]

Thanks for the fast reply PHV
My knowledge of VBA, DAO, etc. is not high, could you please elaborate?

 

[GhostWolf]

docQuery = "qryName

docQuery.Parameters("Parm1").Value=[Value1]
docQuery.Parameters("Parm2").Value=[Value2]

Set rec = db.OpenRecordset(docQuery, dbOpenSnapshot)

 

[nq]

GhostWolf.
Thank you for the insight. I tried your suggestion and got error messages. After some more searching, I came up with t he following code:

Public Function SendEmail() As Boolean
Dim olApp As Outlook.Application
Dim oMail As Outlook.MailItem
Dim db As DAO.Database
Dim rec As DAO.Recordset
Dim bodytxt As String
Dim docQuery As String
Dim qdf As QueryDef

Set db = CurrentDb

docQuery = "qryStudent_placement_list"
Set qdf = db.QueryDefs(docQuery)

qdf.Parameters("Code").Value = [Forms]![frmReport_StudentPlacementList]!

    qdf.Parameters("Yr").Value = [Forms]![frmReport_StudentPlacementList]![Yr]
    
    Set rec = db.OpenRecordset(docQuery, dbOpenSnapshot)

At this point the following error is returned:
3265:Item not found in this collection.

I assume that the qdf.parameter lines are not finding the variables "Code" and "Yr" on the Form.

Any suggestions please?

 

[psemianonymous]

Build the SQL statement completely in code. It fixes all these sorts of problems.

 

[nq]

You are probably right but at this stage I have not worked with SQL enough and would rather solve the current problem. The SQL view from the Access query is:

PARAMETERS [Forms]![frmReport_StudentPlacementList]!

 Text ( 255 ), [Forms]![frmReport_StudentPlacementList]![Yr] Long;
SELECT tblStudents.ID, tblStudents.Given, tblStudents.Surname, tblSchools.Name, tblPlacements.Super, tblPlacements.Class, tblPlacements.Code, tblSchools.Pracoord, tblSchools.Premail, tblSchools.Tel, tblStudents.Email
FROM tblStudents INNER JOIN (tblSchools INNER JOIN tblPlacements ON tblSchools.Name = tblPlacements.Name) ON tblStudents.ID = tblPlacements.ID
WHERE (((tblPlacements.Code) Like [Forms]![frmReport_StudentPlacementList]![Code] & "*" Or (tblPlacements.Code) Is Null) AND ((tblPlacements.Year) Like [Forms]![frmReport_StudentPlacementList]![Yr]))
ORDER BY tblStudents.Surname;

Why "[Code] Text ( 255 )" and "[Yr] Long" exist in the code is beyond me at this stage.

 

[PHV]

I elaborate my suggestion:
docQuery = "qryStudent_placement_list"
Set qdf = db.QueryDefs(docQuery)
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next
Set rec = db.OpenRecordset(docQuery, dbOpenSnapshot)


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[GhostWolf]

That's an interesting solution, PHV. I might have to try something like that one day.

Back to the original problem:

Just twixt us, I absolutely hate the way Microsoft builds a SQL statement! There's so much extraneous junk in it that it's barely legible.

The first thing that strikes me about the SQL you posted is that both the Paramter statement and the Where clause try to refer directly to the Form's fields - which leads me to believe that the parameters are unnecessary here.

That being said, I'd still use parameters for the query, (I'm weird like that), but I'd make the following changes:

PARAMETERS
PlacementCode Text ( 255 ),
PlacementYear Long;
...
WHERE (tblPlacements.Code Like trim(PlacementCode)+'*' Or tblPlacements.Code Is Null) AND
tblPlacements.Year = PlacementYear

Then you can change the code in your program to:
qdf.Parameters("PlacementCode").Value = ...
qdf.Parameters("PlacementYear").Value = ...

((tblPlacements.Year) Like [Forms]![frmReport_StudentPlacementList]![Yr]))

I'm not so sure about that LIKE clause. Seems to me that the absence of a terminating wildcard is essentially an EQUAL clause.

Although I don't think these changes have anything to do with the "Item not found..." error, I suspect they'll clear it up.
.

 

[stix4t2]

This may be the way you want to do it.


Dim qdef As QueryDef, prm as parameter
Dim rs As DAO.Recordset
Dim db As Database
Set db = CurrentDb
Set qdef = db.QueryDefs("Query1")
qdef.Parameters("42").Value = "forms!form2!id"
For Each prm In qdef.Parameters
prm.Value = Eval(prm.Value)
Next

Set rs = qdef.OpenRecordset(dbOpenSnapshot)


rs.close

 

[nq]

Thank you to all for your suggestions.
I have a solution, although it is not ideal. Please be patient while I explain. Rather than use the "Select Query" to extract required records, I changed the "Select Query" to a "Make Table Query" to create a temporary table "tblEmail_Temp".
On the Form, the two unbound controls are used to enter the required parameters, Code (text) and Yr (integer). In the query (design view), there are two criteria:

Like [Forms]![frmReport_StudentPlacementList]!

 & "*" Or Is Null
Like [Forms]![frmReport_StudentPlacementList]![Yr]

Clicking on the "Send Email" button on the form invokes this query and creates the table with the selected records. The "SendEmail" module is then called. The code is shown below:

Public Function SendEmail() As Boolean
    Dim olApp As Outlook.Application
    Dim oMail As Outlook.MailItem
    Dim db As DAO.Database
    Dim rec As DAO.Recordset
    Dim bodytxt As String
    Dim docQuery As String

On Error GoTo HandleErr
Set db = CurrentDb

docQuery = "tblEmail_Temp"

Set rec = db.OpenRecordset(docQuery, dbOpenSnapshot)
    
On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
etc., etc.

This is EXACTLY the same code as I showed in my original post, except that instead of referring to a query, it refers to a table. The email function works perfectly, the message body is assembled from data in the table and emails are sent. This means that the query picks up both parameters from the form, implements the criteria correctly and writes the selections to a table!

I do not profess to be an expert in Access, however, I understood that a "select query" creates a dynamic snapshot of the data (dynaset?). This must be held in an array (whatever) to be accessed by a report, etc. If my code can access the records from a table, why can't it access the records from the dynaset? I am becoming overwhelmed by my own ignorance.

The down side of this is that the temporary table causes bloat. I have a delete query that clears the temporary table but bloat remains until the db is compacted. Whilst I appreciate everyone's attempts to help, I have not yet managed to assemble code that works with the query.

I would like to solve this for personal satisfaction, I have tried your various suggestions but Murphy seems to be one step ahead of me.

Ghostwolf seems to have one approach, PHV and stix4t2 have another. I am not conversant enough with DAO to make a critical comment. However:

Ghostwolf. I agree with your comments about Microsoft and their "SQL" but I could not get your suggestion to work.

stix4t2. I marvel at: qdef.Parameters("42").Value = "forms!form2!id""
42 is the answer to the universe, so I'm not sure where this came from :-)
Also, the use of the Eval function. As I understand, Eval returns a numeric evaluation of a string construction. How does this provide a criteria value for the parameter? I would have thought that the passed parameter (from the Form) must be the same data type as the value in the query to ensure that a comparision can be made.

 

[psemianonymous]

I think the line of code you are marveling at is broken and won't work, especially as it looks like it's pulling a value from a form where it's really just putting in the string "forms!etc.." which doesn't do anything.

Your temp table will work and no one will probably notice the difference, so in the Access world that's good enough. If you're still looking for a 'proper' solution, check out PHV's code as it's the only proper way to do parameters in queries.

But if it works, you're probably done with this.

 

[stix4t2]

nq,

Sometimes my samples are in variables that are to be replaced by the user in their applications of the code. So where I used "42", you would use the parameter name of your choice.

I too, was amazed at the Eval() function. It actual;y evaluates the value, so what ever is referenced in the passed string is evaluated and the value is returned. So in my example the "42" parameter would be filled with the value in forms!form2!id. If the id was 4 on form2, then eval would return 4. So maybe I should of used a parameter name of "ID" rather than "42", but what's the fun in that.

In the querydef, if you set the actual values for the parameters, then a recordset can be built from the querydef based on values off the form.

Also, remember '*', wildcard, is Ascii(42) and if you look at the bits in 42, 0x101010, the 1's and 0's represent the ups and downs in life. Just a little more fun.

Take it easy, if you want to find answers, you have to ask questions.

 

[nq]

To everyone that repsonded - thank you.
For the time being I will use the temporary table idea, just for expediency. However, in due course I must revisit the suggestions and learn from your suggestions.