problems with loop

[etk]

I have this code (below) that loops through a recordset and sends appointments. It executes the queries correctly and sends all appointments in the table, but sends them only to the contacts listed in the first record of the query. How do I get it to loop the contact details?
Can you point out anything wrong with the code?

Private Sub SchedFollowUp()

Dim rsFollow As DAO.Recordset
Set rsFollow = CurrentDb.OpenRecordset("SELECT * FROM Follow_Up WHERE HR_Approved = True AND Added_to_Outlook = False AND Cancelled = False;", dbOpenDynaset)
Dim rsEmployee As DAO.Recordset
Set rsEmployee = CurrentDb.OpenRecordset("SELECT * FROM Employee INNER JOIN Follow_Up ON Employee.EMP_ID = Follow_Up.Emp_ID;", dbOpenDynaset)
Dim rsMentor As DAO.Recordset
Set rsMentor = CurrentDb.OpenRecordset("SELECT * FROM Employee INNER JOIN Follow_Up ON Employee.EMP_ID = Follow_Up.Mentor_ID;", dbOpenDynaset)

    With rsFollow
        rsFollow.MoveFirst
        Do Until rsFollow.EOF
            Dim outobj As Outlook.Application
            Dim outappt As Outlook.AppointmentItem
            Set outobj = CreateObject("outlook.application")
            Set outappt = outobj.CreateItem(olAppointmentItem)
            With outappt
               .MeetingStatus = olMeeting
               .Start = rsFollow!FU_DT & " " & rsFollow!FU_Start_Time
               .Duration = rsFollow!FU_Duration
               .RequiredAttendees = rsEmployee!EMAIL_ADDRESS & "; " & rsMentor!EMAIL_ADDRESS
               .OptionalAttendees = rsEmployee!TM_EMAIL_ADDRESS & "; " & rsMentor!TM_EMAIL_ADDRESS
               .Subject = "Follow-up Session ID# " & rsFollow!FU_Session_ID
               .Body = "test"
               .Location = "test"
               .ReminderMinutesBeforeStart = 15
               .ReminderSet = True
               .Save
               .Send
            End With
                If rsFollow!Added_to_Outlook = False Then
                    rsFollow.Edit
                    rsFollow!Added_to_Outlook = True
                    rsFollow.Update
                End If
            rsFollow.MoveNext
        Loop
    End With
    
    rsFollow.Close
    Set rsFollow = Nothing
    rsAgent.Close
    Set rsAgent = Nothing
    rsNHD.Close
    Set rsNHD = Nothing
            
End Sub

 

[pbaldy]

You're looping the rsFollow recordset but getting addresses from the rsEmployee & rsMentor recordsets. I don't know your data, but you'd either need to include the addresses in the recordset being looped or also use MoveNext on the other 2.

 

[etk]

The idea behind these recordsets is to keep the db as normalized as possible. For the session table I just have the ID's of the various employee's involved in the meeting and the intention of the additional recorsets is to query the email addresses for these employees from an employee table.

For your second option, you said to use .MoveNext method for the other two recordsets. I attempted this and did not get the desired results. Do you have any recommendations on the structure of that code. I don't think I can open a 'With...End With' around the .RequiredAttendees, similarly .MoveNext put directly after the .RequiredAttendees does the same, it schedules all appointments, but with the same contact details as the first appointment. It does not loop in sync with the recordset for the meetings.

 

[pbaldy]

For the session table I just have the ID's of the various employee's involved in the meeting

If you're saying the employee ID is a field in that table, you should be able to join the employee table in the main recordset's source query. That would let you include the email address as part of that recordset.

 

[etk]

I had thought of doing it that way, and perhaps it highlights a limitation in my knowledge of queries. I understand the join on one of the ID's but I am dealing with 3 IDs in that table, the employee, the mentor, and a manager (which I have not included for simplicity of problem solving). How would I join and get all three emails for the various ID's? Or should I break apart the employee table into tables of their respective roles?

 

[pbaldy]

In query design view, you'd add the employee table 3 times. Access will alias the 2nd and 3rd like "Employee_1". Then you join each of your 3 fields to a different instance of the employee table.

 

[etk]

SELECT Follow_Up.*, Mentor.EMAIL_ADDRESS AS Mentor_Email, Emp.EMAIL_ADDRESS AS Employee_Email
FROM Follow_Up
INNER JOIN Employee AS Mentor
ON Follow_Up.Mentor_ID = Mentor.Emp_ID
INNER JOIN Employee AS Emp
ON Follow_Up.Emp_ID = Emp.Emp_ID;

I tried your alias suggestion, but I keep running into problems with the query. If I join just one of the aliases then it works just fine, but if I join both then it gives me an error: 'Syntax error (missing operator) in query expression' Any clues?

 

[pbaldy]

If you're in Access, it probably wants some parentheses around the joins. Try

SELECT Follow_Up.*, Mentor.EMAIL_ADDRESS AS Mentor_Email, Emp.EMAIL_ADDRESS AS Employee_Email
FROM (Follow_Up
INNER JOIN Employee AS Mentor
ON Follow_Up.Mentor_ID = Mentor.Emp_ID)
INNER JOIN Employee AS Emp
ON Follow_Up.Emp_ID = Emp.Emp_ID;

 

[etk]

returns a different error with that placement of paretheses;

'Syntax error in JOIN operation'

 

[etk]

my apologies, I made a typo, missed one character


It works beautifully, many thanks!!

 

[pbaldy]

Happy to help!