DoCmd.SendObject - Custom Filtered Report

[agustinvs]

Hello everybody, I am trying to create a form that sends mail alerts with a filtered report for each person. I have been using some code I found online.
This is my problem, right now it is sending the mails and it is filtering the report but only filter the same records, after the first mail the rest of the mails have the exact same report (filtered but not the right filter)
I would appreciate any help on this.
I created a blank form with a button and this code:

Private Sub Command2_Click()
Dim RS As DAO.Recordset
Set RS = CurrentDb.OpenRecordset("mail_Reminder_RecordSet")
With RS
If .EOF And .bof Then 'IT MEANS THERE ARE NO RECORDS, WE ARE AT THE BEGINNING OF FILE AND END OF FILE (No Records found for this query.)
Else
Do Until .EOF 'DO UNTIL END OF RECORDSET FILE
DoCmd.SendObject acSendReport, "report", acFormatPDF, ![mailadress], , , "Low Balance Alert", "Hello " & ![FirstName] & _
", " & Chr(10) & "You ballance is under 10 pounds. Please…", False
.Edit
![mail_Sent_Date] = Now()
.Update
.MoveNext
Loop
End If
End With
'CLEAN UP MEMORY AT END
If Not RS Is Nothing Then
RS.Close
Set RS = Nothing
End If
End Sub
Then I have this code under "On Open" in the report:

Private Sub Report_Open(Cancel As Integer)
Me.Filter = "IncidKey = '" & Forms![Menu]![IncidKey] & "'"
Me.FilterOn = True
End Sub


Any Ideas? Thanks a lot

 

[John Big Booty]

As the code currently stands it is only designed to send the one report called Report. You will need to add a line to your code to change the report for each recipient. If might look something like;

Private Sub Command2_Click()
Dim RS As DAO.Recordset
[COLOR="Red"]Dim strRepName as String[/COLOR]

Set RS = CurrentDb.OpenRecordset("mail_Reminder_RecordSet")

With RS
If .EOF And .bof Then [COLOR="SeaGreen"]'IT MEANS THERE ARE NO RECORDS, WE ARE AT THE BEGINNING OF FILE AND END OF FILE (No Records found for this query.)[/COLOR]

Else
     Do Until .EOF[COLOR="SeaGreen"] 'DO UNTIL END OF RECORDSET FILE[/COLOR]
     [COLOR="Red"]strRepName[/COLOR] = [COLOR="SeaGreen"]'At this point you need to set the filtered repor tname for the individual recipient[/COLOR]
     DoCmd.SendObject acSendReport, [COLOR="Red"]strRepName[/COLOR] , acFormatPDF, ![mailadress], , , "Low Balance Alert", "Hello " & ![FirstName] & _
     ", " & Chr(10) & "You ballance is under 10 pounds. Please…", False
     .Edit
     ![mail_Sent_Date] = Now()
     .Update
     .MoveNext
     Loop
End If
End With
[COLOR="SeaGreen"]'CLEAN UP MEMORY AT END[/COLOR]
If Not RS Is Nothing Then
RS.Close
Set RS = Nothing
End If
End Sub
Then I have this code under "On Open" in the report:

Private Sub Report_Open(Cancel As Integer)
Me.Filter = "IncidKey = '" & Forms![Menu]![IncidKey] & "'"
Me.FilterOn = True
End Sub

 

[pbaldy]

If that's not it, you need code in your loop to set the value of

Forms![Menu]![IncidKey]

to the current value in the recordset. That's what the report code is counting on. I've used that technique a number of times.

 

[agustinvs]

Thank you both. I just left the office, but I will try that tomorrow morning. Thank you again for the help.

pbaldy, I am not sure I understand what do you mean. I really have no experience with VB. Could you tell me how to set the loop to my value?

Thank you, thank you

 

[pbaldy]

The code in the report is looking to that form control for the value to filter itself by. You're not changing that value, so you keep getting the same report. Before the SendObject line:

Forms![Menu]![IncidKey] = rs!IncidKey

Or whatever the field is named.

 

[agustinvs]

Great, thank you for the advise I will try both options tomorrow and let you know what happens.

 

[agustinvs]

Pbaldy thank you so much for your help. This is working great now! I am so happy

John Big Booty, thanks for your help too, I tried the other option first because it looked easier but I am sure your option was as good too.

 

[pbaldy]

Happy to help!

 

[agustinvs]

Paul,
I think I talked too fast. For some reasson it stop filtering the reports. It did work for a while but now it just sends the same report to all the emails adresses. Do you know what could be wrong?

 

[pbaldy]

Not without seeing the revised code.

 

[agustinvs]

Thanks for the answer. Well I know what happened but I do not know how to fix it. The filter is working but it changed all the IncidKey records on my table to the first one.
I have a warning when I close the form after I send the mails saying "Write Conflict, The record has been changed by another user since you started editing it..."
This is the code:

Private Sub Command2_Click()
Dim RS As DAO.Recordset
Set RS = CurrentDb.OpenRecordset("mail_Reminder_RecordSet")
With RS
If .EOF And .bof Then 'IT MEANS THERE ARE NO RECORDS, WE ARE AT THE BEGINNING OF FILE AND END OF FILE (No Records found for this query.)
Else
Do Until .EOF 'DO UNTIL END OF RECORDSET FILE
Forms![Menu2]![IncidKey] = RS!IncidKey
DoCmd.SendObject acSendReport, "report", acFormatPDF, ![mailadress], , , "Low Balance Alert", "Hello " & ![FirstName] & _
", " & Chr(10) & "You ballance is under 10 pounds. Please…", False
.Edit
![mail_Sent_Date] = Now()
.Update
.MoveNext
Loop
End If
End With
'CLEAN UP MEMORY AT END
If Not RS Is Nothing Then
RS.Close
Set RS = Nothing
End If
 
End Sub

And here is the code for the report

Private Sub Report_Open(Cancel As Integer)
  Me.Filter = "IncidKey = '" & Forms![Menu2]![IncidKey] & "'"
  Me.FilterOn = True
End Sub

Thanks again for the help.

 

[pbaldy]

That form control isn't bound to the table, is it? It should not be. I would just use a hidden, unbound textbox on a form sure to be open.

 

[agustinvs]

Thanks a lot. You were right. I created a text box and named IncidKey and it works like a charm.
It is amazing how little I know about access and how much I can do with your help.
Thanks

 

[pbaldy]

No problemo!

 

[LukeChung-FMS]

Here's a paper we wrote that may help you:

DoCmd SendObject Command in Microsoft Access: Features and Limitations for Sending Emails

 

[agustinvs]

I downloaded the trial and I was disapointed because it does not work with Outlook.