Email report

Topic

New Reply

I have a button on a form screen that is used to email a report on a regular basis to a list of department chairmen. Everything worked fine until I modified a couple of tables in the database. Now when I click the button I get the following message: “Too few parameters. Expected 1.” After modifying the tables, I also modified the queries and reports that were affected. The query and the report mentioned in the following code opens just fine. Does anyone have a clue as to what could cause this error. I compacted and repaired the database and that did not help.

Code:

Private Sub cmdEmail_Click()
On Error GoTo ErrHandler
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim Employee As DAO.Field
    Dim Employee2 As DAO.Field
    Dim Location As DAO.Field
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("qryESOLDeptChair4Email", dbOpenDynaset)
    
    Set Employee = rs![EmailAdd]
    Set Employee2 = rs![FullName]
    Set Location = rs![SchName]

    Do While rs.EOF = False
        gstrEmployeeName = Employee2
        DoCmd.SendObject acSendReport, "rptEmailAllSchoolsWithProf", acFormatSNP, Employee, , , "Attached ESOL Roster for " & [Location], "The attached file contains CONFIDENTIAL student information and should ONLY be opened and saved on a School System Computer.  Right click on the attachment and select open.", True
        rs.MoveNext
    Loop
    DoCmd.SetWarnings True

ExitHandler:
    On Error Resume Next
    gstrEmployeeName = ""
    Set Employee = Nothing
    Set Employee2 = Nothing
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Exit Sub

ErrHandler:
    MsgBox Err.Description
    Resume ExitHandler
End Sub

Reply | Quote

Replies

Difficult to say without seeing it.
Does the query have any references to the form in it?
That is the most likely cause of a parameter request.

Most Likely in the query qryESOLDeptChair4Email

Reply | Quote

Further to what Andrew has said. Queries used as record sources for DAO recordsets cannot reference Forms as parameters.

This thread discusses how you can deal with that (if that is the problem).

Reply | Quote

Thanks for the push in the right direction. The query I was using did not refer directly to a control located on a form, but one of the queries it was using referenced the form control containing the current school year. Once I typed the school year into the query, it worked like a charm. Since this is something that changes only once a year, it is not that hard to manually change the criteria.

Reply | Quote

Hi, Judy,
If it were me, I’d skip the intervening field declarations, and just stick the fields in directly — does anybody have a comment on this, and if it is more efficient?

Instead of:
DoCmd.SendObject acSendReport, “rptEmailAllSchoolsWithProf”, acFormatSNP, Employee, , , “Attached ESOL Roster for ” & [Location] .. etc
I’d put:
DoCmd.SendObject acSendReport, “rptEmailAllSchoolsWithProf”, acFormatSNP, rs!EmailAdd, “Attached for ” & rs!SchName .. etc

Actually, I would probably be wrapping quotes around the fields, as in ” & chr(34) & rs!EmailAdd & chr(34) & “Attached … but I see (after looking at the docmd.sendobject) that the parameters it receives are variants, so I don’t know if that’s necessary or more efficient.

Does someone know if it is better to replace declared fields for real fields in the above report? Would my method work? (I am assuming so..)
Also, Judy — I don’t see anything happening with assigning something to “gstrEmployeeName,” possibly that is included in the sendobject somewhere?

thx
Pat

Reply | Quote

If it were me, I’d skip the intervening field declarations, and just stick the fields in directly — does anybody have a comment on this, and if it is more efficient?

It is probably slightly more efficient (for the computer) to avoid declaring variables you don’t really need.
But usually less efficient for me the programmer.

So I usually do use the variables.

I would probably have

Code:

strMessage = "Attached ESOL Roster for " & [Location], "The attached file contains CONFIDENTIAL student information and should ONLY be opened and saved on a School System etc " 
DoCmd.SendObject acSendReport, strReportNmae, acFormatSNP, Employee, , , strMessage, True

I find it easier to read and debug if you have a series of shorter lines that each do just one job.

And if I want to be sure that it is all working properly I can put in lines like

Code:

debug.print strMessage

to test one part of it.

All this is especially true when you are sending emails. You don’t want to send out a pile of emails then discover later that the wrong stuff is going to people.
So I would comment out the docmd.SendObject line, and run the code with debug.print lines for all the components.

I don’t see anything happening with assigning something to “gstrEmployeeName

It does not appear to be used, but maybe it is actually part of the message.

Reply | Quote

Thanks, John. I agree with you that it is visually easier to store long message string to a variable, which I would definitely do if it were my code. Thanks for your note on the efficiency & of your preferred style.

Pat

Reply | Quote