- Microsoft Access Calendar Control For Windows 10
- Microsoft Access Calendar Control 7.0
- Microsoft Calendar Control 11.0 Access 2010
- Access Microsoft Calendar Control 参照不可
- Microsoft Calendar Control 11
- Microsoft Access Calendar Control For Free
A date picker is a control that lets users quickly enter dates in a Microsoft Office InfoPath form by clicking a small pop-up calendar. For example, you can use a date picker to collect birth dates from people on an insurance form. You can also use a date picker to display preset dates, such as the current date.
Oct 31, 2017 Does MS Access 2016 64 bits have any date time picker control to work with form base or ActiveX? If not, how MS Acess 2016 64 bits application to deal with date time picker user interface? It should have a way to let users to enter date using calendar using MS Access 2016 64 bits. Thanks again for helping, Regards, Sourises. There are plenty of ActiveX control calendars, but they have issues with versioning, broken references, and handling Nulls. This is an ordinary Access form you can import into any database. Download the zip file (30 KB) for Access 2000 and later or Access 97. MS Access by default provides inbuilt functionality to pick dates using calendar control; however it lacks few basic functionalities which makes selecting a date bit difficult. For example, if you want to select a date from year 2015 then you need to navigate multiple times to select correct date since you can move only one month at a time in MS Access calendar control.
In this article
When to use a date picker
Use a date picker when you want to:
Enable users to enter dates of their choice on a form, such as start and end dates for a particular project.
Use the today and now functions to show today's date and time on a form.
Display dates from an external data source, such as billing and shipping dates from a database of customer orders.
In the following illustration, a date picker is used on a travel request form template to collect the departure date for a flight.
You can customize the date picker to suit your needs by double-clicking the control on the form template and then changing its properties. For example, you can specify a default date or change the way that the date is displayed on the form.
The user experience
Date pickers look like text boxes, except that a small calendar icon appears on the right side of the box. To open the pop-up calendar, users click the calendar icon. When the calendar appears, users can click the date that they want on the calendar or use the right and left arrow buttons to scroll through the months. If users want to insert the current date, they can click the Today button at the bottom of the calendar.
Compatibility considerations
When you design a form template in InfoPath, you can choose a specific compatibility mode to design a browser-compatible form template. When a browser-compatible form template is published to a server running InfoPath Forms Services, and then browser-enabled, forms based on the form template can be viewed in a Web browser. When you design a browser-compatible form template, some controls are unavailable in the Controls task pane because they cannot be displayed in a Web browser.
Although date pickers are available when you design a browser-compatible form template, be aware of the following limitations:
Date pickers are fully supported only in Windows Internet Explorer browsers. In other Web browsers, the date picker appears as a text box, although date formatting options are supported in the text box.
Placeholder text is text that appears inside a control on a form template. An example of placeholder text is 'Click here and type.' Although you can leave placeholder text settings for browser-compatible form templates, that text does not appear in Web browsers.
Insert a date picker
The procedure for inserting a date picker differs slightly depending on whether you are designing a new, blank form template or basing the design of your form template on a database or other external data source.
The following illustration shows how a date picker looks when it is selected in design mode.
Controls can be bound or unbound. When a control is bound, it is connected to a field or group in the data source so that data entered into the control is saved in the underlying form (.xml) file. When a control is unbound, it is not connected to a field or group, and data entered into the control is not saved. When you select or move your pointer over a control, text and a binding icon appear in the upper-right corner of the control. The text indicates the group or field to which the control is bound in the data source. The icon indicates whether the control is correctly bound to that group or field. When the binding is correct, a green icon appears. If there's something wrong with the binding, you'll see a blue or red icon instead.
The data source for the form template consists of fields and groups that appear in a hierarchical view in the Data Source task pane. Date picker controls are always bound to fields. In the following example, the Departure date date picker on the form template is bound to the departureDate field in the Data Source task pane.
Insert a date picker on a new, blank form template
When you design a new, blank form template, the Automatically create data source check box in the Controls task pane is selected by default. This enables InfoPath to automatically create fields and groups in the data source as you add controls to the form template. These fields and groups are represented by folder and file icons in the Data Source task pane.
On the form template, place the cursor where you want to insert the control.
If the Controls task pane is not visible, click More Controls on the Insert menu, or press ALT+I, C.
Under Insert controls, click Date Picker.
To add a label to the control, type text above or to the left of the control, followed by a colon (:).
To specify the way that the date is displayed on the form, double-click the date picker that was inserted on the form template.
Click the Data tab, and then click Format.
In the Display the date like this list, in the Date format dialog box, click the display format that you want.
Note: You can change the display format of the date only if the data type is text, date, or date and time.
Insert a date picker on a form template that is based on an existing data source
If you base the design of your form template on an existing Extensible Markup Language (XML) file, database, or Web service, InfoPath derives the fields and groups in the Data Source task pane from that existing data source. In this scenario, you can add a date picker either by dragging a date picker field from the Data Source task pane onto the form template or by inserting the date picker by using the Controls task pane, as described in the following procedure:
On the form template, place the cursor where you want to insert the control.
If the Controls task pane is not visible, click More Controls on the Insert menu, or press ALT+I, C.
Under Insert controls, click Date Picker.
In the Date Picker Binding dialog box, select the field in which you want to store the date picker data, and then click OK.
InfoPath uses the name of the field or group as the control's label. If necessary, change the label text.
To specify the way that the date is displayed on the form, double-click the date picker that was inserted on the form template.
On the Data tab, click Format.
In the Display the date like this list box, in the Date format dialog box, click the display format that you want.
Note: You can change the display format of the date only if the data type is text, date, or date and time.
Tip: You can also use the Data Source task pane to insert controls. In the Data Source task pane, right-click the field that you want to bind the date picker to, and then click Date Picker on the shortcut menu.
Display the current date in a date picker
To automatically insert the current date on a form when users open it, you can use the today function with a date picker control.
Insert a date picker on the form template.
Double-click the date picker that you inserted on the form template.
Click the Data tab.
Under Default Value, click Insert Formula .
In the Insert Formula dialog box, click Insert Function.
In the Categories list in the Insert Function dialog box, click Date and Time.
In the Functions list, click today, and then click OK twice to return to the Date Picker Properties dialog box.
Notice that today() appears in the Value box.
Under Default Value, clear the Update this value when the result of the formula is recalculated check box.
Note: If you clear this check box, the date isn't updated automatically each time a user opens a form based on this form template. This is ideal in situations where you want users to see the date when the form was created. You can leave the check box selected in situations where you have a Last updated on or Last opened on field, where you want the date to change every time the form is opened.
Click OK to return to the form template.
To test your changes, click Preview on the Standard toolbar, or press CTRL+SHIFT+B.
Verify that the date that appears in the date picker looks the way that you want it to. If it doesn't, you can change how the date or time is displayed by clicking the Format button in the Date Picker Properties dialog box.
Display the current date and time in a date picker
To automatically insert the current date and time on a form when users open it, you can use the now function with a date picker control.
Insert a date picker on the form template.
Double-click the date picker that you inserted on the form template.
Click the Data tab.
In the Data type box, click Date and Time (dateTime).
Click Format.
In the Date and Time Format dialog box, in the Display the time like this list, click the option that you want, and then click OK.
In the Date Picker Properties dialog box, under Default Value, click Insert Formula .
In the Insert Formula dialog box, click Insert Function.
In the Categories list in the Insert Function dialog box, click Date and Time.
In the Functions list, click now, and then click OK twice to return to the Date Picker Properties dialog box.
Notice that now() appears in the Value box.
Under Default Value, clear the Update this value when the result of the formula is recalculated check box.
Note: If you clear this check box, the date isn't updated automatically each time a user opens a form based on this form template. This is ideal in situations where you want users to see the date when the form was created. You can leave the check box selected in situations where you have a Last updated on or Last opened on field, where you want the date to change every time the form is opened.
Click OK to return to the form template.
To test your changes, click Preview on the Standard toolbar, or press CTRL+SHIFT+B.
Verify that the date that appears in the date picker looks the way that you want it to. If it doesn't, you can change how the date or time is displayed by clicking the Format button in the Date Picker Properties dialog box.
Layout tips
Use the following tips to help you refine the appearance, size, and other aspects of a date picker:
To change the size of several date pickers at once, select the date pickers whose size you want to change, on the Format menu, click Properties, and then on the Size tab, make the necessary adjustments.
To change the border or color of a date picker, select the date picker, on the Format menu, click Borders and Shading, and then make the necessary adjustments.
To customize the text that appears in a date picker, use the Font and Font Size boxes on the Formatting toolbar. To change the font and font size for all of the date pickers on your form template at once, right-click the date picker that contains the formatting that you want, and then click Apply Font to All Date Picker Controls on the shortcut menu.
To see what your date picker will look like with actual text in it, click Sample Data on the View menu. This helps you get a sense of what the form will look like when users fill it out.
On occasion, the text inside a date picker and the label to the left of the date picker may appear to be slightly out of alignment. To quickly realign them, double-click the date picker, click Align on the Size tab, and then click Apply. If the label and date picker are in different cells in a layout table, right-click the cell that contains the label, click Table Properties on the shortcut menu, and then click Center under Vertical alignment on the Cell tab.
To change the way that the date is displayed, double-click the date picker, click the Data tab, and then click the Format button. Choose a display format in the Date Format dialog box.
so the Calendar Control had to be altered to work in the patched Office 2016 applications.
Some feedbacks we received from our users!
Contents
- 2 Calendar Class Project
- 3 Technical details - Reference
- 3.1 Properties
- 4 How to migrate Calendar Class into your existing project?
- 6 UPDATES
Missing Calendar Control
Calendar Control - for one reason or another, this is not part of the basic VBA form component kit. Microsoft manages Calendar Control in a separate way, in an own .ocx package - it is usually installed with Microsoft Office.When you upgrade your Office version to the new Office 2010 you can face several compatibility problems. After reading this error message: “Could not load an object because it is not available on this machine” you need to find out what is the problem.
First check the references of your VBA project. In the VBA window choose Tools / References for your VBA Project. You may see something like this:
(MANCA = MISSING and in some versions, Calendar Control had local name.)
Now it is clear that the missing mscal.ocx, the Calendar Control is the problem. We googled for a solution and soon found this on Microsoft TechNet ( http://technet.microsoft.com/en-us/library/cc179167.aspx )
“Calendar control (mscal.ocx) is removed in Access 2010 and is not usable for Excel 2010. Instead, users can use Date Picker or their own custom calendar controls.”
So, we have tried Date Picker, and our experience is that it is not easy to use in existing applications. The property and event structure is different from the Calendar Control. In most of the cases it also needs to be installed (it does not exits in Excel 2007).
There are some hack-kind solutions for the 32-bit version Office (by installing the old mscal.ocx on each workstation where you want to use your application) but at the moment 64-bit version of the .ocx does not exist. Maybe never will...
So, what can we do? We can cook one!
Ingredients:
41 buttons and label boxes
2 pieces of combo boxes
8 piece of label box
Approx. 1300 row VBA code
Take a Class module, put most of the VBA code into it. Mix it with your application. Take out the decayed items, replace it with the new, freshly-cooked one, served in a simple frame! All of your applications will easily digest it!
Now, seriously....
We build the control as a class using the very basic elements (buttons, combo boxes, label box) for avoid the problem of compatibility. Forever. (Knock on wood!)
So, we developed a non-ActiveX, non-MSCAL.OCX dependent calendar class for VBA UserForms. It means you do not need to install anything. (It use only the MSFORMS.OCX, ) Our Calendar Class will be part of your application. In case you share your file with anyone else, it will work on his/her computer without any system changes. The only thing to do is to migrate the Calendar Class into your existing application, and use it in your future projects.
It was a huge project, a tremendous thing for us. We spent approx. 20 days with it, mainly the late-night hours. The team-work was very effective, rousing and funny - we really enjoyed it! We are stopping now at version 50 - this works well for almost all of the cases. If you see any problem or inconvenience please do not hesitate to contact us!
You can find the files at the bottom of this page.
Authors:
| Roberto Mensa |
| Gábor Madács Krisztina Szabó |
Calendar Class Project
The concept
Our Calendar Class works in all version of Office VBA (Excel, Word, PowerPoint, Outlook)In the below tables you can find all of the Parameters of the Calendar Class. To ensure compatibility, parameter names are exactly the same as it was in the Calendar Control.
Our goal was to create only one Class module to make the users easier to import and migrate the new Calendar Class.
The original Calendar Control is an ActiveX Control, has to be referenced in the project, and it can be added to the from via the component toolbox. The Calendar Class itself can not be added directly to the form, we need to put it to the source, as a new member of the form (top of the source page, before any subs or functions):
For convenient visual placement and size definition Calendar Class should be placed into a new, own frame instance on the form - for example:
This frame should be referenced in the Form_Initialize() method after creating the Control object, for above example:
Private Sub UserForm_Initialize()
Set Calendar1 = New cCalendar
Calendar1.Add_Calendar_into_Frame Me.Frame1
End Sub
The Calendar functionality is built up from buttons, so the grid-related properties can not work.
Development experiences
The aim of the project was to create a Class with exactly the same properties, methods and events as it is in the Calendar Control. We decided to use buttons to represent the days. It turned out soon that this solution does not allow to create a small sized calendar because the position of button caption is pre-defined within the button. So we changed the concept a bit and put label boxes behind the buttons.Certainly with this button-solution we do not have Grid - this is the reason why some property will not work - however all of them created.
Test environment
We used the below form to test how the calendar works.Technical details - Reference
Properties
Original Calendar Control properties
| Parameter | Data Type | Description |
| BackColor | Long | Configures the background color of the Frame. |
| ControlTipText | String | Specifies text that appears when the user briefly holds the mouse pointer over a control without clicking. |
| Day | Long | Gets the current day of the month. |
| DayFontColor | Long | Configures the color used to display the days of the week. |
| DayLength | Long | Sets the format used to display the days of the week: 0 for Long (“Monday”), anything else for Short (“M”) |
| FirstDay | calDayOfWeek | Sets the day of the week to be displayed in the first column: 1 = dwMonday, 2 = dwTuesday… 7 = dwSunday |
| GridFontColor | Long | Specifies the color used to display the days of the month. |
| Month | Long | Sets the current month. Must be a value between 1 (January) and 12 (December). |
| MonthLength | Long | Configures the format used to display the month: Enumerations: mlLocalLong = 0 for local language long (szeptember) mlLocalShort = 1 for local language short (szept.) mlENLong = 2 for English long (September) mlENShort = 3 for English short (Sep) |
| ShowDateSelectors | Boolean | Specifies whether the calendar will display drop-down boxes for the month and year. A value of -1 means True and a value of 0 means False. |
| ShowDays | Boolean | Specifies whether the calendar display the days of the week. A value of -1 means True and a value of 0 means False. |
| ShowTitle | Boolean | Specifies whether the calendar display the month and year above the calendar grid. A value of -1 means True and a value of 0 means False. |
| TitleFontColor | Long | Determines the color used to display the month and year above the calendar grid. |
| Top | Single | The distance between a control and the top edge of the form that contains it, in points. |
| Height | Single | The height of the control, in points. |
| Width | Single | The width of the control, in points. |
| Left | Single | The distance between a control and the left edge of the form that contains it, in points. |
| Value | Variant | Sets the currently selected date. |
| ValueIsNull | Boolean | Specifies whether the value is Null (that is, no data is selected). |
| Visible | Boolean | Specifies whether the control is visible or hidden. |
| Year | Long | Sets the current year. |
| Tag | String | A user-defined string value. |
| TabIndex | Long | Specifies the position of a single object in the form's tab order |
| TabStop | Boolean | Indicates whether an object can receive focus when the user tabs to it. |
New properties for Calendar Class only
We created some new properties we think would be useful:
| UseDefaultBackColor | Boolean | Specifies whether it is possible to use different color for the weekends and header (False) or not = all days has the same color, like in the original Calendar Control (True) |
| HeaderBackColor | Long | Configures the background color of the Header (days of the week) |
| SaturdayBackColor | Long | Configures the background colorused to display Saturdays. |
| SundayBackColor | Long | Configures the background colorused to display Sundays. |
| YearFirst | Boolean | Determines the order of year and month in the label above the days. True means text begins with year: 2011 dec. False means text begins with month: dec 2011 |
Microsoft Access Calendar Control For Windows 10
Font properties with special change method
In this version of Calendar Class it is difficult to change the font setting at runtime, so we suggest to use these properties only in UserForm_Initialize. If you need to do the change at runtime, see section underHow to migrate...| DayFont | Font | Configures the font used to display the days of the week. |
| GridFont | Font | Specifies the font used to display the days of the month. |
| TitleFont | Font | Sets the font used to display the month and year above the calendar grid. |
Not working properties
The following properties are created to avoid migration errors but they are not working:| GridCellEffect | Integer | Determines the effect used to display the grid. |
| GridLinesColor | Color | Determines the color used to display the lines in the grid. |
| ShowHorizontalGrid | Boolean | Specifies whether the calendar display horizontal gridlines. |
| ShowVerticalGrid | Boolean | Specifies whether to display vertical gridlines. |
| HelpContextID |
Methods
| Aboutbox() | You can see the name of the authors :-) |
| Add(ByVal fForm As MSForms.UserForm) | Adds a new frame together with the Calendar Class to your form. |
| Add_Calendar_into_Frame(ByVal cFrame As Control) | Adds the Calendar Class to your existing frame. |
| Move([Left], [Top], [Width], [Height], [Layout]) | Moves/resizes the frame within the form. (Layout parameter is ignored.) |
| NextDay() NextWeek() NextMonth() NextYear() PreviousDay() PreviousWeek() PreviousMonth() PreviousYear() | Changes the value property of the Calendar class. |
| Refresh() | Refreshes the Calendar. (You need to call this method only if you want to change the font styles at runtime.) |
| Today() | Sets the Value property of the Calendar Class to the actual date. |
Events
| Click | Occurs when clicking on the calendar days. |
| DblClick | Occurs when the user points to a day in the grid and then clicks a mouse button twice. |
| BeforeUpdate(ByRef Cancel as Integer) | Occurs before changed data in the control. Setting the Cancel argument to True (–1) cancels the ongoing change. Note: This event is not working in the original Calendar Contol |
| AfterUpdate | Occurs after changed data in the control. Note: This event is not working in the original Calendar Contol |
Please note, the following events will not work in the Calendar Class:
Enter, Exit, KeyDown, KeyPress, KeyUp, NewMonth, NewYear
How to migrate Calendar Class into your existing project?
Based on our experience we highly advise you to replace the Calendar Control with Calendar Class before starting to use your project in Office 2010. In this case you can prevent the error messages and you can use our VBA code to collect the special properties of your old Calendar Control and build up the necessary subroutines - see under section 4.In case you already use Office 2010 and you do not have Calendar Control, you will see in your VBA Project References that Microsoft Calendar Control
is missing (MANCA = MISSING) (In some versions, Calendar Control had local name.)You have to tick out the missing control - it will not touch your existing code. Now you can migrate the new Calendar Class into your project as it is explained below.
Preparation
1.1. Copy the Calendar Control Class Module into your project.Open your project and the Calendar Class file, copy (drag-and-drop) the cCalendar Class module into your project. (This copy works between two different Office applications too!)
1.2. Now you have a new class module in your project with name cCalendar. You can change the name in the Properties window if you want. In this case, you have to use this new name in the following codes instead of cCalendar.
Change the code manually
The following steps should be done for all of your calendar control instances (as many calendar controls you have in your form).In this example we use the default name of Calendar Control: Calendar1. If you gave your calendar a different name, please replace Calendar1 with the name of your calendar in the below code rows.
2.1. Create placeholder frame
You need to create an empty frame control on your form where the Calendar Class will be placed. Move and resize the frame to the desired place of the Calendar.
You can change the name of the frame in the Properties window if you want. In the below code rows the frame is named Frame1.
2.2. Create object reference in the form code. Use the original Calendar Control’s name.
Write to the top of the code window of the userform:
Private WithEvents Calendar1 As cCalendar
2.3. Create Calendar object in the form code, and add to the frame:
Private Sub UserForm_Initialize()
Set Calendar1 = New cCalendar
Calendar1.Add_Calendar_into_Frame Me.Frame1
End Sub
(If you re-named your frame, please write the exact name instead of Frame1)
If you used the Calendar Control with default settings, you do not need more changes in your code.
Changing of the default settings
In case you want to change the default settings, you have to use the following code to create the Calendar object. Use this code instead of the shorter version mentioned under section 2.3 above.Private Sub UserForm_Initialize()
Set Calendar1 = New cCalendar
With Calendar1
<copy here the non-default properties>
End With
Calendar1.Add_Calendar_into_Frame Me.Frame1
End Sub
For example:
Private Sub UserForm_Initialize()
Set Calendar1 = New cCalendar
With Calendar1
.YearFirst = True ‘Label format: 2011 dec
.UseDefaultBackColor = False ‘Use advanced coloring
End With
Calendar1.Add_Calendar_into_Frame Me.Frame1
End Sub
Change the code with the help of the Migration routine
We created a great helper-code to collect all of the properties where the property value is not the default and create a specific setup subroutine you only need to copy into your code. You can use it only in old, before-2010 Office, where you have a living Calendar Control.Copy the Migration module from our file into your project.
On your old form, where your old calendar control(s) is placed, add to the very-end of the UserForm_Initialize subroutine a function call for the Migration_of_Calendar_Classes(fUserForm As Object).
For example this way - Me stands for your userform:
Private Sub UserForm_Initialize()
<your existing code lines>
...
<your existing code lines>
Debug.Print Migration_of_Calendar_Classes(Me)
End Sub
In this case you will have the code in the immediate window. The only thing you need to do is to follow the commented instructions.
Important note - bug in Office 2010 64 bit
Regarding Font changes please take into consideration that in case you add a control (in this case a Command Button) to your form at runtime (in Userform_Initialize) the Font.Underline and Font.Strikethrough property settings does not work. You can see on the below picture that the False setting does not change the button caption however other Font properties (bold, italic, etc) works correctly.After
Microsoft Access Calendar Control 7.0
UserForm_Initialize, when the form is fully created, the Font.Underline and Font.Strikethrough change works correctly. The below picture illustrates it with a Commandbutton2_Click event.How to change font styles at runtime (if needed)
We use as simple components as possible, thus we can not simulate every aspect of the original Calendar Control. Changing the font styles dynamically after the call of Add_Calendar_into_Frame method is a special issue, the changes has to be applied explicitly by the Refresh method.
E.g.:
...
myCalendar.DayFont.Italic = True
myCalendar.GridFont.Bold = True
myCalendar.GridFont.Italic = True
myCalendar.Refresh
...
Microsoft Calendar Control 11.0 Access 2010
Very simple usage example - Date Picker
Create an Excel Worksheet Date Picker!There will be a single button for show the Date Picker, always editing the active cell.
1. Open VBA macro editor
2. Insert a UserForm into your Excel project (Let’s call it UserForm1)
Access Microsoft Calendar Control 参照不可
3. Create a Frame on
Microsoft Calendar Control 11
UserForm1, with name Frame14. Copy this code into the code window of UserForm1:
Option Explicit
'UserForm with Frame1
Private WithEvents Calendar1 As cCalendar
Private Sub UserForm_Initialize()
Set Calendar1 = New cCalendar
Calendar1.Add_Calendar_into_Frame Me.Frame1
End Sub
Private Sub UserForm_Activate()
Calendar1.Value = ActiveCell.Value
End Sub
Private Sub Calendar1_DblClick()
ActiveCell.Value = Calendar1.Value
Unload Me
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Set Calendar1 = Nothing
End Sub
Now we can create a Command Button on a workseet with this code:
Private Sub CommandButton1_Click()
UserForm1.Show
End Sub
Microsoft Access Calendar Control For Free
Now when the button is pressed, the form with the calendar appears. If the active cell contains a date the calendar will show this date. If the selected cell is empty, the calendar displays the current date. We can change the year and month. With double click on the desired day button, the new date entered into the active cell, and the form disappears.3/3 :-)
Other controls
The main concept of our project was to develop a solution could step over the compatible errors using only the native Office forms controls. We found the same concept used by Jan Karel Pieterse in his MSForm based Treeview control, so we would like to share his tool here - sure it would also be useful for the users of our Calendar Control.