Filter a DAO.Recordset Table

[agustinvs]

Hello,
I am trying to add a filter to a recordset table but I am not sure how to write ir or in what part of the code.
This is my code:

[SIZE=3][FONT=Calibri]Dim RS As DAO.Recordset[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Set RS = CurrentDb.OpenRecordset("Mail_Reminder_RecordSet")[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]With RS[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]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.)[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Else[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Do Until .EOF 'DO UNTIL END OF RECORDSET FILE[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Forms![Menu2]![IncidKey] = RS!IncidKey[/FONT][/SIZE]
[FONT=Calibri][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Calibri]DoCmd.SendObject acSendReport, "report", acFormatPDF, ![Mail], , , "First Alert - High Impact Incident Notification", "This is an automatic mail notification. You have received this mail because there is a high-impact First Alert incident regarding your product line. Please refer to the attached report.", False[/FONT][/SIZE]
[SIZE=3][FONT=Calibri].Edit[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]![Mail_Sent_Date] = Now()[/FONT][/SIZE]
[SIZE=3][FONT=Calibri].Update[/FONT][/SIZE]
[SIZE=3][FONT=Calibri].MoveNext[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Loop[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]End If[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]End With[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]'CLEAN UP MEMORY AT END[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]If Not RS Is Nothing Then[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]RS.Close[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Set RS = Nothing[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]End If[/FONT][/SIZE]

What I need to do is filter by mail_Sent_Date = Null so I do not resend the same email twice. I have tried this but it did not work:

RS.Filter = RS!mail_Sent_Date = Null

Any ideas?

Thanks in advance for your help

 

[pbaldy]

Try

Set RS = CurrentDb.OpenRecordset("SELECT * FROM Mail_Reminder_RecordSet WHERE mail_Sent_Date Is Null")

 

[agustinvs]

It worked great!
Thank you Paul!

 

[pbaldy]

Happy to help! That way is more efficient, as you're only opening the recordset on the desired records, instead of all records.

 

[agustinvs]

Yup it is. I was trying to use a query instead of a table as a recordset but it did not let me record the sent date. Thanks again.

 

[agustinvs]

Do you think is difficult to open a dialog window if there are no null values saying something like "No new incidents" or something?

 

[pbaldy]

You're already testing for EOF, so in that section:

Msgbox "There are no records, blah blah"

 

[smig]

With rs
If rs.RecordsCount = 0 Then ' --- No records in your recordset
... Do what you want
End If
.Close ' -- Close the recordset
End With
rs = Nothing


one comment - if there are records in your recordset you must use the .MoveLast before you trust the .RecordsCount to give you the correct number of records

 

[agustinvs]

Great I used Paul's option and it is working great. The last detail is the "A program is trying to send an e-mail on your behalf..." I look around and I believe there is no way to disable this security warning... Am I right?

 

[pbaldy]

I've used a free utility called ClickYes, and I've heard of but never used Outlook Redemption. I typically use CDO instead of SendObject when I want to avoid those types of things. Google should turn up the code.

 

[agustinvs]

Yup I looked at the redemption too but it seems way to complicated and not too safe. I will take a look at the ClickYes one. No idea what CDO is at all : )
Thank you guys

 

[agustinvs]

It seems like I could replace my sendobject by CDO.Message but I am not sure if it will work with exchange...

 

[pbaldy]

It's more complicated than that one line, but it certainly works with Exchange, as we use it. You do have to output the report to a file first, as you have to attach it yourself. Here's some code:

  Dim iCfg                    As Object
  Dim iMsg                    As Object

  On Error GoTo ErrorHandler

  'code here to output the file

  Set iCfg = CreateObject("CDO.Configuration")
  Set iMsg = CreateObject("CDO.Message")

  With iCfg.Fields
    .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "address of exchange server"
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
    .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "username"
    .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password"
    .Item("http://schemas.microsoft.com/cdo/configuration/sendemailaddress") = "UserName <UserName@Domain.com>"
    .Update
  End With

  With iMsg
    .Configuration = iCfg
    .Subject = "Subject"
    .To = "whoever@Domain.com"    
    .TextBody ="Body"
    .AddAttachment "Path to attachment"
    .Send
  End With

  Set iMsg = Nothing
  Set iCfg = Nothing

 

[agustinvs]

I will try that, seems a little more flexible that the sendobject. I guess I need to export as many reports as email alerts I am sending.
Thanks Paul.

 

[pbaldy]

If the reports are individualized, yes (it could be considered a downside of not using SendObject). In the code to export, I typically give each a unique name coming from the recordset.