Solved

Outlook View Control on Access 2007 form

Posted on 2010-11-10
74
1,932 Views
Last Modified: 2013-04-10
I am under the impression that the Outlook View Control can be used to add an "Outlook" view to an Access form.  I am looking to add an Outlook calendar so users of the Access database can see the current appointments and then create the new appointments in the database.
However, I have not been able to find any sample code to show how to setup the Outlook control so it shows the calendar.  Any help would be great.
0
Comment
Question by:David Barman
  • 43
  • 28
  • 3
74 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
0
 

Author Comment

by:David Barman
Comment Utility
That seems to only show HTML code, not Access VBA code.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
That seemed to be the way this control is used, based in the research I did.

I did see other links, but as you found, there was nothing that specifically outlined how to insert this "Control" into an Access form and have it display a calendar.


It is also interesting to note that a lot of the reference links to this from the MS site seem to be dead ends.
Also interesting is that I did not see anything stating that this can be done in Access 2003 or newer.

JeffCoachman
0
 
LVL 10

Expert Comment

by:conagraman
Comment Utility
i have done a little with outlook in the past but never tackled the calendar. i played around with some code i found and created a sample database for you. it uses a calendar and a list box. when you click on a date it will list all of the appointments on that day and the recurring appointmentsdays with an end date greater than the date selected.  also it will let you create a new appointment.  here are the links i found that i used and can help you.

http://www.dicks-clicks.com/excel/olCalendar.htm#Showing_the_Calendar '<<by far the most help working examples except was made for excel. so 99% usable.
http://technet.microsoft.com/en-us/library/ee692908.aspx
http://msdn.microsoft.com/en-us/library/aa269301(v=office.10).aspx

outlookCalendar.accdb
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Thanks conagraman, I was hoping that an Expert who had done this before would chime in.

;-)

Jeff
0
 
LVL 10

Expert Comment

by:conagraman
Comment Utility
boag2000:

no problem i'm not really using the Outlook View Control, but this is one way listing the appointments could be done.
: )
0
 

Author Comment

by:David Barman
Comment Utility
conagraman, maybe I am not doing something correctly.  I looked at your sample database.  When I click the show outlook calendar, it just opens the Outlook application on the current date.  I appreciate you help, but I need to show the calendar on the Access form.  Secondly, regardless of what date I chose on the database form that you provided, it always opens the current day calender.

Any advice to adding the outlook calendar directly to the access form?
0
 
LVL 10

Expert Comment

by:conagraman
Comment Utility

when you click on a date on the calendar it will list all of the appointments for the date selected in the list box on the form.

the button in the upper left corner was just there to give you another option.

0
 
LVL 10

Expert Comment

by:conagraman
Comment Utility
0
 

Author Comment

by:David Barman
Comment Utility
that looks interesting.  But I was hoping that someone could provide some sample code, to get me started.  That is where I have having an issue.  Getting the control configured and linked to the outlook calendar folder.
0
 

Author Comment

by:David Barman
Comment Utility
According the the site:  http://outlookcode.com/article.aspx?id=70,
The OVC cannot be used as a control on an Access 2007 user form.

Unless someone can offer some contrary comments, I need to find an alternative approach.
My goal is to be able to schedule appointments with the access database and then add those appointments to a public folder calendar in outlook 2007.  However, the reason, I originally wanted to use the OVC to show the calendar was so the user could see what time slots were available.
Any ideas or suggestions?
0
 
LVL 10

Expert Comment

by:conagraman
Comment Utility
ok what you do is just like the link i showed you.
insert a "Microsoft Forms 2.0 Frame" active x control>> then right click on the control in the menu that pops up click "frame Object">> Edit

at this point you will see a toolbox popup. right click inside the tool box >> Click on "Additional Controls"

in the additional controls menu click on the box next to "Microsoft Office Outlook View Control. >> click ok

then in the tool box a new button will appear that looks like a list box. if you mouse over it it will have a tool tip that says "ViewCtl." click on that button.


ee1.bmp
0
 
LVL 10

Expert Comment

by:conagraman
Comment Utility
after you click on the button click on the form control you have on your form and draw a box.
0
 
LVL 10

Expert Comment

by:conagraman
Comment Utility
you will at this point you should see outlook on your form. it will probably show your inbox. to change this right click on the outlook view control in the menu select Properties.

in the properties menu click on the "folder" line>> then in text box next to the apply button type in Calendar >> then click the Apply button.

you can change the view of the calendar if you want you can do this by
clicking in the View line of the properties menu and in the textbox type in Day/Week/Month >> then click the apply button
0
 
LVL 10

Expert Comment

by:conagraman
Comment Utility
here is a pic.
ee2.bmp
0
 
LVL 10

Expert Comment

by:conagraman
Comment Utility
i played around with this and created a sample database with some different view options.
OutlookVC.accdb
0
 
LVL 10

Expert Comment

by:conagraman
Comment Utility
this is easily the most unstable control i have ever delt with. it works fine but when you are playing with it in design view make sure you make the save button your best friend because your database will crash frequently and unexpectedly. Having said that- the result is verrry cool and worth it.
0
 

Author Comment

by:David Barman
Comment Utility
Looks impressive.  Any ideas how to point it to an Exchange public calendar folder?
0
 
LVL 10

Expert Comment

by:conagraman
Comment Utility
not really. i dont deal with sharepoint too much. you can synchronize your sharepoint calendar with your outlook calendar though. might do the trick.
0
 

Author Comment

by:David Barman
Comment Utility
I am not talking about sharepoint.  In Exchange server, there is a folder called "Public Folders" and a subfolder of that is "All Public Folders" in which users (or admins) can create folders that all users on the Exchange server can view/edit/etc. This is typically used a a company wide shared resource.

I wish to display the shared calendar on this form.  That is why I inquiring to change the folder path from the default mailbox to the public folder calendar.

What do you think?
0
 
LVL 10

Expert Comment

by:conagraman
Comment Utility
i suppose you could use any folder. you just need to know the name that outlook gives it.  instead of using .folder = "Calendar" you would use something like "publicCalendar"  
0
 

Author Comment

by:David Barman
Comment Utility
I am giving this a try.  But having some difficulties.  After add the frame and the OVC control into the frame.  I replicated your form timer event and getting an error.  The error is:
Run time erorr 91:   Object variable or with block variable not set.  

Here is the code:
Option Compare Database
Dim OutlookFrame As Frame


Private Sub Form_Timer()
Set OutlookFrame = Me.Frame9.Object

OutlookFrame.ActiveControl.folder = "Calendar"                   'THE ERROR OCCURS ON THIS LINE
OutlookFrame.ActiveControl.View = "Day/Week/Month"

Me.TimerInterval = 0
End Sub


What do you think?
0
 
LVL 10

Expert Comment

by:conagraman
Comment Utility
i should say that i put the timer in so that the view control would show the calendar not  the default inbox folder.  (kinda hacky i know) i tried other ways but ended up with the timer being the most efficient.  to get rid of the error set the "timerinterval" in the properties menu of the form to something small like 50


0
 

Author Comment

by:David Barman
Comment Utility
I had it set to 1 and changed it to 50, same error.
0
 
LVL 10

Expert Comment

by:conagraman
Comment Utility
did you try my sample database? did it error?
0
 

Author Comment

by:David Barman
Comment Utility
I had other problems with the sample database.  Everytime I try to open it it says:

could not load an object because it is not available on this machine

and the top buttons are on the form. It looks like the frame is there, but the OVC controll does not seem to be there.  If I try to edit the frame, then access crashes.
0
 
LVL 10

Expert Comment

by:conagraman
Comment Utility
ya the OVC is like i said verrrry unstable.

hmmm well -try your code without the timer and see if you can get the inbox to load
0
 

Author Comment

by:David Barman
Comment Utility
Yes, the inbox works just fine.
0
 
LVL 10

Expert Comment

by:conagraman
Comment Utility
do the buttons work that way?
0
 

Author Comment

