UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Importing Outlook Appointments To Access And Deleting, Access 2016    
 
   
wornout
post Mar 14 2019, 05:59 PM
Post#1



Posts: 1,285
Joined: 17-November 13
From: Orewa New Zealand


I have a calendar(from peter Hibbs) and I import appointments and events correct.
I also can delete in access but when I delete from my access calendar it does not delete in outlook.
The problem is when I send the appointment to outlook from access the appointment ID is sent to the millage in outlook
But when I do the appointment in outlook and import it to access it does not come with an ID in mileage so if I have to delete it access does not know the id to delete in outlook.
How do I get around this,
Do I do something in access or outlook
Go to the top of the page
 
theDBguy
post Mar 14 2019, 06:16 PM
Post#2


Access Wiki and Forums Moderator
Posts: 74,678
Joined: 19-June 07
From: SunnySandyEggo


Hi. You could try using the Outlook assigned EntryID property to identify the appointment.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
wornout
post Mar 14 2019, 06:47 PM
Post#3



Posts: 1,285
Joined: 17-November 13
From: Orewa New Zealand


In my appointment table in access I have the entryID so how would I incorporate that into my delete code
I am a bit confused as it deletes it from the access calander and the outlook one at the same time see code below
so when It deletes it from access calendar it needs to look for the access ID and when it deletes it from outlook it needs to look for the accessid then in outlook find the matching appointmentID.
so first it would need to look for the accessid then use the appointmentid??


CODE
Private Sub cmdDelete_Click()
        Me.EnableEvents = True
        DoCmd.SetWarnings False

DoCmd.OpenQuery "deletereocurrence"
DoCmd.OpenQuery "Appendtoreocurrencetbl"

  Me.EnableEvents = True

Dim objApp As Outlook.Application
Dim objAppt As Object
Dim objNS As Object
Dim objFolder As Object

Set objApp = New Outlook.Application
Set objNS = objApp.GetNamespace("MAPI")

Set objFolder = objNS.GetDefaultFolder(olFolderCalendar)
  Me.EnableEvents = True

Dim sfilter As String
sfilter = "[Mileage] = " & Me.txtAppointmentID & ""


Set objAppt = objFolder.Items.Find(sfilter)  ' olAppointmentItem



If objAppt Is Nothing Then
        MsgBox "appointment not found"
    Else
      If Me.Text44 = 0 Then
        objAppt.Delete
        
        Else
        If Me.Text44 > 0 Then
        Call Filtertbl
      End If
      End If
        
    End If



    If Nz(Me.txtPattern) <> "" And Me.chkRecurSingle = True Then                                                                                'if recurring appt and sequence mode then
        If MsgBox("Are you sure you want to delete this appointment and ALL the associated recurring appointments?", vbQuestion + vbYesNo, "Delete Recurring Appointments") = vbNo Then Exit Sub
        CurrentDb.Execute "DELETE FROM tblAppointments WHERE RecurrenceID = " & Me.lstAppts.Column(8)                                          'delete all recurring records in group
    Else
        If MsgBox("Are you sure you want to delete this appointment?", vbQuestion + vbYesNo, "Delete Appointment") = vbNo Then Exit Sub         'request confirmation
        CurrentDb.Execute "DELETE FROM tblAppointments WHERE ApptID = " & Me.txtAppointmentID                                                   'delete session record
    End If

    gDummy = 1
    
    


    'return 1 (refresh screen on return)
    DoCmd.Close acForm, Me.Name                                                                                                                 'and close form
      DoCmd.SetWarnings True
End Sub
Go to the top of the page
 
wornout
post Mar 14 2019, 07:07 PM
Post#4



Posts: 1,285
Joined: 17-November 13
From: Orewa New Zealand


I noticed it has a call macro in it here that is
CODE
Public Sub Filtertbl()
Dim i As Integer
Dim strSQL As String
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Dim strMileage As String

Dim objApp As Outlook.Application
Dim objAppt As Object
Dim objNS As Object
Dim objFolder As Object
Dim Reocurrencetbl As Recordset
Dim ApptID As Field
Set objApp = New Outlook.Application
Set objNS = objApp.GetNamespace("MAPI")

Set objFolder = objNS.GetDefaultFolder(olFolderCalendar)
Dim sfilter1 As String

Set rs = db.OpenRecordset("Reocurrencetbl")
rs.MoveLast ' thats because access won't know the total result row count until you scroll to the end.

