Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2003
    Location
    Atlanta, GA
    Posts
    92

    Unanswered: Report e-mail and Name Change

    Hello again all!
    I've recently began working with Access again (2003) and am trying to create a CMD button from a form that will send out emails and rename the report being produced.
    I am using code based off of:
    http://www.dbforums.com/microsoft-ac...pdf-email.html

    but I receive a "Compile Error: Method or data member not found" error on DoCMD.SetProperty . I'm still in the basics of understanding VBA, and was hoping somebody could explain what might be going on. I've searched the site, but can't seem to locate anything to resolve this.


    Here is what I have in the On Click Event:

    Private Sub btn_Email_Click()
    Dim stReport As String
    Dim stWhere As String
    Dim stEmailadd As String
    Dim stSubject As String
    Dim stEmailMessage As String

    stEmailMessage = "Please see the updated POR for " & Me.PORSiteName & ". Contact me if you have any questions. " & vbCrLf & vbCrLf & "Thank You, " '
    stSubject = "POR for " & Me.PORSiteName & "-" & Me.PORID & "-" & Me.PORLocation '
    stReport = "rpt1Project"
    stEmailadd = Me.EmailUpdatesTo
    DoCmd.OpenReport stReport, acViewPreview, "", stWhere, acWindowNormal, ""
    DoCmd.SetProperty stReport, acPropertyCaption, "POR for " & Me.PORLocation '
    DoCmd.SendObject acSendReport, stReport, "HTML(*.html)", stEmailadd, , , stSubject, stEmailMessage, True, ""
    End Sub

    Basically, This pulls email addresses from the form, produces the forms report and should rename the report to add an ID to the file name. It completes it all, except for the file name change. Any ideas?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    As far as I know, there is no SetProperty method for the DoCmd object in Access 2003 (this was introduced in Access 2007). Moreover, the acPropertyCaption is also undefined in this version of Access.
    Have a nice day!

  3. #3
    Join Date
    Sep 2003
    Location
    Atlanta, GA
    Posts
    92
    Is there an alternative method available in 2003? I'm trying to get this to work on three computers, ranging from Access 2003 to Access 2010.
    Last edited by inzzane; 10-23-11 at 11:18.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Here's a possibility (the report must be open):
    Code:
    Sub ChangeReportCaption(ByVal ReportName As String, ByVal Caption As String)
    
        Dim obj As AccessObject
        Dim dbs As Object
        Set dbs = Application.CurrentProject
        For Each obj In dbs.AllReports
            If obj.Name = ReportName Then
                If obj.IsLoaded = True Then
                    Reports(ReportName).Caption = Caption
                    Exit For
                End If
            End If
        Next obj
        
    End Sub
    In the example you posted, use:
    Code:
    DoCmd.OpenReport stReport, acViewPreview, "", stWhere, acWindowNormal, ""
    ChangeReportCaption, stReport, "POR for " & Me.PORLocation 
    DoCmd.SendObject acSendReport, stReport, "HTML(*.html)", stEmailadd, , , stSubject, stEmailMessage, True, ""
    Have a nice day!

  5. #5
    Join Date
    Sep 2003
    Location
    Atlanta, GA
    Posts
    92
    Sinndho,
    Thanks for the reply and assistance. I have made the corrections (added both the Sub ChangeReportCaption, and new line to code), but am now receiving a Compile Error: Argument Not Optional on the ChangeReportCaption line for the existing code.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    If the error occurs on the line:
    Code:
    ChangeReportCaption, stReport, "POR for " & Me.PORLocation
    What's the value of stReport?
    Have a nice day!

  7. #7
    Join Date
    Sep 2003
    Location
    Atlanta, GA
    Posts
    92
    When I receive the error, the report doesn't open.
    The report normally opens as "POR Report". I'm beginning to wonder now if I need to have some kind of delay to allow the report to open completely before acting on this line. If I remove the last 2 lines of code, the report does open.

  8. #8
    Join Date
    Sep 2003
    Location
    Atlanta, GA
    Posts
    92
    I appreciate your help on this. I've been losing my hair trying to figure this out. In case I misunderstood you previous reply...

    stReport = "rpt1Project" [Name of report in Access/Reports).
    "PORReport" is caption of report when opened, and name of file when produced.
    Me.PORLocation is location of the text ID that need to add to the report when emailed.

    Here is the code as I have it now:


    Code:
    Sub ChangeReportCaption(ByVal ReportName As String, ByVal Caption As String)
    
        Dim obj As AccessObject
        Dim dbs As Object
        Set dbs = Application.CurrentProject
        For Each obj In dbs.AllReports
            If obj.Name = ReportName Then
                If obj.IsLoaded = True Then
                    Reports(ReportName).Caption = Caption
                    Exit For
                End If
            End If
        Next obj
        
    End Sub
    Code:
    Private Sub Command91_Click()
    Dim stReport As String
    Dim stWhere As String
    Dim stEmailadd As String
    Dim stSubject As String
    Dim stEmailMessage As String
    
    stEmailMessage = "Please see the updated POR Quote for " & Me.PORSiteName & " and contact me if you have any questions. " & vbCrLf & vbCrLf & "Thank You, " '
    stSubject = "POR Quote for " & Me.PORSiteName & "-" & Me.PORID & "-" & Me.PORLocation '
    stReport = "rpt1Project"
    stEmailadd = Me.EmailUpdatesTo
    DoCmd.OpenReport stReport, acViewPreview, "", stWhere, acWindowNormal, ""
    ChangeReportCaption , stReport, "POR for " & Me.PORLocation
    DoCmd.SendObject acSendReport, stReport, "HTML(*.html)", stEmailadd, , , stSubject, stEmailMessage, True, ""
    
    End Sub

  9. #9
    Join Date
    Sep 2003
    Location
    Atlanta, GA
    Posts
    92

    Got It! SOLVED!

    Sinndho,
    I've figured it out! Here's what I came across:

    Code:
    Private Sub Command91_Click()
    Dim stReport As String
    Dim stWhere As String
    Dim stEmailadd As String
    Dim stSubject As String
    Dim stEmailMessage As String
    Dim ReportCap As String
    
    stEmailMessage = "Email Body Message " & Me.PORSiteName & " Body Message " & vbCrLf & vbCrLf & "Thank You, "                'Email Body
    stSubject = "POR Quote for " & Me.PORSiteName & "-" & Me.PORID & "-" & Me.PORLocation                         'E-Mail Subject
    stReport = "rpt1Project"              'Original name of Report
    ReportCap = "PORReport"            'Current Report Caption
    stEmailadd = Me.EmailUpdatesTo  'Location to retrieve email addresses
    DoCmd.OpenReport stReport, acViewPreview, "", stWhere, acWindowNormal, ""    'Opens the report
    Reports!rpt1Project.Caption = "POR for " & Me.PORLocation          'Renames The report and add ID field
    DoCmd.SendObject acSendReport, stReport, "HTML(*.html)", stEmailadd, , , stSubject, stEmailMessage, True, ""            'Send out the email
    
    End Sub
    I don't know how well the code is written, but it works. :P

    Again, I appreciate your help with this!

    Thank you.

  10. #10
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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