Solved

Finding and replacing existing item?

Posted on 2001-06-22
6
163 Views
Last Modified: 2010-04-08
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
0
Comment
Question by:mercia
  • 3
  • 3
6 Comments
 
LVL 9

Expert Comment

by:rjcpjc
ID: 6218840
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
 

Author Comment

by:mercia
ID: 6227267
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
 
LVL 9

Expert Comment

by:rjcpjc
ID: 6227912
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:mercia
ID: 6230383
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
 
LVL 9

Accepted Solution

by:
rjcpjc earned 200 total points
ID: 6232142
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
 

Author Comment

by:mercia
ID: 6234571
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many people use more than one email account and so it becomes difficult for them to manage them when they use separate accounts,  so, in this article, I have shared an easy way to add Other Mail Accounts in your Google Inbox. It helps to combine all…
In this step by step procedure, you will come to know the details of creating an Outlook meeting in 2007, 2010, 2013 & 2016.
CodeTwo Sync for iCloud (http://www.codetwo.com/sync-for-icloud?sts=6554) automatically synchronizes your Outlook 2016, 2013, 2010 or 2007 folders with iCloud folders available via iCloud Control Panel. This lets you automatically sync them with…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question