Outlook View Control on Access 2007 form

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.
David BarmanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
0
David BarmanAuthor Commented:
That seems to only show HTML code, not Access VBA code.
0
Jeffrey CoachmanMIS LiasonCommented:
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
Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

conagramanCommented:
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
Jeffrey CoachmanMIS LiasonCommented:
Thanks conagraman, I was hoping that an Expert who had done this before would chime in.

;-)

Jeff
0
conagramanCommented:
boag2000:

no problem i'm not really using the Outlook View Control, but this is one way listing the appointments could be done.
: )
0
David BarmanAuthor Commented:
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
conagramanCommented:

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
David BarmanAuthor Commented:
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
David BarmanAuthor Commented:
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
conagramanCommented:
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
conagramanCommented:
after you click on the button click on the form control you have on your form and draw a box.
0
conagramanCommented:
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
conagramanCommented:
here is a pic.
ee2.bmp
0
conagramanCommented:
i played around with this and created a sample database with some different view options.
OutlookVC.accdb
0
conagramanCommented:
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
David BarmanAuthor Commented:
Looks impressive.  Any ideas how to point it to an Exchange public calendar folder?
0
conagramanCommented:
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
David BarmanAuthor Commented:
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
conagramanCommented:
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
David BarmanAuthor Commented:
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
conagramanCommented:
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
David BarmanAuthor Commented:
I had it set to 1 and changed it to 50, same error.
0
conagramanCommented:
did you try my sample database? did it error?
0
David BarmanAuthor Commented:
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
conagramanCommented:
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
David BarmanAuthor Commented:
Yes, the inbox works just fine.
0
conagramanCommented:
do the buttons work that way?
0
David BarmanAuthor Commented:
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
conagramanCommented:
upload your database and ill take a look at what is going on
0
David BarmanAuthor Commented:
Appointment database with error is attached appointments.accdb
0
conagramanCommented:
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
David BarmanAuthor Commented:
This is strange.  Both forms do the same thing for me.
0
conagramanCommented:
this might sound weird, but when you open outlook do you have a calendar?
0
David BarmanAuthor Commented:
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
conagramanCommented:
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
David BarmanAuthor Commented:
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
conagramanCommented:
let me know what happens when you try setting the folder in the properties menu.
0
David BarmanAuthor Commented:
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
conagramanCommented:
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
conagramanCommented:
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
conagramanCommented:
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
David BarmanAuthor Commented:
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
conagramanCommented:
i saved it in 07.
0
conagramanCommented:
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
conagramanCommented:
you shouldn't have to add references or anything just use the code in my sample database.
0
conagramanCommented:
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
David BarmanAuthor Commented:
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
conagramanCommented:
i'm not sure. i will have to try a few things and see. i will let you know.
0
conagramanCommented:
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
David BarmanAuthor Commented:
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
conagramanCommented:
you have to put the timer on the parent form. the sub form shouldn't have any code
0
conagramanCommented:
also make sure you have both the oframe and ovc variables declared on the parent form. and make sure the references are set.
0
David BarmanAuthor Commented:
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
conagramanCommented:
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
conagramanCommented:
look at my last post and make sure you have a reference to the microsoft outlook view control
0
David BarmanAuthor Commented:
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
conagramanCommented:
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
conagramanCommented:
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
David BarmanAuthor Commented:
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
conagramanCommented:
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
David BarmanAuthor Commented:
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
conagramanCommented:
what i would do is make two new forms in your existing database
0
David BarmanAuthor Commented:
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
conagramanCommented:
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
David BarmanAuthor Commented:
I understand.  I don't either.  This is a real problem for me.
0
conagramanCommented:
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
conagramanCommented:
got it
in the code change
Set VC = MyFrame.ActiveControl
to
Set VC = MyFrame!ViewCtl1

0
conagramanCommented:
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
conagramanCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David BarmanAuthor Commented:
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
David BarmanAuthor Commented:
The solution provided was correct.  Please award 500 points to conagraman.
0
David BarmanAuthor Commented:
This solved the problem.  Thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Exchange

From novice to tech pro — start learning today.