On my main table I have organization information of different locations (location, unit name, etc.). The primary key is UnitID (autonumber).
On my secondary table, I have all the trainers assigned to the entire organization. I have a UnitID field (number) to associate the trainers to the corresponding unit on the main table (name, email address, etc.).
I have a form to show each location on my main table. I created a subform showing the trainers assigned to that unit (linked by UnitID field).
(1) I want to send an email to only the trainers that are associated with the record. The following pulls all the trainers. How do I pull only the trainers from the current record? Should there be criteria in the qryTest? Other? Any help is appreciated.
On Error Resume Next
Dim db As DAO.Database
Dim rs As Object
Dim iCount As Integer
Dim rsEmail As DAO.Recordset
Dim vRecipientList As String
DoCmd.RunCommand acCmdSaveRecord
Set db = CurrentDb()
Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryTest")
If rs.RecordCount > 0 Then
rs.MoveFirst
Do
If Not IsNull(rs!COREmail) Then
If Not IsNull(rs!COREmail) Then
vRecipientList = vRecipientList & rs!COREmail & ";"
End If
End If
rs.MoveNext
Loop Until rs.EOF
DoCmd.SendObject acSendNoObject, , , vRecipientList, , , , "Sir/Ma'am," & vbCr & vbCr & vbCr & _
"vr", , True
End If
Error_Handler_Exit:
On Error Resume Next
Exit Sub
Error_Handler:
If Err.Number <> 0 Then Err.Clear
Resume Error_Handler_Exit
On my secondary table, I have all the trainers assigned to the entire organization. I have a UnitID field (number) to associate the trainers to the corresponding unit on the main table (name, email address, etc.).
I have a form to show each location on my main table. I created a subform showing the trainers assigned to that unit (linked by UnitID field).
(1) I want to send an email to only the trainers that are associated with the record. The following pulls all the trainers. How do I pull only the trainers from the current record? Should there be criteria in the qryTest? Other? Any help is appreciated.
On Error Resume Next
Dim db As DAO.Database
Dim rs As Object
Dim iCount As Integer
Dim rsEmail As DAO.Recordset
Dim vRecipientList As String
DoCmd.RunCommand acCmdSaveRecord
Set db = CurrentDb()
Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryTest")
If rs.RecordCount > 0 Then
rs.MoveFirst
Do
If Not IsNull(rs!COREmail) Then
If Not IsNull(rs!COREmail) Then
vRecipientList = vRecipientList & rs!COREmail & ";"
End If
End If
rs.MoveNext
Loop Until rs.EOF
DoCmd.SendObject acSendNoObject, , , vRecipientList, , , , "Sir/Ma'am," & vbCr & vbCr & vbCr & _
"vr", , True
End If
Error_Handler_Exit:
On Error Resume Next
Exit Sub
Error_Handler:
If Err.Number <> 0 Then Err.Clear
Resume Error_Handler_Exit