Sending Email Dependent on IF Statement

[jbento]

I have a form that is associated with a table of course.

I have a radio button called: chg_hw and a date field called: expIECD

I have code that generates a date in the expIECD field. I have another field called: aprv_date.

I have a report called: rpt_expIECD_HW

I need to generate code to send email if a condition is met.

The code I have for doing this is as follows:

Private Sub Command12_Click()

If chg_hw.Value = True And Me!expIECD = DateAdd("d", 110, Me!aprv_date) Then
DoCmd.SendObject acSendReport, "rpt_expIECD_HW", acFormatSNP, "email@test.com",,, strSubject, "The attached is being sent.", True
End If

End Sub

The problem I am having I need for this part of the code to check every record in the database: Me!expIECD = DateAdd("d", 110, Me!aprv_date). The code only works if I click on the record that meets this criteria, and it only puts that record in the SNP file in the email.

I need it to automatically check every record and add those records to the SNP file.

Does anyone know how can I modify the code to generate this?

Any help will be SURELY appreciated.

Thanks in advance,

Jerome Benton
JERPAT Web Designs

http://www.jerpat.org

GOD Is Good All The Time!!!

 

[Rolliee]

How often do you want to perform this op? You must tie it to and event with oop. There are lots of events. Perhaps on_open of a form in answer to a MsgBox question:

"May I update the 'tickler?'"

Rollie E

 

[jbento]

I have created a form with a command button on it.

All of the code mentioned above is associated with this command button.

I will have a user click this command button each time she opens the database to perform the operation.

Does that help,

Jerome Benton
JERPAT Web Designs

http://www.jerpat.org

GOD Is Good All The Time!!!

 

[Rolliee]

JB,

Use that cmd buttom to loop through the me.recordset with the same checks for each record is one way.

Rollie

 

[jbento]

I am not sure how to set this code up, but I want to ask if this is correct. Please look at the below and let me if this will work or not.

Private Sub Command12_Click()

Dim rst As Recordset


Set db = CurrentDb()
Set rst = db.openrecordset("tbltest", dbopendynaset)
rst.movefirst
Do loop rst.eof

rst.movenext
Do while IsNotNull(rst!expIECD)
rst.MoveNext
loop
rst.movenext
Loop
rst.Close

If chg_hw.Value = True And Me!expIECD = DateAdd("d", 110, Me!aprv_date) Then
DoCmd.SendObject acSendReport, "rpt_expIECD_HW", acFormatSNP, "email@test.com",,, strSubject, "The attached is being sent.", True
End If

End Sub

This is probably all wrong, but I really need help.

Thanks so much for your help so far.


Jerome Benton
JERPAT Web Designs

http://www.jerpat.org

GOD Is Good All The Time!!!

 

[Rolliee]

Let me make you a sample. You are very close. Tell me the data source of the mainform adn the subform and I will write the sample. OK?


Rollie

 

[jbento]

There is no subform, just a mainform.

Table = tbltest

Fields = ID (autonumber)
expIECD (datefield)
aprv_date (datefield)
chg_hw (radio button)

Report = rpt_expIECD_HW

That is it.

I really appreciate your assistance with this!!!

Thanks,

Jerome Benton
JERPAT Web Designs

http://www.jerpat.org

GOD Is Good All The Time!!!

 

[KenReay]

Hi

if the form is bound and you want to move sequentialially through the recordset of teh form you can use code like so:

Private Sub Command12_Click()

Dim rst As Recordset

Set rst = Me.RecordsetClone
rst.movefirst
Do Until rst.eof
me.bookmark = rs.bookmark
If chg_hw.Value = True And Me!expIECD = DateAdd("d", 110, Me!aprv_date) Then
DoCmd.SendObject acSendReport, "rpt_expIECD_HW", acFormatSNP, "email@test.com",,, strSubject, "The attached is being sent.", True
End If
rst.movenext
loop
rst.close
set rst = nothing

End Sub

But from you explanation so far I am not sure this is teh case you use the term "associated with" rather than bound.

Also, to be honest I am not sure what you are trying to do,

are you trying to Email 'n' records to 'n' recipients (which is what the example implies), or are you trying to send a single report to a single recipient, where the content of the report is filtered based on your criteria?, if the latter, you do not need a loop in the form, but ratehr to base the report "rpt_expIECD_HW" on a query with criteria on chg_hw and expIECD


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[jbento]

Ken,
What I am trying to do is get a single report with all of the records in that report that meet the condition. That would be a filtered report.

So I probably can create a query with criteria based on what I need.

I thought about that yesterday, and I told myself that probably wouldn't work, so I should have went with my first instinct.

I will try it and if it doesn't work, I will try your example.

I appreciate your response.

Thanks so much.

Jerome Benton
JERPAT Web Designs

http://www.jerpat.org

GOD Is Good All The Time!!!