Finding and replacing existing item?

I have wrote some code that imports data from a database directly into Outlook's Calendar.  However, I seem to only be able to add new items.  I would like to know of a good method of checking if the item already exists and if so replacing it?????????  Here is the point where I add the data:

 Do Until rst.EOF
    Set itm = itms.Add("IPM.Appointment.olFolderCalendar")
    If IsNull(rst!AMPM) = False Then itm.Subject = rst!AMPM
      If IsNull(rst!Date) = False Then itm.StartDate = rst!Date
      If IsNull(rst!L18) = False Then itm.Description = rst!L18

itm.Close (0)
      rst.MoveNext
     
   Loop
   rst.Close
Can anyone exlpain the best way to go about this??  Thanks
merciaAsked:
Who is Participating?
 
rjcpjcConnect With a Mentor Commented:
Try setting a variable something like this:

newDate = FormatDateTime(itm.Start,2)

2 represents Short Date.  

That will format the start date as a short date, but I don't know if it will automatically truncate the date or just display it as short date.  The other possibility would be to get the month, day and year of the itm.Start value and compare that to the month, day and year of your rst!date.  

You can do that with DatePart.

tmpMonth = DatePart(itm.Start, "m")
tmpDay = DatePart(itm.Start, "d")
tmpYear = DatePart(itm.Start, "yyyy")

Do the same thing with your rst!Date.

Just a couple of options.
0
 
rjcpjcCommented:
You'd have to loop through the items in the calendar and check the subject, start and end times.  That would probably be the best way to tell.

So the code would be something like this:

Set fld = objNS.GetDefaultFolder(olFolderCalendar)
Set itms = fld.Items

For each itm in itms
  If itm.Start = ??? and itm.End = ??? and itm.Subject = ??? then
     'Item already exists
     itm.Start = newStartTime
     itm.End = newEndTime
     itm.Save
   Else
     Set itm = itms.Add("IPM.Appointment.olFolderCalendar")
      If IsNull(rst!AMPM) = False Then itm.Subject = rst!AMPM
      If IsNull(rst!Date) = False Then itm.StartDate = rst!Date
     If IsNull(rst!L18) = False Then itm.Description = rst!L18

Next

-------
That is just an example of code.  You'd replace the ???'s with the variables from your recordset that you wanted to test for.  

Hope that makes sense.  Post back if you have more questions.

--PatriciaC
0
 
merciaAuthor Commented:
Thanks I do not think I explained very well I would like the items to be replaced with the items from the database.  My database does not contain time info and therefore Outlook defaluts to 00:00 ( I would like to know how to get round that).  So if item exists the data in each individual field Subject, Start and Location should be replace by rst!L18, rst!Date, rst!L18 respectfully.  Sorry for confusion.

Wayne
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
rjcpjcCommented:
Well, you would have to do the same sort of search, just by subject.  Item.Subject, or Item.Location.  

Not sure what you mean by get around the time info from your database.  

If the item exists then you would use

itm.Subject = rst!L18
itm.Start = rst!Date
itm.Location = rst!L18

based on your code above.

Hope that helps.  If not post back.
0
 
merciaAuthor Commented:
Hi

Here is the code as it stands:

Set itms = fld.Items
    Do Until rst.EOF

    For Each itm In itms

    If itm.Subject = (rst!l86) And itm.Start = (rst!Date) Then
            itm.Start = rst!Date
            itm.Subject = rst!l86
            itm.Save
    Else
            Set itm = itms.add("IPM.Appointment.Access Calendar")

            If IsNull(rst!Date) And IsNull(rst!l86) = False Then itm.Start = rst!Date
            If IsNull(rst!AMPM) And IsNull(rst!l86) = False Then itm.Location = rst!AMPM
            If IsNull(rst!l86) = False Then itm.Subject = rst!l86
            If IsNull(rst!l86) = False Then itm.Start = FormatDateTime(rst!Date, vbShortDate)
            If IsNull(rst!l86) = False Then itm.BusyStatus = olBusy
            If IsNull(rst!l86) = False Then itm.Duration = 240
               
            itm.Close (0)
            rst.MoveNext
       
        End If '
    Next


   Loop
   rst.Close
   txt.Value = vbCrLf & "All calendar entries imported!"

This runs however I get error no 3021: No current record and it still adds a new appointment.  I think I know why this is.  The no record must be to do with my Do... Loop, which I think I can sort.  My main problem is that my  database stores date info 27/06/2001 and on Outlook the date filed Start appears to store date in the format 27/06/2001 00:00 which I think is causing the appointments to be added again.  If it was possible for me to add a standard time to rst!Date this would help I think.  Here is my idea.  When I add an item (in english

if rst!AMPM = AM Then itm.Start = rst!date & #09:00#
if rst!AMPM = PM Then itm.Start = rst!date & #13:00#

Now I know this code will not work but I do not know how to set the time as my databse does not store the time.  I think if I get around this in this way it should solve my problems.  Do you think I am on the right lines and can you help with the code or can you suggest a better way?

Thanks

Wayne

0
 
merciaAuthor Commented:
Thanks the DatePart has allowed me greater control of this, I have a slight problem in that the For Next is starting at the beinning and not where the recordset starts.  I will post another question.  You really have helped me on my way

Wayne
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.