Which Loop do I use?

[sjl]

I'm trying to send an email for each record in a form that meets a specific criteria. The form is based on a query.

As I have it below, the VBA just processes the first record. I am aware of the For....Next, For each....Next and Do.....Loop type loops for repetitive operations, but am not sure which to use in my case.

this is what I have so far (which only processes the first record)....

Private Sub Form_Load()

Dim dtOne As Date
Dim dtDue As Date
Dim strproject As String
Dim strprotocol As String
Dim strstudymgr As String

dtOne = Date 'todays date
dtDue = Me.dtmNPacketDue
strproject = Me.strBrochureName
strprotocol = Me.strNIHProtocolNum
strstudymgr = Me.strSMName


If dtDue > dtOne Then

DoCmd.SendObject , , , "xein@mycompany.com", , , "Due Date Approaching", _
"The due date for" & strproject & ", " & strprotocol & ", is: " & dtDue _
& vbCrLf & "which is xx weeks from today. Please make a note of it." _
& vbCrLf & vbCrLf & "xxx coordinator" & vbCrLf & "Elizabeth Meng", False

End If
End Sub​

thanks in advance, for any insights,

sjl

 

[pbaldy]

I would open a recordset on the query, and use this loop

Do While Not rs.EOF
...
Loop

 

[sjl]

I would open a recordset on the query, and use this loop

Do While Not rs.EOF
...
Loop

Thanks,
Not sure what you mean by opening a "recordset on the query", but I will look into that.

I have seen in a manual the use of rst.EOF (and it seems that rst is VBA for recordset). I see that you used "rs". Is this the same as "rst"?

thanks much,
sjl

 

[boblarson]

I have seen in a manual the use of rst.EOF (and it seems that rst is VBA for recordset). I see that you used "rs". Is this the same as "rst"?

Just an FYI for you - rs isn't by nature a recordset in VBA, but it is frequently used that way. Same for rst. They are just easy ways of naming a variable for a recordset.

 

[odin1701]

Also, make sure you have an rs.MoveNext in between the Do While and Loop, otherwise you will have a very upset person whoever is the first in that recordset. It should be after you send your email so you don't start the loop by moving to the second record.

 

[sjl]

Is this correct?

Thanks gentlemen,
I have taken a stab at the suggestions...is this correct?
Do I name the QUERY that the Form (that I am in) is based on?

Is the Move Next in the right position?

Private Sub Form_Load()
Dim rst As Recordset, db As Database
Set rst = db.OpenRecordset("qryEmail_Notif_NIEHS", dbOpenTable)
Dim dtOne As Date
Dim dtDue As Date
Dim strto As String
Dim strproject As String
Dim strprotocol As String
Dim strstudymgr As String

dtOne = Date 'todays date
dtDue = Me.dtmNPacketDue
strto = Me.strSMName
strproject = Me.strBrochureName
strprotocol = Me.strNIHProtocolNum
strstudymgr = Me.strSMName


Do While Not rst.EOF

'If dtOne = dtmNPacketDue - 70 Then
If dtDue > dtOne Then
'DoCmd.SendObject , , , strto, "Llong, beth", , "Due Date Approaching", _

DoCmd.SendObject , , , "mein@company.com", , , "Due Date Approaching", _
"The due date for" & strproject & ", " & strprotocol & ", is: " & dtDue _
& vbCrLf & "which is xx weeks from today. Please make a note of it." _
& vbCrLf & vbCrLf & "coordinator" & vbCrLf & "Beth Llong", False

End If
rst.MoveNext ''make sure you have an rs.MoveNext in between the Do While and Loop
Loop
End Sub​

 

[pbaldy]

Looks about right, but I suspect you'd want to get values from the recordset, not the form:

dtDue = rst!dtmNPacketDue

and it would have to be inside the loop to get the current value for each pass.

 

[odin1701]

Also - instead of running that on form load - I would assign a command button to run that. Or do something so that it isn't run more than once per day.

 

[sjl]

Looks about right, but I suspect you'd want to get values from the recordset, not the form:

dtDue = rst!dtmNPacketDue

and it would have to be inside the loop to get the current value for each pass.

Yes! Makes sense .... is this placement correct?

Private Sub Form_Load()
Dim rst As Recordset, db As Database
Set rst = db.OpenRecordset("qryEmail_Notif_NIEHS", dbOpenTable)
Dim dtOne As Date
Dim dtDue As Date
Dim strproject As String
Dim strprotocol As String
Dim strstudymgr As String

Do While Not rst.EOF

dtOne = Date 'todays date
dtDue = rst!dtmNPacketDue
strproject = rst!strBrochureName
strprotocol = rst!strNIHProtocolNum
strstudymgr = rst!strSMName


If dtDue > dtOne Then

DoCmd.SendObject , , , "xein@mycompany.com", , , "Due Date Approaching", _
"The due date for" & strproject & ", " & strprotocol & ", is: " & dtDue _
& vbCrLf & "which is xx weeks from today. Please make a note of it." _
& vbCrLf & vbCrLf & "xxx coordinator" & vbCrLf & "Elizabeth Meng", False

End If
rst.MoveNext 'make sure you have an rs.MoveNext in between the Do While and Loop
Loop
End Sub​

Concerning the comment by odin1701: " Also - instead of running that on form load - I would assign a command button to run that. Or do something so that it isn't run more than once per day."

This is a form that will (hopefully) be opened at 2 am each morning, by a "front end" database. I am hoping to get it set up such that it will run just once per day (using the Windows task scheduler, which will conduct the "front end" db to open it). No users will have access to the form.

However, I am not sure of the details, so if this is fuzzy thinking please let me know

 

[odin1701]

As long as you have control over when exactly the form opens, then you're fine.

 

[pbaldy]

Looks like it should work. Have you tested? If you don't want to send the emails during testing, comment that out and pop a message box with the variables, or use Debug.Print to output them to the immediate window.

A couple of nitpicky points. Since dtOne is always the same, I would set it before the loop rather than during. Rather than this test:

If dtDue > dtOne Then

I would change the query to only return records that matched it. That way you're not looping through a lot of records to get to the ones you really want.

 

[sjl]

This is not running....

I am almost there, but I'm getting a "Object variable or With block variable not set" (Run Time Error 91).

The line that seems to be generating the run-time error is this one:

Set rst = db.OpenRecordset("qryEmail_Notif_NIEHS")
(I have taken out the dbOpenTable comment in the Set rst statement b/c sounded like that can't be used for queries ?)


This is my current, not-running, code:

Private Sub Form_Load()
Dim rst As Recordset, db As Database
Set rst = db.OpenRecordset("qryEmail_Notif_NIEHS")
''''''''''''if object refers to a QueryDef object or if source refers to an SQL statement you can't use dbOpenTable for the type argument--a run-time error occurs
Dim dtOne As Date
Dim dtDue As Date
Dim strto As String
Dim strproject As String
Dim strprotocol As String
Dim strstudymgr As String

dtOne = Date 'todays date

Do While Not rst.EOF
dtDue = Me.dtmNPacketDue
'strto = Me.strSMName
strproject = Me.strBrochureName
strprotocol = Me.strNIHProtocolNum
strstudymgr = Me.strSMName

If dtDue > dtOne Then

DoCmd.SendObject , , , "mein@company.com", , , "Due Date Approaching", _
"The due date for" & strproject & ", " & strprotocol & ", is: " & dtDue _
& vbCrLf & "which is xx weeks from today. Please make a note of it." _
& vbCrLf & vbCrLf & "coordinator" & vbCrLf & "Beth Llong", False

End If
'make sure you have an rs.MoveNext in between the Do While and Loop
rst.MoveNext
Loop
End Sub​

I'm also confused as to if I need to define a QueryDef (instead of just referencing the Query)???? .



thanks for any further tips,
sjl

 

[boblarson]

Just change this:

Dim rst As Recordset, db As Database
Set rst = db.OpenRecordset("qryEmail_Notif_NIEHS")


to this:

Dim rst As DAO.Recordset, db As DAO.Database
Set rst = db.OpenRecordset("qryEmail_Notif_NIEHS")

 

[boblarson]

I'm also confused as to if I need to define a QueryDef (instead of just referencing the Query)????

Nope, you can reference a query like that.

 

[sjl]

Object Variable is (still) not Set.....?

Bob,
thanks so much for the input. For some reason, the RunTime 91 error continues....

This is the code, with Bob's substitutions. Does anyone see the "Object variable or With block variable not set"? (run-time error I'm getting).

By the way, this code sits in the On Load event of the Form (which is based on the Query I am naming in the rst below).

Private Sub Form_Load()
Dim rst As DAO.Recordset
Dim db As DAO.Database

Set rst = db.OpenRecordset("qryEmail_Notif_NIEHS")
Dim dtOne As Date
Dim dtDue As Date
Dim strproject As String
Dim strprotocol As String
Dim strstudymgr As String

dtOne = Date 'todays date

Do While Not rst.EOF
dtDue = rst!dtmNPacketDue
strproject = rst!strBrochureName
strprotocol = rst!strNIHProtocolNum
strstudymgr = rst!strSMName

If dtDue > dtOne Then

DoCmd.SendObject , , , "sjl@company.com", , , "Due Date Approaching", _
"The due date for" & strproject & ", " & strprotocol & ", is: " & dtDue _
& vbCrLf & "which is xx weeks from today. Please make a note of it." _
& vbCrLf & vbCrLf & "IRB coordinator" & vbCrLf & "Seth O Connell", False

End If
rst.MoveNext
Loop
End Sub​

​

 

[boblarson]

Yep, just spotted it. You don't instantiate the db object.

Add this line before the Set rst line:

Set db = CurrentDb

 

[sjl]

Bob:

You the man! It now runs. Thanks SOO much.

sjl

 

[boblarson]

 

[sgtSortor]

Did you ever get this to work correctly?

 

[boblarson]

Did you ever get this to work correctly?

Just a thought - I think the poster's statement:

Bob:

You the man! It now runs. Thanks SOO much.

sjl

means that they did get it to work correctly.