?
Solved

Finding and replacing existing item?

Posted on 2001-06-22
6
Medium Priority
?
170 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
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 

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 800 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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
Are you looking for the options available for exporting EDB files to PST? You may be confused as they are different in different Exchange versions. Here, I will discuss some options available.
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…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses

839 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