?
Solved

Finding and replacing existing item?

Posted on 2001-06-22
6
Medium Priority
?
166 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

How to resolve IMCEAEX NDRs in Exchange or Exchange Online related to invalid X500 addresses.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…
Suggested Courses

770 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