Need to filter and email reports based on the name of the recipient 2

[Valeriya]

I have run into the following problem. I have a Projects Database which has a form from which I need to e-mail reports based on the project leader name (which is one of the columns in the report). So basically I need to filter the report based on the project leader name (that way the report shows all the projects for the particular project lead) and email it to that project lead. That process has to take place for every project leader on the report. Is there any way to do a DoCmd.SendObject command to send a report and at the same time filter it based on the name of the project lead?

Thanks!
Valeriya

 

[JerryKlmns]

How about, use a recordset of

SELECT DISTINCT ProjectLeaderName, HisEmail
FROM YourTable

Loop throu every record, open the report using the WHERE condition
DoCmd.OpenReport "YourReportName", acViewNormal, , "ProjectLeaderName='" & rs.Fields(0) & "'"
And send that active object
DoCmd.SendObject acReport, , acFormatSNP, rs.Fields(0), , , "YourSubject", "SomeTextHere"

 

[Valeriya]

Hi Jerry,
Here is the code that I wrote. When I click Send Report botton it gives me this error message:

Run Time Error '2282'...The format in which you are attempting to output the current object is not available.

Private Sub Snd_Report_Click()
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    
    
    Set db = CurrentDb
    Set rst = db.OpenRecordset("SELECT Assignee.ID, Assignee.[E-mail Address]FROM Assignee")

    While Not rst.EOF
    
            DoCmd.OpenReport "Open Projects", acViewPreview, , "Project Lead" = " & rst.Fields(0) & "
            
            DoCmd.SendObject acSendReport, acFormatSNP, rst.Fields(1), , , "Your Projects by Status"
            
            rst.MoveNext
            
    Wend



End Sub

Also, after I get the error message the report does open but there is no data in it. All I get is just a skeleton. I do not want the report to be open. All I need it for it's snap shot to be sent to each project lead with data only relevant to that particular project lead. If I do AcViewNormal, then when I click the Send Report botton my system is trying to print the report(which I do not want either). But I guess at this point I need to figure out first why nothing gets sent and why I get the error message.

PS. Assignee.ID is AutoNumber

Thanks much!

Valeriya

 

[JerryKlmns]

Private Sub Snd_Report_Click()
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    
    Set db = CurrentDb
    Set rst = db.OpenRecordset("SELECT Assignee.ID, Assignee.[E-mail Address] FROM Assignee")
    While Not rst.EOF
       DoCmd.OpenReport "Open Projects", acViewNormal, , "Project Lead=" & rst.Fields(0)            
       DoCmd.SendObject acReport, acFormatSNP, rst.Fields(1), , , "Your Projects by Status"
       rst.MoveNext
       DoCmd.Close acReport, "Open Projects", acSaveNo
    Wend
    rst.Close
    Set rst = Nothing
    Set db = Nothing

End Sub

 

[Valeriya]

Jim,
I have revised my code like you advised, however, it still does not work. I get the following error message:

Run-time error: '3075':
Syntax error(missing operator) in query expression'(Project Lead=1)'.

Thanks a lot!

Valeria.

 

[Valeriya]

I also tried this code. Still same problem.

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    
    Set db = CurrentDb
    Set rst = db.OpenRecordset("SELECT Assignee.[First Name] & ' ' &  Assignee.[Last Name] AS Expr1, Assignee.[E-mail Address] FROM Assignee;")
                              
    While Not rst.EOF
       DoCmd.OpenReport "Open Projects", acViewPreview, , "Project Lead=" & rst.Fields(0)
       DoCmd.SendObject acReport, acFormatSNP, rst.Fields(1), , , "PLEASE DISREGARD TEST Your Projects by Status"
       rst.MoveNext
       DoCmd.Close acReport, "Open Projects", acSaveNo
    Wend
    rst.Close
    Set rst = Nothing
    Set db = Nothing

End Sub

Appreciate any input.

Many Thanks!

Valeriya

 

[JerryKlmns]

How about

DoCmd.OpenReport "Open Projects", acViewNormal, , "[Project Lead]=" & rst.Fields(0)

 

[Valeriya]

I tried that too. Does not work. Not sure what the problem could be. Everything looks right but every time I get that syntex error - Run-time error: '3075':
Syntax error(missing operator) in query expression.

 

[ssatech]

And you tried this:

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT Assignee.ID, Assignee.[E-mail Address] FROM Assignee")

While Not rst.EOF
DoCmd.OpenReport "Open Projects", acViewNormal, , "[Project Lead]=" & rst.Fields(0)
DoCmd.SendObject acSendReport, "Open Projects", acFormatSNP, rst.Fields(1), , , "Your Projects by Status", True

rst.MoveNext
DoCmd.Close acReport, "Open Projects", acSaveNo
Wend
rst.Close
Set rst = Nothing
Set db = Nothing

 

[Valeriya]

Thanks guys for all your help!!! It works fine now.

Valeriya