Dim TC As Long
TC = Nz(rs.RecordCount, 0)

rs.MoveFirst ' Moving back to the first result set
For i = 0 To TC - 1
Debug.Print rs.Fields("ApptID")
strMileage = rs.Fields("ApptID")
Debug.Print strMileage
sfilter1 = "[Mileage] = " & strMileage & ""
Debug.Print sfilter1
Set objAppt = objFolder.Items.Find(sfilter1)  ' olAppointmentItem


If objAppt Is Nothing Then
        MsgBox "appointment not found"
    Else
        
        objAppt.Delete
        
        
    End If

rs.MoveNext
Next i
End Sub
Go to the top of the page
 
theDBguy
post Mar 14 2019, 08:06 PM
Post#5


Access Wiki and Forums Moderator
Posts: 74,678
Joined: 19-June 07
From: SunnySandyEggo


Hi. Granted I am only looking at your code using my phone’s small screen, but I don’t see in either one of them where you’re retrieving and storing the Outlook assigned EntryID.

Sent from phone...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
wornout
post Mar 14 2019, 09:51 PM
Post#6



Posts: 1,285
Joined: 17-November 13
From: Orewa New Zealand


Boy its confusing
so in the table tblAppointments

the row/entry ID(Auto number) in access is called txtappointmentID and is stored in outlook under the mileage field

the outlook Appointment ID is stored in access as a field ApptEntryID

The filtertbl macro I assume filters the table tblAppointments on the txtappointmentID

with the filtered table

the delete macro I assume looks in outlook mileage for the txtappointmentID and then deletes the appointment
I need to somehow get the delete macro to look first to see if the filtered row has an ApptEntryID if so delete on that if not look for the txtappointmentID and delete on that if nothing then the message box not found

Go to the top of the page
 
theDBguy
post Mar 14 2019, 09:54 PM
Post#7


Access Wiki and Forums Moderator
Posts: 74,678
Joined: 19-June 07
From: SunnySandyEggo


Hi. I suppose you could try to do that or also try what I was suggesting, which was to use the EntryID assigned by Outlook to the appointment. Have you looked into the EntryID property in Outlook?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
wornout
post Mar 15 2019, 04:05 PM
Post#8



Posts: 1,285
Joined: 17-November 13
From: Orewa New Zealand


Hi I dont think I understand you the outlook entry ID is stored in my access table but not in my code

Not all entries in the table have an outlook entry ID some only have a row ID

the row ID is stored in outlook as mileage

what I think I need to do is
get my code to look and see if the row I am deleting has an outlook entry ID if so use that to delete from outlook but if it does not have outlook entry ID then use the rowID to delete from outlook.
But I am not sure how to fit that into my code? or what to put.

I think you are suggesting to use the outlook entry ID to delete but not all in my table has an outlook entry code as some entries are done from access to outlook
Go to the top of the page
 
theDBguy
post Mar 15 2019, 04:17 PM
Post#9


Access Wiki and Forums Moderator
Posts: 74,678
Joined: 19-June 07
From: SunnySandyEggo


Hi. All objects are assigned an EntryID by Outlook, which would easily identify the object for you to update or delete. If you’re not storing this information in Access, my suggestion is you should. Try to modify your code to capture this value from Outlook when you create an appt from Access.

Sent from phone...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
wornout
post Mar 15 2019, 05:52 PM
Post#10



Posts: 1,285
Joined: 17-November 13
From: Orewa New Zealand


Ok I now have 2 textboxs on my delete form EntryID and txtAppointmentID
How do I get delete from out look appointments
I want to say if EntryID = " " then
look up milage in outlook and delete matching entry
else
look up EntryID in outlook Appointment entryID
and delete

And to make the situation more difficult if the entry is recurring a form pops up and asks if you want to deal with the series
each entry has a Recurrence number 0 being there is no occurrence if it does then all instances are given a matching number some will have outlook EntryID and some will just have txtAppointmentID

Have attached the calendar hopefully someone can make head and tail of it
Attached File(s)
Attached File  Trial_Calander.zip ( 1001.14K )Number of downloads: 1
 
Go to the top of the page
 
wornout
post Mar 15 2019, 06:16 PM
Post#11



Posts: 1,285
Joined: 17-November 13
From: Orewa New Zealand


