Cannot Pull Data from the Report

[Novice1]

I posted the following code on a button (report to a first sergeant). I can't pull e-mail addresses from the report (rptLateByUnit). What am I doing wrong? Thanks

=========

DoCmd.SendObject acSendReport, "rptLateByUnit", acFormatPDF, Me.CCF_EMail, Me.CCS_Email, , "FOUO: Open Personnel Data Discrepancies", "This document contains information which must be protected in accordance with AFI 33-332, Air Force Privacy Act Program, and DoD Regulation 5400.7R, DoD Freedom of Information Act Program; and Privacy Act of 1974 as amended applies. This document is For Official Use Only." & vbCr & vbCr & _
"Whenever we become aware of a personnel data discrepancy (e.g., divorce not reported in DEERS, inaccurate SGLI, ID card discrepancy, etc), then we notify the member of the issue and ask him or her to fix the problem within 2 weeks." & vbCr & vbCr & _
"At the beginning of each month, we provide first sergeants a list of open discrepancies. We respectfully request the assistance of first sergeants in resolving any discrepancy that has been open for more than 2 weeks." & vbCr & vbCr & _
"If one or more people on the attached list are unavailable (e.g., TDY, leave, etc.), then please let us know so we can adjust the follow-up date in our tracking system." & vbCr & vbCr & _
"vr" & vbCr & vbCr & vbCr & _
"Mr. James Petersen, Cntr" & vbCr & _
"Site Manager, Moody AFB" & vbCr & _
"Comm (229) 257-3415, DSN Prefix 460" & vbCr & _
"Fax (229) 257-2904", True

 

[pbaldy]

This code is in that report? The Me reference should pull the addresses if those are the correct control names (and it would only work in Report view). Are you getting an error, or?

 

[Novice1]

The fields are in the report (based on a query).

When I run the code I get "Compile Error: Method or data member not found."

 

[Pat Hartman]

Reports don't store data. Tables store data. If you need to get email addresses, you need to open a recordset or use dLookup()

 

[pbaldy]

If the report is open the data is available. Try cutting down the code to the minimum just to send a simple email with one address. Make sure the control you're referencing is on the report.

 

[Novice1]

Ok ... I added the following code but I'm getting the same error.

==============

Dim myDB As Database
Dim myRS As Recordset

Set myDB = OpenDatabase("DiscrepancyAndInfoTracker.accdb")

Set myRS = myDB.OpenRecordset("qryLatebyUnit", dbOpenDynaset)


DoCmd.SendObject acSendReport, "rptLateByUnit", acFormatPDF, Me.CC_Email, Me.CCF_EMail, Me.CCS_Email, , "FOUO: Open Personnel Data Discrepancies", "This document contains information which must be protected in accordance with AFI 33-332, Air Force Privacy Act Program, and DoD Regulation 5400.7R, DoD Freedom of Information Act Program; and Privacy Act of 1974 as amended applies. This document is For Official Use Only." & vbCr & vbCr & _
"Whenever we become aware of a personnel data discrepancy (e.g., divorce not reported in DEERS, inaccurate SGLI, ID card discrepancy, etc), then we notify the member of the issue and ask him or her to fix the problem within 2 weeks." & vbCr & vbCr & _
"At the beginning of each month, we provide first sergeants a list of open discrepancies. We respectfully request the assistance of first sergeants in resolving any discrepancy that has been open for more than 2 weeks." & vbCr & vbCr & _
"If one or more people on the attached list are unavailable (e.g., TDY, leave, etc.), then please let us know so we can adjust the follow-up date in our tracking system." & vbCr & vbCr & _
"vr" & vbCr & vbCr & vbCr & _
"Mr. James Petersen, Cntr" & vbCr & _
"Site Manager, Moody AFB" & vbCr & _
"Comm (229) 257-3415, DSN Prefix 460" & vbCr & _
"Fax (229) 257-2904", True

 

[pbaldy]

You're still referring to the report in the SendObject code (which I believe should work).

 

[Pat Hartman]

If the report is open the data is available.

Exactly which report record do you expect to reference? I hope it is the first. If this report needs to go to each unit commander, you can't get their email's by referencing the report.

Me.CCF_EMail, Me.CCS_Email

Is referring to controls on the current form. Are these what you want to get from the report? How is the report selecting its data? Your description of the error you are getting is far from clear. When you get a compile error, the compiler tells you what statement is causing the error. Perhaps you could share that with us.

 

[pbaldy]

Exactly which report record do you expect to reference? I hope it is the first. If this report needs to go to each unit commander, you can't get their email's by referencing the report.

It's not my report, I was simply saying that you can get the values from a report with Me.ControlName. If the code was initiated from the detail section, you would get that record's values. I've only used Report view a couple of times, when I wanted the sorting/grouping ability of a report with the interactivity of a form, but you can reference any record you want, just as you can on a form.

 

