Controlling who gets an email

[DiveKennene]

OK. I am actually making progress on my project. I have a database set up to automatically send emails to certain customers when a certain time frame comes up (set up through a query that "gathers" all customer emails that fall within a certain time frame). My code goes through the list and sends out the emails one by one until everyone in the query has had an email sent to them. What I need to do now is: after the email is sent to joe at anything dot com, I need to check the checkbox (Check107) that is on my main form (called "Customers") and as this code loops it searches for any customer email that is checked so as to not send emails every time the form loads. Here is the working code I have for sending the emails. Any help is hugely appreciated.

Private Sub Form_Load()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strEmail As String

Set db = CurrentDb
Set rs = db.OpenRecordset("Repack Reminder Query")

Do While Not rs.EOF
strEmail = rs.Fields("Email")
DoCmd.SendObject , , HTML, strEmail, , , "Reserve is going out of Date!", "Please be advised, your Reserve is going out of date soon!", False
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End Sub

 

[pbaldy]

I'm not sure it's what you're really after, but:

Me.Check107 = True

Will check the box, assuming the code is on that form.

 

[DiveKennene]

That's the right direction. Unfortunately the code in my example is on the form "Email Reminder" and the check box is on a form called "Customers". I am not sure what the syntax would be for setting the check box to true as my form loops through the emails to send.

 

[pbaldy]

Well, that would be

Forms!Customers.Check107 = True

But I suspect what you might want is within the loop:

rs.Edit
rs!FieldName = True
rs.Update

 

[DiveKennene]

Okay...I put in the code: Forms!Customers.Check107 = True
and I keep getting the error message that it can't find the form. The form is there in my project, I am sure of that. What am I missing?

 

[DiveKennene]

Okay, new update...I got the Forms!Customers.Check107=True to kinda work. I send out 2 emails and it ticked the checkbox for the first customer but not the second one. Now I am really confused. Here is my code at this point.

Private Sub Form_Load()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strEmail As String
Dim cb As Control

Set db = CurrentDb
Set rs = db.OpenRecordset("Repack Reminder Query")

Do While Not rs.EOF
strEmail = rs.Fields("Email")
DoCmd.SendObject , , HTML, strEmail, , , "Reserve is going out of Date!", "Please be advised, your Reserve is going out of date soon!", False
rs.MoveNext
Forms!Customers.Check107 = True
Loop
rs.Close
Set rs = Nothing
End Sub

 

[pbaldy]

Try the second code. Since you're stepping through a recordset, I'd just set the value in the underlying table rather than on the form. You can requery the form at the end if you want to display the updated values.

 

[DiveKennene]

Please excuse my ignorance, I am a rookie when it comes to code. What second code and where would I put it?

 

[DiveKennene]

Again, I'm real new to this...How do I set the value to the underlying table?

 

[pbaldy]

This is what I meant

But I suspect what you might want is within the loop:

rs.Edit
rs!FieldName = True
rs.Update

 

[DiveKennene]

Got It!

Thanks for all your help...I finally got it to work. For being a newbie, I am impressed with what I have accomplished with everyones help. In case anyone is interested, here is my final product, it's called "Riggers Friend Database":

http://www.packing-made-simple.com/rigger.html

Thank you all again.

 

[pbaldy]

Link doesn't work, but glad you got it sorted out.

 

[DiveKennene]

Sorry, was having some issues with my webhost...apparently they like getting paid. The link is working now.