If all you have left is to validate the Email field within your recordset, you could validate within your loop. Just enclose the task of sending hte email within the validation. Here I use the function I showed you the other day to test for both Null and empty string. Also, if you only want to email to a specific domain, you can use the InStr function to make sure you are not emailing outside the domain.
Code:
Do Until rs.EOF
If Not isNothing(rs![EmailAddress]) Then
strEMail = rs![EmailAddress]
If InStr(2, naofsc, "@SchoolDomain.com") Then 'Check and see if a valid school address
DoCmd.SendObject , , , "email@email.com.au", , , emsubject & " " & naofsc, messagebody, no
End If 'Valid school address
End If 'Check for Email Address
rs.MoveNext
Loop
However, it seems you still may need to validate other fields such as SchoolName. You could add another If Then statement and use the isNothing Function. I would add it as the first check.
Something Like
Code:
Do Until rs.EOF
If Not isNothing(rs![SchoolName]) Then 'Check for School Name
naofsc = rs![SchoolName]
If Not isNothing(rs![EmailAddress]) Then 'Check for Email Address
strEmail = rs![EmailAddress]
If InStr(2, naofsc, "@SchoolDomain.com") Then 'Check and see if a valid school address
DoCmd.SendObject , , , "email@email.com.au", , , emsubject & " " & naofsc, messagebody, no
End If 'Valid school address
End If 'Check for Email Address
End If 'Check for School Name
rs.MoveNext
Loop
If it was me, I would assign the recordsource of the form at the load event of the form using an SQL string. I would declare two string variables at the top of the module below the haeader.
Dim strSQL as String
dim strWhere as String
In the load event of the form I would assign the recordsource via the SQL string. You can base it off of a saved query.
Code:
strWhere = "FieldPK > 0"
strSQL = "SELECT MyQry.* " & _
"FROM MyQry " & _
"WHERE " & strWhere & _
"ORDER BY [FieldPK];"
Me.RecordSource = strSQL
Now I can adjust strWhere at anytime I like.
strWhere = (strWhere & " AND ") & "[SomeField] = '" & Me.txtField.Value & "'"
Now that strWhere is adjusted, I can adjust strSQL
Code:
strSQL = "SELECT MyQry.* " & _
"FROM MyQry " & _
"WHERE " & strWhere & _
"ORDER BY [FieldPK];"
Now I can avoid touching the form and jusat open up a DAO recordset based on the SQL string I created.
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)