none
Emailing a filtered report RRS feed

  • Question

  • Have trouble with the following code:

     

    [code]

    Private Sub Warning_Email_Click()
    
    On Error GoTo Err_WarningEmail
    
    strEmpWarnEmail = "badams2@uwlax.edu"
    
    Dim vbRst As New ADODB.Recordset
    
    vbRst.Open "qryEarning_Warning", CurrentProject.Connection, adOpenDynamic
    
    Do While Not vbRst.EOF
    
        strEmpWarnEmail = vbRst.Fields(11).Value
          
        Reports![rptEarning_Warning].Filter = strEmpWarnEmail
        
        FilterOn = True
        
        DoCmd.SendObject acSendReport, "rptEarning_Warning", , vbRst.Fields(11).Value, , , This Is it, "We are trying very hard"
        
        vbRst.MoveNext
        
        FilterOn = False
        
        Loop
         
    Exit_cmdWarningemail:
        Exit Sub
    
    Err_WarningEmail:
        MsgBox Err.Description
        Resume Exit_cmdWarningemail
    
    End Sub
    

    [/code]


    I am getting a "syntax error, missing operator in query expression 'sandy@college.edu' "

    this occurs when it is running the part Reports![rptEarning_Warning].Filter = strEmpWarnEmail

     

    Any ideas?


    • Edited by badams2 Wednesday, October 26, 2011 7:07 PM
    Wednesday, October 26, 2011 7:07 PM

Answers

  • You might assign the Field to the Filter, something like this:

    Private Sub Warning_Email_Click()
    
    On Error GoTo Err_WarningEmail
    
    ' declare as String
    Dim strEmpWarnEmail As String
    
    'strEmpWarnEmail = "badams2@uwlax.edu"
    
    Dim vbRst As New ADODB.Recordset
    
    vbRst.Open "qryEarning_Warning", CurrentProject.Connection, adOpenDynamic
    
    Do While Not vbRst.EOF
    
        strEmpWarnEmail = vbRst.Fields(11).Value
         ' chang the Fieldname 'EmailFieldName' accordingly      
        Reports![rptEarning_Warning].Filter = "EmailFieldName='" & strEmpWarnEmail & "'"
        ' set FilterOn of the Report Object
        Reports![rptEarning_Warning].FilterOn = True
        
        DoCmd.SendObject acSendReport, "rptEarning_Warning", , vbRst.Fields(11).Value, , , This Is it, "We are trying very hard"
        
        vbRst.MoveNext
    
         ' set FilterOn to False of the Report Object
        Reports![rptEarning_Warning].FilterOn = False
        
        Loop
         
    Exit_cmdWarningemail:
        Exit Sub
    
    Err_WarningEmail:
        MsgBox Err.Description
        Resume Exit_cmdWarningemail
    
    End Sub
    
    

     


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Wednesday, October 26, 2011 8:38 PM
    Moderator