Results 1 to 7 of 7

Thread: Email report

  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    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
    Andrew

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    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).
    Regards
    John



  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  5. #5
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    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.
    Regards
    John



  7. #7
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •