Pull Data from Recordset

[RedHeadedMonster via AccessMonster.com]

I have a form in which a manager fills out. A subform on the form contains a
Defualt list of submanagers and the manager would be able to select which
managers need to be notified of this forms action. The form would then send
an automatic email to all the submanagers so that they be aware of the issue.

Heres the code Im having issues with:

Dim rstEmail As DAO.Recordset
Dim Iemail As Integer

Set rstEmail = Me.frmLUDefaultStakeholders.Form.Recordset

With rstEmail
If .RecordCount > 0 Then
.MoveLast
.MoveFirst
End If
End With


For Iemail = 1 To rstEmail.RecordCount
If Me.frmLUDefaultStakeholders.Form.shYes = True Then
If msgTo = "" Then
msgTo = Me.frmLUDefaultStakeholders.Form.EmailAssy
Else
msgTo = msgTo & ";" & Me.frmLUDefaultStakeholders.Form.EmailAssy
End If
rstEmail.MoveNext
End If
Next Iemail

Heres the problem " If Me.frmLUDefaultStakeholders.Form.shYes = True Then"
If this is removed the email works perfectly populated with all the emails of
the recordset, however I only want the selected (shYes = True) managers to
receive the email. When that line is left in, the email still comes up, but
there is NO names in the msgTO.

Any idea what I've left out or done wrong?
Thanx!
RHM

 

[Daryl S]

RedHeadedMonster -

I am surprised it works as is. You are looping through the rescordset, so
you should be testing values of the recordset. Try this:

For Iemail = 1 To rstEmail.RecordCount
If rstEmail!shYes = True Then
If msgTo = "" Then
msgTo = rstEmail!EmailAssy
Else
msgTo = msgTo & ";" & rstEmail!EmailAssy
End If
rstEmail.MoveNext
End If
Next Iemail