[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5917
  • Last Modified:

MS Access 2010 need MS Access 2003 Calendar Control

I have a database written using MS Access 2000 that will not open calendar items under MS Access 2010.  I have tried registering MSCAL.OCX using regsvr but that doesn't seem to help.  The debugger shows the problem with the "Date" function below:


Private Sub Form_Load()
       
    '  Enter parameters in BeginningDate and EndingDate text boxes when
    '  form is loaded.
    Me!SelectDate = Date  'Now()
    Me!BeginningDate = DateSerial(Format(Date, "yy"), Format(Date, "m") - 6, 1)
    Me!EndingDate = Date

I previously added references that cleared up the error as it occurred elsewhere in the program, but for some reason the calendar control stalls on that same statement.
0
Qualitycomputer
Asked:
Qualitycomputer
  • 13
  • 11
  • 5
2 Solutions
 
danishaniCommented:
Try to use this CalendarControl instead, learn how to replace the deprecated Calendar control in Microsoft Access 2010 applications.
http://msdn.microsoft.com/en-us/library/gg251104.aspx

HTH,
Daniel
0
 
danishaniCommented:
Another alternative, look at Tony's site as well;
http://www.granite.ab.ca/access/calendars.htm

Edit: on previous post, use the DatePicker instead of CalendarControl, which is not being provided in Access 2010.
0
 
Nick67Commented:
Here's the regsitry keys, again.
Merge these in after registering the OCX and you should be good.

Windows Registry Editor Version 5.00

[HKEY_CLASSES_ROOT\TypeLib\{8E27C92E-1264-101C-8A2F-040224009C02}]

[HKEY_CLASSES_ROOT\TypeLib\{8E27C92E-1264-101C-8A2F-040224009C02}\7.0]
@="Microsoft Calendar Control 11.0"

[HKEY_CLASSES_ROOT\TypeLib\{8E27C92E-1264-101C-8A2F-040224009C02}\7.0\0]

[HKEY_CLASSES_ROOT\TypeLib\{8E27C92E-1264-101C-8A2F-040224009C02}\7.0\0\win32]
@="C:\\Program Files (x86)\\Microsoft Office\\Office14\\MSCAL.OCX"

[HKEY_CLASSES_ROOT\TypeLib\{8E27C92E-1264-101C-8A2F-040224009C02}\7.0\FLAGS]
@="2"

[HKEY_CLASSES_ROOT\TypeLib\{8E27C92E-1264-101C-8A2F-040224009C02}\7.0\HELPDIR]
@="C:\\Program Files (x86)\\Microsoft Office\\Office14"

Open in new window

Windows Registry Editor Version 5.00

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{8E27C92B-1264-101C-8A2F-040224009C02}]
@="Calendar Control 11.0"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{8E27C92B-1264-101C-8A2F-040224009C02}\Control]

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{8E27C92B-1264-101C-8A2F-040224009C02}\Implemented Categories]

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{8E27C92B-1264-101C-8A2F-040224009C02}\Implemented Categories\{40FC6ED4-2438-11CF-A3DB-080036F12502}]

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{8E27C92B-1264-101C-8A2F-040224009C02}\Implemented Categories\{40FC6ED5-2438-11CF-A3DB-080036F12502}]

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{8E27C92B-1264-101C-8A2F-040224009C02}\Implemented Categories\{7DD95801-9882-11CF-9FA9-00AA006C42C4}]

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{8E27C92B-1264-101C-8A2F-040224009C02}\Implemented Categories\{7DD95802-9882-11CF-9FA9-00AA006C42C4}]

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{8E27C92B-1264-101C-8A2F-040224009C02}\InprocServer32]
@="C:\\Program Files (x86)\\Microsoft Office\\Office14\\MSCAL.OCX"
"ThreadingModel"="Apartment"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{8E27C92B-1264-101C-8A2F-040224009C02}\Insertable]

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{8E27C92B-1264-101C-8A2F-040224009C02}\MiscStatus]
@="0"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{8E27C92B-1264-101C-8A2F-040224009C02}\MiscStatus\1]
@="131473"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{8E27C92B-1264-101C-8A2F-040224009C02}\ProgID]
@="MSCAL.Calendar.7"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{8E27C92B-1264-101C-8A2F-040224009C02}\Programmable]

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{8E27C92B-1264-101C-8A2F-040224009C02}\ToolboxBitmap32]
@="C:\\Program Files (x86)\\Microsoft Office\\Office14\\MSCAL.OCX, 1"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{8E27C92B-1264-101C-8A2F-040224009C02}\TypeLib]
@="{8E27C92E-1264-101C-8A2F-040224009C02}"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{8E27C92B-1264-101C-8A2F-040224009C02}\Version]
@="7.0"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{8E27C92B-1264-101C-8A2F-040224009C02}\VersionIndependentProgID]
@="MSCAL.Calendar"

Open in new window

0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
QualitycomputerAuthor Commented:
Nick,

I was able to merge those registry entries but I don't see the MSCAL.OCX as an option under the "Available References" dialogue in Access.  I tried browsing to the file that I previously registered using

