Msgbox and Exit Sub Issue......

[doran_doran]

I am using following code to collect e-mail address from a subform to a text and then e-mailing them.

I would like for system to pop a message "You must populate at least one e-mail address" when the recordset is empty.

The following code puts all the records in a field call txttotalrecipients from a subform which is tie to a temp table called tbltmprecipients.

It's all working accept the msgbox and exit sub portion. May be i have them in the wrong place.

= = = code begin here = = =
Private Sub cmdSendMail_Click()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim Recipients As String

Set db = currentdb()
Set rst = db.OpenRecordset("tbltmpRecipients", dbOpenDynaset)

If IsNull(Recipients) Then
MsgBox "You must populate at lease one e-mail address"
Exit Sub
Else
rst.MoveFirst
Recipients = rst.Fields("TotalRecipients")
rst.MoveNext

Do While Not rst.EOF
Recipients = Recipients & ", " & rst.Fields("totalrecipients")
rst.MoveNext
Loop
End If

Me.txtTotalRecipients = Recipients
rst.close
Set dbs = Nothing
Call SendNotesMailCOMVersion

End Sub

 

[pbaldy]

If I'm correctly interpreting what you want, replace

If IsNull(Recipients) Then

with

If rst.EOF Then

 

[doran_doran]

New code...

Thanks for the help. The new code is working like charm. However, if user put an e-mail address into the subform and take it out then is not working. I wonder why. The subform looks like it has one blank record. Beside that it's working.

= = = New Code = = =
Private Sub cmdSendMail_Click()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim Recipients As String

Set db = currentdb()
Set rst = db.OpenRecordset("tbltmpRecipients", dbOpenDynaset)

If rst.EOF Then
MsgBox "You must populate at lease one e-mail address"
Exit Sub
Else
rst.MoveFirst
Recipients = rst.Fields("TotalRecipients")
rst.MoveNext

Do While Not rst.EOF
Recipients = Recipients & ", " & rst.Fields("totalrecipients")
rst.MoveNext
Loop
End If

Me.txtTotalRecipients = Recipients
rst.close
Set dbs = Nothing
Call SendNotesMailCOMVersion

End Sub

 

[doran_doran]

Working Version....

Private Sub cmdSendMail_Click()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim Recipients As String

Set db = currentdb()
'Set rst = db.OpenRecordset("tbltmpRecipients", dbOpenDynaset)
Set rst = db.OpenRecordset("Select * from tbltmpRecipients Where TotalRecipients Is Not Null", dbOpenDynaset)

If rst.EOF Then
MsgBox "You must populate at lease one e-mail address"
Exit Sub
Else
rst.MoveFirst
Recipients = rst.Fields("TotalRecipients")
rst.MoveNext

Do While Not rst.EOF
Recipients = Recipients & ", " & rst.Fields("totalrecipients")
rst.MoveNext
Loop
End If

Me.txtTotalRecipients = Recipients
rst.close
Set dbs = Nothing
Call SendNotesMailCOMVersion

End Sub