by:David Barman
Comment Utility
I am not sure what you mean.  When I said the inbox works fine, I meant in my database.  I didn't change anything in yours.  I cannot get yours to work.  If I comment out the code in the timer event,the The form opens, but I get the same errors for every button I click.  It appears that the OVC control is not there.
In any event, without the timer event code in my database, it shows the inbox in the OVC control.
0
 
LVL 10

Expert Comment

by:conagraman
Comment Utility
upload your database and ill take a look at what is going on
0
 

Author Comment

by:David Barman
Comment Utility
Appointment database with error is attached appointments.accdb
0
 
LVL 10

Expert Comment

by:conagraman
Comment Utility
i can’t tell what the difference is. it doesn’t work for me either. i uploaded your database and i added a second form. the second form works for me. i don’t think you’re doing anything wrong. seems like access just wanted to be weird for no apparent reason.
try it again. this time (and you might have last time) before you put anything else on the form put in the frame and then the ovc. once that is done add the timer event. once that works then add other controls to the form.   also if it crashes on you when you are putting the ovc on the form start over with a new form just to be on the safe side.

appointments.accdb
0
 

Author Comment

by:David Barman
Comment Utility
This is strange.  Both forms do the same thing for me.
0
 
LVL 10

Expert Comment

by:conagraman
Comment Utility
this might sound weird, but when you open outlook do you have a calendar?
0
 

Author Comment

by:David Barman
Comment Utility
Yes.  I don't even know how you couldn't have one, unless you were to delete it (actually never tried to delete one to see if it would allow it)
0
 
LVL 10

Expert Comment

by:conagraman
Comment Utility
if you have outlook express you might not.
ok its not outlook because you have a calendar. we know its not the activex control because you can see the inbox.

we should try this manually.
right click on the outlook view control in the menu select Properties.

in the properties menu click on the "folder" line>> then in text box next to the apply button type in Calendar >> then click the Apply button.

after you click the apply button the control should show your calendar.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:David Barman
Comment Utility
Ok.  Here's the thing.  When I open the Appointment2 form that you created, it generates an error and it does not appear that the OVC control is on the form after I put the form in edit mode.
I have attached the error. Access-Error.pdf
0
 
LVL 10

Expert Comment

by:conagraman
Comment Utility
let me know what happens when you try setting the folder in the properties menu.
0
 

Author Comment

by:David Barman
Comment Utility
I cannot get to the properties window, because when the error comes up (see my last post), then if I edit the frame, there isn't a control in the frame.  The frame is completely empty.  Therefore, properties is not possible.
0
 
LVL 10

Expert Comment

by:conagraman
Comment Utility
right click on the outlook view control in the menu select Properties.

in the properties menu click on the "folder" line>> then in text box next to the apply button type in Calendar >> then click the Apply button.
ee1.bmp
0
 
LVL 10

Expert Comment

by:conagraman
Comment Utility
then create a new form and drop in a view control. then save it.  then reopen it and try setting the folder in the properties menu
0
 
LVL 10

Expert Comment

by:conagraman
Comment Utility
when in design view if you dont see the ovc but you know its there click on the frame then click in the area where the ovc should be and it should appear
0
 

Author Comment

by:David Barman
Comment Utility
Ok.  I put a new Outlook View Control onto the form and set the folder property to  Calendar.  It shows the calendar.
What can we conclude about the control vanishing?  What version of Access did you save the database with?  Could that be the difference?????
0
 
LVL 10

Expert Comment

by:conagraman
Comment Utility
i saved it in 07.
0
 
LVL 10

Expert Comment

by:conagraman
Comment Utility
this is good news! ok if you can set the calendar on the properties menu you can do it by code.  try adding a button and have it set the .folder = "Calendar"

do it on the same form you just created.
0
 
LVL 10

Expert Comment

by:conagraman
Comment Utility
you shouldn't have to add references or anything just use the code in my sample database.
0
 
LVL 10

Expert Comment

by:conagraman
Comment Utility
what we can conclude about the control is just that it is very unstable in design view. to put it kindly.