C:\windows\system32\RegSvr32 /s C:\Program Files (x86)\microsoft office\OFFICE14\mscal.ocx

but Access does not accept that control as something that it will register.  Do you know how it should appear on the list of "Available References"  And does RegSvr32 indicate a successful registration or simply revert to the shell prompt (as it did for me)?

I think that I am getting close now and I'd hate to have to recode my database using substitute controls as suggested by Microsoft in the helpful articles cited by danishani.
0
 
Nick67Commented:
It may not need to be a reference, it may just work if it registered successfully
the /s in the command line was for silent, as I took it out of a VBScript, so the reversion may or may not indicate success
Run it without the /s and see it it succeeds
You HAVE to run cmd as administrator, even if you are logged on as admin
UAC = PITA
Open your db and compile?
Does it complete?
Then open the form that has the calendar control on it in design view.
Is it there?
0
 
danishaniCommented:
The date and time picker control is part of the Windows Common controls.  The file is MSCOMCT2.OCX, located in C:\Windows\System32 on 32-bit or C:\Windows\Syswow64 on 64-bit.  If you don't have this file, you can download it from the following location:

http://activex.microsoft.com/controls/vb6/MSCOMCT2.cab

Once you download the file, you'll need to extract the files and then register the OCX file.  You will likely need to open the command prompt as administrator in order to get the file to register successfully.  Here are the steps in case you need them.

* Go to Start\All Programs\Accessories.
* Right-click Command Prompt and then click on Run As Administrator.
* Change directory to the folder where you extracted the files.  For example, type the following and then press Enter:  cd c:\windows\system32
* Type the following and then press Enter:  regsvr32 mscomct2.ocx
* You should get a message saying this succeeded.

I found this in this thread;
http://social.technet.microsoft.com/Forums/nl-NL/officeitpro/thread/defdd35a-f285-4b84-bf4c-d62011823b37
0
 
Nick67Commented:
@danishani

The date and time picker work, but if you are in a mixed environment, with deployed front ends and users accustomed to the Calendar control, it's nice to get it going.
And it can be done.
I have 3 Access 2010, 1 Access 2007 and 15 Access 2003 machines that all use the same code base, with the front end deployed locally to each machine.

It's just a bit of a PITA to get a new machine going :)
0
 
QualitycomputerAuthor Commented:
I downloaded MSCOMCT2.cab as per the instructions above, copied to Syswow64, and registered from an administrative command prompt (with confirmation of success).  I then went into "Available References" and browsed for that file and successfully added it.  I closed Access and reopened my database but I still get "runtime error 2683: there is no object in this control".

I will try the other suggestion in a few hours once I can get back to this.

Thanks again!!!
0
 
Nick67Commented:
MSCOMCT2.cab will not fix the Calendar control, it is a replacement for it.
Depending on your needs and environment, you may want to go that route!
0
 
QualitycomputerAuthor Commented:
Looking at the design view of one of the forms that uses the calendar control, I realize that I am clueless as to how I would replace MSCAL with MSCOMCT.  Is this something that can be easily explained or should I, perhaps, keep trying to get the old calendar control to work?  I'm definitely stuck at this point.  
0
 
danishaniCommented:
I havent actualy use it myself, while the Access 2007 and Access 2010 version has a build-in Date picker, which works great for me.

So, its either try that particular version (MSCOMCT) to work or try to get the older version work (MSCAL). Both ways will be time consuming, however you do know that the build-in version works at least.

Just my 2 cts ...

HTH,
Daniel
0
 
QualitycomputerAuthor Commented:
Assuming that I just want to use the built-in date picker, is there an easy way to remove the references to the old calendar controls to avoid the error messages?  If I just remove the dead control from the form will the date picker automagically appear?
0
 
QualitycomputerAuthor Commented:
Answering my own question I can say that removing the old control does not help.  The problem is that the "set beginning date" button, for example, on the form calls the following code:

Private Sub SetDate_Click()
   
Me.grpSelect = 0

    On Error GoTo SetDate_Error
   
    If SetDate.Caption = "Set Beginning Date" Then
        BeginningDate = Format(SelectDate.Value, "short date")
        SetDate.Caption = "Set Ending Date"
    Else
        EndingDate = Format(SelectDate.Value, "short date")
        SetDate.Caption = "Set Beginning Date"
    End If
   
    Exit Sub
   
SetDate_Error:
    MsgBox Err.Description
    Exit Sub
End Sub


Is it viable to try to replace the numerous instances of the start and end dates with an alternative control (built-in or 3rd party)?
0
 
danishaniCommented:
Yes, just follow these steps as described in below article;
http://msdn.microsoft.com/en-us/library/gg251104.aspx

HTH,
Daniel
0
 
Nick67Commented:
Pretty much.  Textboxes formatted as date or bound to date fields activate it
0
 
Nick67Commented:
If you can post it, I'll sort it out
0
 
QualitycomputerAuthor Commented:
So, thanks to both of you the date picker does seem to be working generally within the application and my database.  It's actually a very cool feature -- for the last 10 years I've been entering dates manually for many fields and now it is just a simple click on the calendar.  