[Pat Hartman]

@Novice,
I closed the new thread you started. Please stick with this one until you have your answer.
This is a procedure in one of my applications. It sends a report to each address in the table. The code does other stuff that you might want to consider. For example, it prints the report for snail mail if there isn't an email address and since it is sending pdf's which must be saved first, it needs a location where the files should be saved.

Private Sub cmdSendEmails_Click()
    Dim strValue As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim qd As DAO.QueryDef
    Dim td As DAO.TableDef
    Dim strMsg As String
    Dim StartTime As Date
    Dim EndTime As Date
    Dim stDocName As String
    Dim QUOTE As String
    Dim DocName As String
    Dim strPath As Variant
    Dim strSQL As String
    Dim CountErr As Integer
    Dim TestEmail As Boolean
    Dim strEmail As String
    
On Error GoTo ErrProc

    QUOTE = """"
    CountErr = 0
    Set db = CurrentDb()
    stDocName = "Inspectors report"
    TestEmail = DLookup("TestEmail", "tblEmail", "RecID = 1")
    
    'get path
    strPath = DLookup("FileFolder", "tblEMail", "RecID = 1")
    If strPath & "" = "" Then
        MsgBox "Please open email defaults form and add document path.", vbOKOnly
        Exit Sub
    End If
    If Right(strPath, 1) <> "\" Then
        strPath = strPath & "\"
    End If

    'delete old email errors
    strSQL = "Delete * From tblNoEmailAddress"
    DoCmd.SetWarnings False
    DoCmd.Hourglass True
    DoCmd.RunSQL strSQL
    strSQL = "Delete * From tblEmailErrors"
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
    DoCmd.Hourglass False
    
    'open email error recordset.
    Set td = db.TableDefs!tblNoEmailAddress
    Set rs2 = td.OpenRecordset
    
    
    Set qd = db.QueryDefs!qGetIRDataJobs
    Set rs = qd.OpenRecordset
    
    StartTime = Now()
    Me.lblElapsedTime.Visible = False
    Me.lblStatus.Visible = True
    Me.lblStatus.Caption = "Sending Emails...."
    Me.Recalc
    DoEvents
    DoCmd.Hourglass (True)
    
    ' Create the Outlook session.
        'Set oOL = CreateObject("Outlook.Application")
        'Set oOL = New Outlook.Application
    On Error Resume Next
    Set oOL = GetObject(, "Outlook.Application")

    If Err.Number = 429 Then
        Set oOL = CreateObject("Outlook.application")
    End If

    On Error GoTo ErrProc


    If Me.txtJob & "" = "" Then
        Do Until rs.EOF = True
            Me.txtJob = rs!Job
            GoSub PrintOrEmail
            rs.MoveNext
        Loop
    Else
        GoSub PrintOrEmail
    End If
    'Close outlook session
    Set oOL = Nothing
    
    DoCmd.Hourglass (False)
    DoCmd.SetWarnings (True)
    Me.lblStatus.Caption = "Complete"
    EndTime = Now()
    Me.lblStatus.Visible = True
    Me.lblElapsedTime.Visible = True
    Me.lblElapsedTime.Caption = DateDiff("n", StartTime, EndTime) & " Minutes"
    
    'close recordsets
    rs.Close
    rs2.Close
    If CountErr > 0 Then
        DoCmd.OpenReport "rptNoEmailAddress", acViewPreview
    Else
        MsgBox "All reports were emailed.", vbOKOnly
    End If
    If DCount("*", "tblEMailErrors") > 0 Then
        DoCmd.OpenReport "rptEMailErrors", acViewPreview
    End If
ExitProc:
    Exit Sub
    
PrintOrEmail:
    DocName = strPath & rs!Job & "_" & Format(Date, "yyyymmdd") & ".pdf"
    If rs!IR_Email & "" = "" Then
        DoCmd.OpenReport stDocName, acViewNormal
        rs2.AddNew
            rs2!Job = Me.txtJob
            rs2!PrintDate = Now()
        rs2.Update
        CountErr = CountErr + 1
    Else
        Kill DocName    'delete existing file if any so outputto won't hang
        DoCmd.OutputTo acOutputReport, stDocName, acFormatPDF, DocName, False
        ' send the PDF via outlook
        If TestEmail = True Then
            strEmail = "mfalaguerra@safetymarking.net"
        Else
            strEmail = rs!IR_Email
        End If
        
        strValue = Email_Via_Outlook(strEmail, "Quantity Review Report", "", False, DocName)
    End If
    Return
ErrProc:
    Select Case Err.Number
        Case 53 'file not found
            Resume Next
        Case Else
            MsgBox Err.Number & "--" & Err.Description
            Resume Next
    End Select
    
End Sub