it does the vanishing thing in design view for me too. you just have to click on the frame and then in the area where the ovc is and it appears. the problem is sometimes when you do that access will crash on you.
0
 

Author Comment

by:David Barman
Comment Utility
Ok.  Here's the next step.  I want the control to show the calendar, but I don't want the user to be able to edit the appointments directly with the control.  I want the database to do all the add/deletes/edits.

Any ideas?
0
 
LVL 10

Expert Comment

by:conagraman
Comment Utility
i'm not sure. i will have to try a few things and see. i will let you know.
0
 
LVL 10

Expert Comment

by:conagraman
Comment Utility
turns out yes it is possible.
the trick is to create two forms  a parent form and a sub form.
Place the ovc in the subform. to disable to ovc you just disable the sub form.

here is a sample to look at that might work for you . i converted it to mdb so you never know it might.
OVC.mdb
0
 

Author Comment

by:David Barman
Comment Utility
Ok.  Problem.  When I try to implement this in my database, the subform runs great, if I open it by itself.  However, when I run the parent form, I keep getting a runtime error 91:  object variable or with block not set

I cannot seem to figureout how to fix this.  The error occurs on the 'OVC.Folder = "Calendar"' line.

Here is the code for the timer event:
Private Sub Form_Timer()
Set OFrame = Me.OutlookCalendar.Form!Frame0.Object
Set OVC = OFrame.ActiveControl
OVC.Folder = "Calendar"
OVC.View = "Day/Week/Month"
Me.TimerInterval = 0
End Sub

Any ideas?
0
 
LVL 10

Expert Comment

by:conagraman
Comment Utility
you have to put the timer on the parent form. the sub form shouldn't have any code
0
 
LVL 10

Expert Comment

by:conagraman
Comment Utility
also make sure you have both the oframe and ovc variables declared on the parent form. and make sure the references are set.
0
 

Author Comment

by:David Barman
Comment Utility
I have tried it in the parent form and it does not change the error.
I also have dim statements for both the frame and OVC
0
 
LVL 10

Expert Comment

by:conagraman
Comment Utility
also:

do not set the property of the form to enabled = false in the property menu
do it in the code in the timer event

Here is the code for the timer event:
Private Sub Form_Timer()
Set OFrame = Me.OutlookCalendar.Form!Frame0.Object
Set OVC = OFrame.ActiveControl
OVC.Folder = "Calendar"
OVC.View = "Day/Week/Month"
me.outlookcalendar.enabled = false '<< disable the form

Me.TimerInterval = 0
End Sub
0
 
LVL 10

Expert Comment

by:conagraman
Comment Utility
look at my last post and make sure you have a reference to the microsoft outlook view control
0
 

Author Comment

by:David Barman
Comment Utility
If by reference, you mean the DIM OVC as ViewCtl, then yes.
Also, I have not yet tried to implement disabling the subform.  I am just trying to get the subform to execute without errors.
0
 
LVL 10

Expert Comment

by:conagraman
Comment Utility
look at my sample database for reference.

all you do is have two forms a parent and a sub

in the subform you put the from and the ovc in the form.
add the references :
microsoft forms 2.0 object library and
microsoft office outlook view control

in the parent form add a timer with the following code and set the interval to something small like 1

Option Compare Database
Dim MyFrame As Frame
Dim VC As ViewCtl


Set MyFrame = Me.sbfOVC.Form!OVCFrame.Object
Set VC = MyFrame.ActiveControl

VC.Folder = "Calendar" '<<set the folder to calendar
VC.View = "Day/Week/Month"  '<<set the calendar view

Me.btnEnable.SetFocus '<<move focus to something else
Me.sbfOVC.Enabled = False '<<disable view control
Me.TimerInterval = 0 '<<stop timer event from repeating


thats it. this is how i was able to get it to work.
0
 
LVL 10

Expert Comment

by:conagraman
Comment Utility
this part of the code is to go in the event


Set MyFrame = Me.sbfOVC.Form!OVCFrame.Object
Set VC = MyFrame.ActiveControl

VC.Folder = "Calendar" '<<set the folder to calendar
VC.View = "Day/Week/Month"  '<<set the calendar view

