Printing from VBA

[Leathem]

I have a small but annoying problem that I'm hoping someone can help me with. I've attached a file containing some code that's activated when the user clicks on a button on a form called DistributeLetters. The code is supposed to get some information from a table and use it to fill in a report that is in the form of a letter and then either send the letter as an email attachment if the person has an email address or print a hard copy if the person has not listed an email address. The first works fine (the email part); but while the code does print a hard copy of the letter, it also for some reason I can't fathom prints a copy of the DistributeLetters form with the button! I can't see where this is called for in the code.

 

[vbaInet]

Why not paste the code here and enclose them in code tags as shown in the link below:

http://www.access-programmers.co.uk/forums/showthread.php?p=1009015#post1009015

DoCmd.SelectObject - select the report object after opening the report and before printing.

 

[Leathem]

thanks for the quick reply. I inserted the statement DoCmd.SelectObject acReport, "Enrolled Students Letter" after the statement DoCmd.OpenReport "Enrolled Students Letter" and before the statement DoCmd.RunCommand acCmdPrint, but now the code stops executing at that new statement and produces an error message that says that the object "enrolled students letter" is not open.

PS: thanks for the advice on posting code. I'll do so in the future.

 

[vbaInet]

Before the DoCmd.SelectObject line, add two DoEvents, i.e.

DoEvents
DoEvents
DoCmd.SelectObject ...

PS: thanks for the advice on posting code. I'll do so in the future.

It will still be necessary to post the code here. I don't want to have to keep opening your doc in order to see the code

 

[Leathem]

Apologies. I misunderstood your earlier request. Here's the code. I still get the same error message. Here's the code:

Private Sub Command0_Click()
' Error handler
    On Error GoTo 0
    ' Create a SELECT command
    Dim MemberQuery
    MemberQuery = "SELECT MemberData_Test.StudentID, MemberData_Test.NAME, MemberData_Test.[E-MAIL] FROM MemberData_Test"
    ' Get a recordset using the query
    Dim Recordset
    Set Recordset = CurrentDb.OpenRecordset(MemberQuery)
    ' Move through the recordset looking at each record
    Do Until Recordset.EOF
    Dim StudentName, EmailAddress
    StudentName = Recordset("NAME")
    EmailAddress = Recordset("E-MAIL")
    Forms.DistributeLetters.StudentName = StudentName
    Forms.DistributeLetters.EmailAddress = EmailAddress
    If EmailAddress = "" Or IsNull(EmailAddress) Then
        Forms.DistributeLetters.EmailAddress = "No Email"
        DoCmd.OpenReport ("Enrolled Students Letter")
        DoEvents
        DoEvents
        DoCmd.SelectObject acReport, "Enrolled Students Letter"
        DoCmd.RunCommand acCmdPrint
        Else
        DoCmd.SendObject acReport, "Enrolled Students Letter", "PDFFormat(*.pdf)", _
           EmailAddress, "", "", "Course enrollment confirmation attached (pdf format)", "", False, ""
        End If
    Recordset.MoveNext
    Loop
    Recordset.Close
    CurrentDb.Close
    Exit Sub
emailerror:
        MsgBox ("There was an error in the subroutine")
End Sub

 

[vbaInet]

I've overhauled your code:

Private Sub Command0_Click()
    ' Error handler
    On Error GoTo emailerror

    Dim rst As DAO.Recordset
    
    Const MemberQuery As String = "SELECT StudentID, [NAME], [E-MAIL] " & _
                                  "FROM MemberData_Test"
                  
    ' Get a recordset using the query
    Set rst = CurrentDb.OpenRecordset(MemberQuery, dbOpenSnapshot)
    
    ' Move through the recordset looking at each record
    With rst
        Do While Not .EOF
            Forms.DistributeLetters.StudentName = ![Name]
            Forms.DistributeLetters.EmailAddress = ![E-Mail]
            
            If Nz(EmailAddress, "") = "" Then
                Forms.DistributeLetters.EmailAddress = "No Email"
                DoCmd.OpenReport ("Enrolled Students Letter")
                Reports("Enrolled Students Letter").Print
            Else
                DoCmd.SendObject acReport, "Enrolled Students Letter", "PDFFormat(*.pdf)", _
                    ![E-Mail], "", "", "Course enrollment confirmation attached (pdf format)", "", False, ""
                DoEvents
            End If
            
            .MoveNext
        Loop
    End With
    
    rst.Close
    Set rst = Nothing
    
exit_emailerror:
    Exit Sub
    
emailerror:
    MsgBox "There was an error in the subroutine"
    Resume  exit_emailerror
End Sub

* Bad naming conventions - Command0, Name as a field name. Name is an Access reserved keyword and should not be used
* Error handler was redundant - don't use On Error GoTo 0
* Not explicitly dimensioning your variables to a certain data type - Dim variable As ???

Please scrutinise the code line-by-line and note additions and deletions.

 

[Leathem]

Thanks for the revised code. I see some commands in there that I will need to learn for future use. I've run the code and it does solve the problem of printing the form in addition to the report. There's a problem, though. I've stepped through the code to track it, and here's what happens: it goes through the file and sends emails just as it should, but the first time it encounters a record with no email address it proceeds to print the letter ok, but then goes immediately to the emailerror statement, displays the message box and then loops back to the exit_emailerror entry point, drops to the Exit Sub statement and stops executing. It never proceeds to the next record.

 

[vbaInet]

Comment out this line:

    On Error GoTo emailerror

, run the code and tell me the full error message.

 

[Leathem]

The error message reads: Run-time error '2451'. The report named 'Enrolled Students Letter' you entered is misspelled or refers to a report that isn't open or doesn't exist.

 

[vbaInet]

What line does it highlight?

 

[Leathem]

when the error message popped up I selected "debug" which took me to the code. The line that was highlighted was Reports.("Enrolled Students Letter").Print.

 

[boblarson]

vbaInet:

Why did you use:

                DoCmd.OpenReport ("Enrolled Students Letter")
                Reports("Enrolled Students Letter").Print

When this line:

DoCmd.OpenReport ("Enrolled Students Letter")

already prints it? Because it hasn't been specified with acViewPreview, it will print with that.

 

[Leathem]

I think it was a carryover. Plus, I didn't realize that the print command would be implicit. In any case, that solved the problem! Thank you so much!

 

[vbaInet]

vbaInet:

Why did you use:

                DoCmd.OpenReport ("Enrolled Students Letter")
                Reports("Enrolled Students Letter").Print

When this line:

DoCmd.OpenReport ("Enrolled Students Letter")

already prints it? Because it hasn't been specified with acViewPreview, it will print with that.

Good point Bob. That was just a replacement for DoCmd code and obviously an oversight.