Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2009
    Posts
    61

    Unanswered: Conflicting vacation Dates

    Hi There,

    I am building an employee leave request database. The idea is that when employees request time off for vacation or leave of absence, they can request it through the database. I have two things at this point that I need help with.

    1. I need a way to view conflicting dates. i.e. when an employee requests a period off, I need a way to view any other dates that fall within the same range. That means either that exact same dates, or dates that even conflict by one day.

    2. If I approve the requested dates, I would like to be able to upload those dates to my outlook calendar.

    The database is set up as follows:

    Table: VacRequests
    Fields:
    EmployeeID - Employee requesting time
    VacRequestSrtDate - Start date
    VacRequestFinDate - Finish Date
    LeaveType - Vacation, leave of absence, etc
    Comments - Employee comments
    Approved - If it is approved
    Denied - If it is denied
    DateRequested - Filled in automatically with Date() function
    DeniedText - Why the request was denied

    I have a form running that filters out all open requests (those that have not been approved and have not been denied). I would like to add all functionality to the form. So I would like to have a way to view any conflicting dates in the table. I would like to see all dates regardless of if they have been approved or denied. My first thought was to use a subform to view the dates, but I am unable to set the filter on the date range.

    For the Outlook portion of my problem, I would like to be able to click the approved field (yes/no) and have the code run and deposit those dates into my calendar along with the employee name.

    If more information is required let me know.

    Thanks in advance!!

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    For the first, does this help?

    Overlapping records

    For the second, this should get you started:

    ACC2000: How to Use Automation to Add Appointments to Microsoft Outlook
    Paul

  3. #3
    Join Date
    Aug 2009
    Posts
    61
    So I tried to play around with the code for problem one for now. I have inserted the code as follows into the form load event. I am not sure if this is the correct place and I am getting a compile error.

    Private Sub Form_Load()
    SELECT (EmployeeID, VacRequestSrtDate, VacRequestFinDate)
    FROM VacRequests
    WHERE VacRequestSrtDate <= Forms![VacRequests Subform].txtVacRequestFinDate And VacRequestFinDate >= Forms!FormName.txtVacRequestSrtDate

    End Sub

    Should I use the code in this way or start a seperate module and then call that module. How would I do that?

  4. #4
    Join Date
    Aug 2009
    Posts
    61
    okay I have tackled problem two. The code works fine and I was able to get access talking to Outlook (I needed to select the Object libary 14 in order to get it working for Access 2007).

    Here is the code I am working with. The only thing that I need to change is the reoccurence. Right now it creates a one because it is a section of the code. I have played around with my form and am able to get it to add the duration into my Calendar, but it still calls it a reoccurance. It is not a big deal, but I am concerned that if it is done incorrectly by the operator, it will cause problems (i.e hundreds of reoccurances in my calendar).

    The code is as follows:

    Private Sub cmdAddAppt___Click()
    On Error GoTo Add_Err

    'Save record first to be sure required fields are filled.
    DoCmd****nCommand acCmdSaveRecord

    'Exit the procedure if appointment has been added to Outlook.
    If Me!Approved = True Then
    MsgBox "This appointment is already added to Microsoft Outlook"
    Exit Sub
    'Add a new appointment.
    Else
    Dim objOutlook As Outlook.Application
    Dim objAppt As Outlook.AppointmentItem
    Dim objRecurPattern As Outlook.RecurrencePattern

    Set objOutlook = CreateObject("Outlook.Application")
    Set objAppt = objOutlook.CreateItem(olAppointmentItem)

    With objAppt
    .Start = Me!VacRequestSrtDate & " " & Me!VacRequestSrtTime
    .Duration = Me!VacRequestLength
    .Subject = Me![EmployeeID] & " , " & [LeaveType]

    If Not IsNull(Me!Comments) Then .Body = Me!Comments
    If Not IsNull(Me!ApptLocation) Then .Location = Me!ApptLocation
    If Me!ApptReminder Then
    .ReminderMinutesBeforeStart = Me!ReminderMinutes
    .ReminderSet = True
    End If

    Set objRecurPattern = .GetRecurrencePattern

    With objRecurPattern
    .PatternStartDate = VacRequestSrtDate
    'You could get these values
    'from new text boxes on the form.
    .PatternEndDate = VacRequestFinDate
    End With

    .Save
    .Close (olSave)
    End With
    'Release the AppointmentItem object variable.
    Set objAppt = Nothing
    End If

    'Release the Outlook object variable.
    Set objOutlook = Nothing

    'Set the AddedToOutlook flag, save the record, display a message.
    Me!Approved = True
    DoCmd****nCommand acCmdSaveRecord
    MsgBox "Appointment Added!"

    Exit Sub

    Add_Err:
    MsgBox "Error " & Err.Number & vbCrLf & Err.Description
    Exit Sub
    End Sub

  5. #5
    Join Date
    Aug 2009
    Posts
    61
    okay figured that out. I just used comment mark on thos lines of the code, which stopped the reoccurence.

  6. #6
    Join Date
    Aug 2009
    Posts
    61
    Quote Originally Posted by pbaldy View Post
    For the first, does this help?

    Overlapping records

    For the second, this should get you started:

    ACC2000&#58; How to Use Automation to Add Appointments to Microsoft Outlook
    Hi pbaldy,

    So I now have this code working really well. I made some adjustments in my form and the code in order to make it fit into what I need. Since starting this project it has expanded and now we would like to have teh appointments go into a shared calendar so that all memebers of the management team can see upcoming dates. My problem is now trying to get this code to send the appointment to a shared calender, rather than my default. I have been searching around for a solution and have found the following:

    Private Sub FormHeader_Click()

    Dim objNS As Outlook.NameSpace
    Dim objFolder As Outlook.MAPIFolder 'get name of other persons folder
    Dim objRecip As Outlook.Recipient 'other persons name
    Dim strName As String 'the name or email of the persons folder
    Dim objAppt As Outlook.AppointmentItem
    Dim objApp As Outlook.Application


    On Error Resume Next

    ' ### name of person whose Calendar you want to use ###
    strName = "Mike Leblanc"

    'This example assume that the Outlook object is already alive.

    Set objNS = objApp.GetNamespace("MAPI")

    Set objRecip = objNS.CreateRecipient(strName)

    Set objFolder = objNS.GetSharedDefaultFolder(objRecip, olFolderCalendar)

    If Not objFolder Is Nothing Then
    Set objAppt = objFolder.Items.Add

    If objAppt Is Nothing Then
    Set objAppt = objApp.CreateItem(olAppointmentItem)
    End If


    Else
    MsgBox "no access to the folder meaning it is not shared"
    End If



    Mycode that is working perfectly for my default calendar is as follows:



    Private Sub cmdAddAppt___Click()
    On Error GoTo Add_Err

    'Save record first to be sure required fields are filled.
    DoCmd****nCommand acCmdSaveRecord

    'Exit the procedure if appointment has been added to Outlook.
    If Me!Approved = True Then
    MsgBox "This appointment is already added to Microsoft Outlook"
    Exit Sub
    'Add a new appointment.

    Else
    Dim objOutlook As Outlook.Application
    Dim objAppt As Outlook.AppointmentItem
    Dim objRecurPattern As Outlook.RecurrencePattern

    Set objOutlook = CreateObject("Outlook.Application")
    Set objAppt = objOutlook.CreateItem(olAppointmentItem)

    With objAppt
    .Start = Me!startDate & " " & Me!VacRequestSrtTime
    .Duration = Me!VacRequestLength
    .Subject = Me![Employee] & " , " & [LeaveType]

    If Not IsNull(Me!Comments) Then .Body = Me!Comments
    If Not IsNull(Me!ApptLocation) Then .Location = Me!ApptLocation
    If Me!ApptReminder Then
    .ReminderMinutesBeforeStart = Me!ReminderMinutes
    .ReminderSet = True
    End If

    'Set objRecurPattern = .GetRecurrencePattern

    ' With objRecurPattern
    '.PatternStartDate = Startdate
    'You could get these values
    'from new text boxes on the form.
    '.PatternEndDate = VacRequestFinDate
    'End With

    .Save
    .Close (olSave)
    End With
    'Release the AppointmentItem object variable.
    Set objAppt = Nothing
    End If

    'Release the Outlook object variable.
    Set objOutlook = Nothing

    'Set the AddedToOutlook flag, save the record, display a message.
    Me!Approved = True
    DoCmd****nCommand acCmdSaveRecord
    MsgBox "Appointment Added!"
    DoCmd.Requery

    Exit Sub

    Add_Err:
    MsgBox "Error " & Err.Number & vbCrLf & Err.Description
    Exit Sub
    End Sub


    My question really is how do I combine the two pieces of code and have the ability to either choose which calendar to send it to, or have it default to the shared calendar (lets call it "Shop Appointments").

    Any help on this would be really appreciated.

    Thanks!

  7. #7
    Join Date
    Aug 2009
    Posts
    61
    Does anybody have a solution for this problem?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •