Creating appointments from table, looping list

E

etk

Registered User.
Local time
Today, 08:40
Joined
May 21, 2011
Messages
52
I have a table (Session_lst) which is a list of appointments. It has the following fields

Session_lst
---------------------
Session_ID
Session_date
Session_time
Session_duration
Agent
Agent_mgr
Appt_booked
---------------------

I want the table to book the appointments with myself and the Agent, and the Agent_mgr (as optional) in outlook calendar. I am comfortable with the VBA to schedule the meeting, but I struggle with loop to go through the table. I want the loop to do something like this:

Loop through table. Enter any appointment for which field Appt_booked <> true
For each appointment added to outlook, set Appt_booked = true

However, I also forsee that appointments will get cancelled and rebooked, re-using the same Session_ID, but updating the record. So I think I need to use a For Each loop, rather than a Do while loop, but I am not so confident with my loop.

I also found a thread about recordsets on this forum. Am I on the right path by using a recordset?
 
Check the "Similar threads" section at the bottom of the page.
 
I took a stab at this. Here is what I've got. There are a couple extra fields than the initial table.

PHP: 
Private Sub SchedOutlook()


Dim rsSesssion As Recordset
Set rsSession = CurrentDb.OpenRecordset("SELECT * FROM Session_lst WHERE HR_Apprvd = True AND Appt_booked = False", dbOpenDynaset)

    With rsSession
        rsSession.MoveFirst
        Do Until rsSession.EOF
                    
            Dim outobj As Outlook.Application
            Dim outappt As Outlook.AppointmentItem
            Set outobj = CreateObject("outlook.application")
            Set outappt = outobj.CreateItem(olAppointmentItem)
            With outappt
               .Start = Session_date & " " & Session_time
               .Duration = Session_duration
               .RequiredAttendees = Agent_Email & Trainer_Email
               .OptionalAttendees = Mgr_Email
               .Subject = "Refresher Training" & Session_ID
               .Body = "This is a Test"
               .Location = "Via Phone"
               .ReminderMinutesBeforeStart = 15
               .ReminderSet = True
               .Save
            End With
        Loop
        rsSession.MoveNext
    End With
    
    rsSession.Close
    Set rsSession = Nothing
            
End Sub

I am having issues with the SQL Query. I get run time error 3061: too few parameters, Expected: 2

What is the problem with the query?

Will the rest of this code do what I want or are there other problems that I should address?
 
I got past the runtime error 3061. It happened to be a punctuation mark that I accidentally put into my code in Access.

I am however, having issues with this segment of the code
PHP: 
Dim outobj As Outlook.Application
            Dim outappt As Outlook.AppointmentItem
            Set outobj = CreateObject("outlook.application")
            Set outappt = outobj.CreateItem(olAppointmentItem)
            With outappt
               .Start = Session_DT & " " & Session_Time
               .Duration = Session_Duration
               .RequiredAttendees = Agent_Email & NHD_Email
               .OptionalAttendees = Mgr_Email
               .Subject = "Peer to Peer Session" & Session_ID
               .Body = "This is a Test"
               .Location = " Via Phone"
               .ReminderMinutesBeforeStart = 15
               .ReminderSet = True
               .Save
            End With
When I originally created this code using a snipet from MSDN each portion of the outlook appointment began with Me!, but I run into 'invalid use of the Me keyword' . If I delete the Me! then I get runtime error 13, type mismatch. How do I successfully add the Me! into each outlook item?
 
Where is the code? You can't use Me in a standard module, as it's just a shortcut to refer to the object containing the code. I would think you want values from the recordset, which would look like:

rsSession!FieldName
 
Hi, yes, the rsSession! did fix the problem. I think the code is fine now, but I have other problems. The machine I created this on is a thin client and it can't perform the code. It crashes every time. I have a desktop with Access 2007, but it does not seem to recognize the recordset. I am missing a Library or something, but I have all the same Libraries (though some are older versions) selected as I do in Access 2010. Is there a way to make this use less resources in 2010 or work in 2007?
PHP: 
Private Sub SchedOutlook()


Dim rsSesssion As Recordset
Set rsSession = CurrentDb.OpenRecordset("SELECT * FROM Session_lst WHERE HR_Apprvd = True AND Appt_booked = False", dbOpenDynaset)

    With rsSession
        rsSession.MoveFirst
        Do Until rsSession.EOF
                    
            Dim outobj As Outlook.Application
            Dim outappt As Outlook.AppointmentItem
            Set outobj = CreateObject("outlook.application")
            Set outappt = outobj.CreateItem(olAppointmentItem)
            With outappt
               .Start = rsSession!Session_date & " " & rsSessionSession_time
               .Duration = rsSession!Session_duration
               .RequiredAttendees = rsSession!Agent_Email & rsSession!Trainer_Email
               .OptionalAttendees = rsSession!Mgr_Email
               .Subject = "Refresher Training" & rsSession!Session_ID
               .Body = "This is a Test"
               .Location = "Via Phone"
               .ReminderMinutesBeforeStart = 15
               .ReminderSet = True
               .Save
            End With
        Loop
        rsSession.MoveNext
    End With
    
    rsSession.Close
    Set rsSession = Nothing
            
End Sub
 
Last edited:
In older versions you'd need the MS DAO reference checked, and you should disambiguate this:

Dim rsSesssion As DAO.Recordset
 
Still having issues with this code. I attempted to run the code and it created 6600 appointments before it crashed on a dummy database with 3 records in it, one of which it should have ignored due to the recordset query. Any reason this is not picking up on the EOF?

I also had the intention to update the field Apt_in_outlk to 'true' upon adding the appointment to outlook. Should I do this with an update query or use the recordset to do the update like in this thread: http://www.access-programmers.co.uk/forums/showthread.php?t=77701

PHP: 
Private Sub SchedOutlook()


Dim rsSesssion As DAO.Recordset
Set rsSession = CurrentDb.OpenRecordset("SELECT * FROM tblSession WHERE RPO_Apprvd = True AND Apt_in_outlk = False", dbOpenDynaset)

    With rsSession
        rsSession.MoveFirst
        Do Until rsSession.EOF
                    
            Dim outobj As Outlook.Application
            Dim outappt As Outlook.AppointmentItem
            Set outobj = CreateObject("outlook.application")
            Set outappt = outobj.CreateItem(olAppointmentItem)
            With outappt
               .Start = rsSession!Session_DT & " " & rsSession!Start_Time
               .Duration = rsSession!Session_Duration
               .RequiredAttendees = rsSession!Agent_Email & ";" & rsSession!NHD_Email
               .OptionalAttendees = rsSession!Mgr_Email
               .Subject = "Peer to Peer Session" & rsSession!Session_ID
               .Body = "This is a Test"
               .Location = " Via Phone"
               .ReminderMinutesBeforeStart = 15
               .ReminderSet = True
               .Save
            End With
    
        Loop
        rsSession.MoveNext
    End With
    
    rsSession.Close
    Set rsSession = Nothing
            
End Sub
 
You must log in or register to reply here.

Similar threads

A
Solved VBA code for deleting Outlook 'Appointments' lying within a specified date range
Replies
8
Views
1,431
Gasman
Gasman
C
Help with looping through records
Replies
11
Views
645
cricketbird
C
G
Solved Update recordset skipping the last record
2
Replies
21
Views
837
The_Doc_Man
The_Doc_Man
B
Database structure
Replies
19
Views
1,856
LarryE
LarryE
avincent61
  • Question Question
DateAdd with DMax +1, not updating table data
Replies
10
Views
1,350
avincent61
avincent61

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom