Working with Loop??

[Aliffi]

Dear all,

I have a SQL SERVER back end db, front end ADP

I am going to develope a monitoring form on contract based table. which will check if the Date is > Current Date then it will automatically sends an alert msg to person email

i put this code to Form Load

________________________________________________________
If Me.Date_Planed > Now() Then
DoCmd.SendObject acSendNoObject, , , "farzam.aliffi@cec.eu.int", , , "Job Alert", [TaskM],, No
Else
MsgBox ("No Jobs are due yet,to be alerted Thank you!")
End If
---------------------------------------------------------
it works fine for only first record,
but i want to use a kind of loop and records set to
check all the records in underlaying table or form itself
and do the alert for any record whos date is > Now()

waiting for your replys
thanks
fazam

 

[IanAble]

In general for a Form you'd create a DAO recordset reference and loop through the records like this.

Dim myRecordset as DAO.Recordset

Set myRecordset = Me.RecordsetClone
With myRecordSet
Do Until .EOF
---Action Code---
.MoveNext
Loop
End With

You'd use the same principle if you want to access the table directly via an ADO recordset.

Whichever object model you'd use I suggest that you wrap the SendObject arguments in a seperate function e.g.

Private Function SendEMail(EMail As String,Subject As String,TaskM As Variant) As Boolean
Onerror Goto Error_Handling

DoCmd.SendObject acSendNoObject, , , EMail , , , Subject, TaskM,, No

SendEMail=True

Exit_SendEMail:
Exit Function

Error_Handling:
---Add Message Box if you want error feedback---
Resume Exit_SendEMail
End Function

It'll make error handling easier and using the value of the function you could update a table telling you if the e-mails have been sent or not.

 

[PHV]

I guess that an ADP uses ADODB instead of DAO.

 

[Aliffi]

Well, I have used your code with some slight changes
as follows it is working but assume that i have

3 Tasks which is checked by a IF function for date_planed Filed to be higher than NOW()
and it is under that loop , it checks the Field WEll but it sends only the first record for 3 msgs. it should send each record ... with different msgs
but dsont
any suggestion plz
here is the code i used

-----------------------------------------------
Dim myRecordset As Recordset


Set myRecordset = Me.RecordsetClone
With myRecordset
Do Until .EOF
If Me.Date_Planed > Now() Then
DoCmd.SendObject
'------------------------------------------------------
'Sends the MSG to email well
'------------------------------------------------------
acSendNoObject, , , "farzam.aliffi@cec.eu.int", , , "Job Alert", "Dear " + [TaskM] + vbCrLf + "Please carry on with the following Task " + vbCrLf + "------------------------------------------------" + vbCrLf + vbCrLf + [Task] + vbCrLf + vbCrLf + "------------------------------------------------" + vbCrLf + vbCrLf + vbCrLf + vbCrLf + vbCrLf + "Regards," + vbCrLf + "Database Section ", No

Else
MsgBox ("No Jobs are due yet,to be alerted Thank you!")
End If
.MoveNext
Loop
End With
-----------------------------------------------------------
thnks

 

[IanAble]

A couple of points to begin with.

1. If you are going to use the RecordsetClone (a DAO object) you must declare it as a DAO recordset otherwise Access will assume it's an ADO object and the results could be unpredictable.

Dim myRecordset As DAO.Recordset

2. As I said I strongly suggest you wrap the action code in a seperate function. As well as all the other benefits I mentioned it will make debugging easier.

OK. I think your problem is with this line:
If Me.Date_Planed > Now() Then

1. The value of Me.Date_Planed will be the value of the current record only. You should use the value in the record you looking at i.e. myRecordset![Date_Planed].

2. It makes sense to not check the current date all the time. Set the value of a date variable at the begining of the routine dtNow = Now().

3. Finally use CDate([Date_Planned]) to convert [Date_Planned] into the same format as dtNow.

You can test these points and if you starting at the begining of the recordset by doing this:

Dim myRecordset as DAO.Recordset
Dim dtNow as Date

dtNow = Now()
Set myRecordset = Me.RecordsetClone
With myRecordSet
Do Until .EOF
debug.print "Record " & .AbsolutePosition + 1 & " of " & .RecordCount
If IsDate(![Date_Planed]) Then
debug.print CDate(![Date_Planed]) & " > " & dtNow & " Is & (CDate(![Date_Planed]) > dtNow )
End If
.MoveNext
Loop
End With
Set myRecordset = Nothing

Let me know how you get on.

 

[Aliffi]

It gives an error at the line

Dim myRecordset as DAO.Recordset

and it says Runtime Error 13 miss match type

???

Idea

 

[PHV]

Reread my post stamped 17 Oct 06 8:23

 

[IanAble]

Fair comment PHV...Aliffi it should be
Dim myRecordset As ADODB.Recordset

 

[kjv1611]

Wow, Aliffi, I never knew a computer would refer to a person like that!

[highlight]miss[/highlight] match type

[LOL]

You know, she's the type that does all the matching!