theDBguy
Good Idea but I have no idea how
It would mean when I send the appointment to outlook I would have to get the outlook entryID as well ?
I did not know this could be done
Go to the top of the page
 
theDBguy
post Mar 15 2019, 06:56 PM
Post#12


Access Wiki and Forums Moderator
Posts: 74,678
Joined: 19-June 07
From: SunnySandyEggo


Hi. When you use CreateItem in Outlook, an EntryID is assigned. You should be able to grab it then.

Sent from phone...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
wornout
post Mar 15 2019, 10:34 PM
Post#13



Posts: 1,285
Joined: 17-November 13
From: Orewa New Zealand


This is my code to send appointment to outlook
CODE
Public Function ExportOutlookAppointments() As Long 'Mine

'Copy any appointments stored in tblAppointments into Outlook diary
'Entry  (tblAppointments) holds appointments to export
'Exit   ExportOutlookAppointments = Number of appointments exported to Outlook

'Use this for Late Binding
Dim objApp As Object
Dim NameSpace As Object
Dim fdrCalendar As Object
Dim ItemAppt As Object

'Use this for Early Binding and create a reference to Miscrosoft Outlook Object Library
'Dim objApp As Outlook.Application
'Dim NameSpace As Outlook.NameSpace
'Dim fdrCalendar As Outlook.MAPIFolder
'Dim ItemAppt As Outlook.AppointmentItem

Dim rst As Recordset
'Dim vMisc As Variant

    On Error GoTo ErrorCode

    Set objApp = CreateObject("Outlook.Application")
    Set NameSpace = objApp.GetNamespace("MAPI")
    Set fdrCalendar = NameSpace.GetDefaultFolder(9)                                     'set olFolderCalendar

    Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblAppointments WHERE Senttooutlook = FALSE")  'fetch valid appts
    Do Until rst.EOF
        Set ItemAppt = fdrCalendar.Items.Add
        ItemAppt.Subject = rst!ApptSubject
        ItemAppt.Start = rst!ApptStart
        ItemAppt.End = rst!ApptEnd
        ItemAppt.Location = Nz(rst!ApptLocation)                                        'Location code
        ItemAppt.Mileage = rst!ApptID
        ItemAppt.ReminderSet = False
      
'        ItemAppt.ReminderMinutesBeforeStart = 15                                        'mins before start
    
'        ItemAppt.AllDayEvent = vMisc                                                    'all day event
'        ItemAppt.Body = vMisc                                                           'fetch Body text
'        ItemAppt.BusyStatus = vMisc                                                     'busy status
'        ItemAppt.Category = vMisc                                                       'fetch Category text
'        ItemAppt.OptionalAttendees = vMisc                                              'optional attendees
        
        ItemAppt.Close 0                                                                'olSave and close
        ExportOutlookAppointments = ExportOutlookAppointments + 1                       'inc counter
        rst.Edit
        rst.Update
        rst.MoveNext
    Loop
    rst.Close
    Set rst = Nothing
    Set objApp = Nothing
    Set NameSpace = Nothing
    Set fdrCalendar = Nothing
    Set ItemAppt = Nothing
    Exit Function

ErrorCode:
    Beep
    MsgBox Err.Description

End Function


I am not sure where to put it or what to put
needs to be stored in the tblAppointments in the apptentryID
Go to the top of the page
 
wornout
post Mar 15 2019, 11:49 PM
Post#14



Posts: 1,285
Joined: 17-November 13
From: Orewa New Zealand


OK I have this in my delete code It debug.prints right but debugs on Set objAppt2 = objFolder.Items.Find(Tfilter) ' olAppointmentItem saying it can not parse condition error at "00000000E2122764062."
the EntryID is 00000000E2122764062D9A45B11528610EED5BAC645F3000

CODE
Dim sfilter As String
Dim Tfilter As String

sfilter = "[Mileage] = " & Me.txtAppointmentID & ""
Tfilter = "[EntryID] = " & Me.txtEntryID & ""
Debug.Print sfilter

Debug.Print Tfilter
Set objAppt = objFolder.Items.Find(sfilter)  ' olAppointmentItem
Set objAppt2 = objFolder.Items.Find(Tfilter)  ' olAppointmentItem



If objAppt Is Nothing Then
        MsgBox "appointment not found"
         If Me.Text44 = 0 Then
        objAppt2.Delete
        
    Else
      If Me.Text44 = 0 Then
        objAppt.Delete
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    21st March 2019 - 07:03 AM