Me.btnEnable.SetFocus '<<move focus to something else
Me.sbfOVC.Enabled = False '<<disable view control
Me.TimerInterval = 0 '<<stop timer event from repeating

0
 

Author Comment

by:David Barman
Comment Utility
It works fine if I create a new database and start with two blank forms and create if from scratch.  But when I try to add the subform to the page control of the form in my exisiting database, I get the error.  I have even tried re-creating the subform.  If I open the subform (and as a test, add the code to it's timer event), the subform works fine as a stand alone.
From what I have read on the internet, the error suggests that the variable has not been defined.
How would add it as a subform change anything?
0
 
LVL 10

Expert Comment

by:conagraman
Comment Utility
well first im glad to hear you were able to make it work. as far as making it work in your existing database i can just tell you to make sure you have checked everything.

review the steps of my last to post and compare your working database to your non working one. see if there are any differences. check the names of everything in your code. if your calling your subform "sbfovc" in your code but its real name is "sbfOutlook" then it will error.  
0
 

Author Comment

by:David Barman
Comment Utility
I believe this is the same problem I had before, when I uploaded the database to you and you did not see any differences.  Do you recall?
This is the same problem.  I am 99% confident that the code is correct.
0
 
LVL 10

Expert Comment

by:conagraman
Comment Utility
what i would do is make two new forms in your existing database
0
 

Author Comment

by:David Barman
Comment Utility
That may work, however, I need the subform added to a page control on an exisiting form.  The form is question is massive.  It has many page tabs with multiple subforms on each tab.  Recreating that form would be a LOT of work.  There has to be a better way to solve this???
0
 
LVL 10

Expert Comment

by:conagraman
Comment Utility
well if you can create a subform with no problems you should be able to add it as a subform on your existing form no problem. just make sure the references are set and the code is the same as above and i dont see why it wouldnt work.
0
 

Author Comment

by:David Barman
Comment Utility
I understand.  I don't either.  This is a real problem for me.
0
 
LVL 10

Expert Comment

by:conagraman
Comment Utility
The problem is the ovc has to have the focus before it can be accessed. i am having trouble finding a way to set the focus to the ovc that works consistently.
0
 
LVL 10

Expert Comment

by:conagraman
Comment Utility
got it
in the code change
Set VC = MyFrame.ActiveControl
to
Set VC = MyFrame!ViewCtl1

0
 
LVL 10

Expert Comment

by:conagraman
Comment Utility
the reason we have been running into that error is because the control hasn’t had the focus. it hasn’t had the focus because we have been referencing the activecontrol on the microsoft form. i did that because there really isn’t any documentation on the form control (that i know of) but now that we know how to reference the viewctl itself that problem should go away.
0
 
LVL 10

Accepted Solution

by:
conagraman earned 500 total points
Comment Utility
in fact because we can now reference the control directly we dont need to use a timer event we can use another event such as the form's on load or open event.


Private Sub Form_Load()

Set MyFrame = Me.sbfovc.Form!Frame0.Object
Set VC = MyFrame!ViewCtl1

With VC
.Folder = "Calendar" '<<set the folder to calendar
.View = "Day/Week/Month"  '<<set the calendar view
End With
End Sub
0
 

Author Comment

by:David Barman
Comment Utility
Ok.  When the calendar is displayed, it is also showing the task list at the bottom.  Need the realesate.  How can we no show the tasks?
0
 

Author Comment

by:David Barman
Comment Utility
The solution provided was correct.  Please award 500 points to conagraman.
0
 

Author Closing Comment

by:David Barman
Comment Utility
This solved the problem.  Thank you.
0

Featured Post

Promote certifications in your email signature

Has your company recently won an award or achieved a certification? They'll no doubt want to show it off. Email signature images used to promote certifications & awards can instantly establish credibility with a recipient and provide you with numerous benefits.

Join & Write a Comment

Suggested Solutions

Utilizing an array to gracefully append to a list of EmailAddresses
Not sure what the best email signature size is? Are you worried about email signature image size? Follow this best practice guide.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now