What has been and remains the problem are those forms that invoked the old calendar control.  In addition to the option that I mention below (replacing the old form with a new form) I would be willing to simply delete the form and use the date picker.   My Access programming skills, however, have declined considerably from when I did alot of the tweaking within the database (several years ago) and I'm not sure how to remove the old controls.  Simply deleting the box from the form using Design View obviously wont do it.  The article that Daniel sent is very good but not quite enough information for me given my current skill set with Access.  Of the options presented in the article the following approach looks the most promising:

"Use a Custom Calendar Form

In addition to the previous techniques, you may want a calendar that always appears, exactly like the old Calendar control. One way to achieve this is to use a subform that includes all the behavior of a calendar. At least one of these was created for you, and the sample application includes the form that is named frmCalendar. The form is self-contained-you just import the form into your own application and then you can embed it into any other form as a subform. Open frmCalendar in Design view (see Figure 7) and you will see that it is a small form with a buttons and other controls-the code within the form does all the work of updating and managing the form. In the ribbon, in the Tools group, select View Code. Review the code. Do not change the code. Close the Visual Basic editor and then frmCalendar when you are finished."


Nick, you've generously offered to look at this for me but I'm not clear which code would be helpful to post.  Would screenshots of the design view be helpful or would you want the Visual Basic code such as I've copied in earlier posts?  Once I know what to replace in the forms I'm sure that I can manually edit each and every form that calls the calendar control, but might there be a "find" option that would show me where all of these instances of the control are located?  I realize that this may be too much to hope for.

Thanks again!!!
0
 
Nick67Commented:
If it is possible, post a copy of the db with just a few rows of data.
If that is not possible, export the problem forms and enough tables, with a few rows of data, for me to look at the problem objects.
If that is not possible, start with posting the code.

With this code
Private Sub SetDate_Click()
   
Me.grpSelect = 0

    On Error GoTo SetDate_Error
   
    If SetDate.Caption = "Set Beginning Date" Then
        BeginningDate = Format(SelectDate.Value, "short date")
        SetDate.Caption = "Set Ending Date"
    Else
        EndingDate = Format(SelectDate.Value, "short date")
        SetDate.Caption = "Set Beginning Date"
    End If
   
    Exit Sub
   
SetDate_Error:
    MsgBox Err.Description
    Exit Sub
End Sub

What are BeginningDate and EndDate?
Are they textbox controls, or variables?
With my db, when I used the calendar control, the GotFocus event of textboxes opened a calendar form
This is different.
How did the calendar play into this code you posted?
What kind of control is SelectDate?
0
 
QualitycomputerAuthor Commented:
Nick,

I exported the form that is called by the reports that fails due to the missing calendar control.  Will this form be sufficient to give you an idea of what I will need to find and replace in my database?    Also, it might help for you to note that I use the "Frequent Date" buttons on the right side of the form.  I'm not sure if they will fail too as I never get that far without the missing control.
Report-Date-Range.mdb
0
 
Nick67Commented:
I think so.
So, you use this form to set BeginningDate and EndDate, which are then used as parameters to open a report?
And the Calendar control may either be removed, so that the native date picker can be used, or an Access 2010 native control put in place/

That, I can do, but overnight.
My Access 2010 box is at home
0
 
QualitycomputerAuthor Commented:
Nick,

Yes, exactly -- this form seems to be the bottleneck.  Removing or adding the native control would be perfect.  If that works you will have saved me a tremendous amount of time and effort.  Will points truly serve as sufficient payment for your services?  
0
 
Nick67Commented:
Yes.

Some day soon I may put a 'Hire Me' button on my profile, but it is poor etiquette here to solicit payment, or offer it--and I don't quite understand the rules involved with that.  I know that it's naughty to post a question to solicit bids, so I am not sure how it all works.

No matter, I am not looking to be paid.  I am very happy to have found a place to post those registry keys that got the calendar control working for me someplace where they may do someone else some good.

I'll tackle this at home, this evening
0
 
QualitycomputerAuthor Commented:
I'm not trying to be a naughty Experts Exchange user - not at all.  I'm just so grateful to have help and don't want to take advantage of your generosity.  Thanks again in advance.
0
 
Nick67Commented:
No Problem
<I don't quite understand the rules involved with that>

I don't know myself, no implications :)

We'll get 'er cased
0
 
QualitycomputerAuthor Commented:
Just checking in to see if you have made any progress on the calendar form.  I'm certainly not trying to rush you, nor am I in a hurry to have this done.  I get periodic reminders from EE requesting me to keep the question alive.
0
 
Nick67Commented:
My bad.
Life caught up with me.
I coach soccer Tues/Thurs and I forgot.

Thanks for the pingback.
I'll work on it
0
 
Nick67Commented:
This should work.
Access 2003 doesn't have the date picker, but this should play nice.
Let me know.

Report-Date-Range.mdb
0
 
Nick67Commented:
Tried it at home on Access 2010
It plays nice.

Let me know how it goes and if you needed it documented better
0
 
QualitycomputerAuthor Commented:
The new form works great!  That solves the problem very nicely.  Many thanks to both of you for all of your expert help!!!
0

Featured Post

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!

  • 13
  • 11
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now