Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Animated Floating Calendar

Introduction.

Calendar (the ActiveX Control) is a good object for clicking and inserting Date values into Fields quickly, instead of typing manually. 

There is only one problem, it occupies a lot of space on the form when it is placed for more Date Fields and it comes in the way of placing other controls on the Form, In short, a very useful and convenient object cannot go into our design and we may have second thoughts and decide to go with the traditional method: typing everything manually, after all, somebody else is going to do that, not the developer.

We are going to use the Calendar Control and we don't accept defeats and all those above reasons will not prevent us from using it wisely. The method that we are going to try out here is kind of hard to implement for the first time. But it will be very easy on other Forms in the same Project.

Microsoft Access Floating Calendar Project

Open your Database with a Form that you have already designed to use or design a new one with at least two date fields on the Form. Let us do the easy part of our project first. Copy and paste the following VBA Code into a Global Module and save it:

Option Compare DatabaseOption Explicit
'Global Declarations
Public Const twips As Long = 1440
Dim mm_actctl As Control

Public Function Calendar()
Dim sngStart As Single, CalCtrl As Control
Dim ctl As Control, frm As Form, t_height As Long
Dim m_left As Long, m_top As Long, i As Double
Dim w As Long, h As Long, y As Double
Dim caltop As Long, calheight As Long
Dim secHeight As Long
Dim frmWidth As Long, t_width As Long

On Error GoTo Calendar_Err

Set ctl = Screen.ActiveControl
Set mm_actctl = ctl
Set CalCtrl = Screen.ActiveForm.Controls("Cal1")
Set frm = Screen.ActiveForm

CalCtrl.Width = 0.1458 * twips ' 0.1458"
CalCtrl.Height = 0.1563 * twips ' 0.1563"
m_left = ctl.Left + ctl.Width
m_top = ctl.Top + ctl.Height
caltop = m_top
calheight = ctl.Height + (15 * twips * 0.106) '0.105"

secHeight = frm.Section(acDetail).Height
frmWidth = frm.Width
t_height = caltop + calheight
t_width = m_left + (15 * twips * 0.17) '0.17"

If t_height > secHeight Then
    m_top = secHeight - (calheight + (0.106 * twips))
End If

If t_width > frmWidth Then
   m_left = frmWidth - (15 * twips * 0.17) ' 0.17"
End If

CalCtrl.Left = m_left
CalCtrl.Top = m_top
CalCtrl.Visible = True

sngStart = Timer
i = 0.05: y = i
Do While Timer < (sngStart + 0.75)

If Timer >= sngStart + y Then
    y = y + i
    w = CalCtrl.Width + (0.17 * twips) ' 0.17"
    CalCtrl.Width = w
    h = CalCtrl.Height + (0.106 * twips) ' 0.105"
    CalCtrl.Height = h
    DoEvents
End If
Loop

Calendar_Exit:
Exit Function

Calendar_Err:
MsgBox Err.Description, , "Calendar"
Resume Calendar_Exit
End Function

'Insert Date into the active Field

Public Function Cal1Click()
Dim m_cal As Control, m_ctl As Control

On Error GoTo Cal1Click_Err

Set m_cal = Screen.ActiveForm.Controls("Cal1")
mm_actctl.Value = m_cal.Value
m_cal.Width = 0.1458 * twips ' 0.1458"
m_cal.Height = 0.1563 * twips ' 0.1563"
mm_actctl.SetFocus
DoEvents
m_cal.Visible = False

Cal1Click_Exit:
Exit Function

Cal1Click_Err:
MsgBox Err.Description, , "Cal1Click"
Resume Cal1Click_Exit
End Function

NB: While running this code if you end up with some error, please refer to my earlier Post Command Button Animation and link the essential Library Files to your Project as explained there.

MS-Access Calendar Control

  1. Open the Form in Design View.
  2. Select Ms-Access ActiveX Control from Insert Menu.
  3. Scroll through the Displayed List and find Calendar Control as shown in the image below:
  4. Select it and click OK. A Calendar Control is inserted into your Form.
  5. Drag it anywhere at a convenient place for the time being. We are going to change their properties. Click on it and display its property sheet and change the following property values as shown below:

Ms-Access Calendar Control Properties

  1. Name = Cal1
  2. Visible = False
  3. Special Effect = Raised
  4. Border Color = 0
  5. Back Color = 11139322
  6. Month Length = System (Medium) - Access 2003, e.g.: Jul 2007
  7. Grid Lines Color = 2147483632
  8. Grid Font Color = 10485760
  9. Title Font Color = 10485760
  10. Width = .1458"
  11. Height = .1563"

After changing the width & height properties the Calendar becomes a small rectangle and you can place it anywhere on the form, at a convenient place.

We need to copy a few more lines of Code in our Form's Code Module. When the Form is in
Design View Select Code from Toolbar Button above or select Code from View menu and paste the following code into the Form Module:

Private Sub Cal1_Click()
   Cal1Click
End Sub

Private Sub Detail_Click()
   Me.Cal1.Visible = False
End Sub

Private Sub Form_Load()
   Me.Cal1.Value = Date
End Sub

When you click on a date on the Calendar it calls the Routine Cal1Click() and inserts the clicked date into the Active Field and the Calendar disappears.

You have displayed the Calendar but you don't want to click on it to insert a date and at this point, you want to hide the calendar, then click on an empty area of the detail section of the Form, the Calendar will disappear. This is achieved through the Detail_Click() Routine.

When the Form with the Calendar is open the System Date is set as the default value for the Calendar with the Form_Load() Event Procedure. The Calendar will always open up with the current date.

If you are new to customizing ms-Access Menus and Toolbars you may be a little confused about the next part of this article. Don't worry, we are going to familiarize something that is already there and we have to learn its usage now or later, the earlier the better.

Creating Ms-Access Toolbar Button

We will create a new toolbar button and attach the program, that you have copied earlier into the Global Module. We will place a copy of the toolbar button in a Shortcut Menu as well, for easier usage of Animated Floating Calendar, on MS-Access Form.

Select the following Menu Option:

  1. View - - > Toolbar - - > Customize. Click New to create a new Toolbar and name it as ToolCal then click OK. A new small empty Toolbar will show up on top.
  2. Select the Commands Tab. Click and drag the New button and place it on the new Toolbar.
  3. Right-click on the new toolbar button, and point the cursor on the Change Button Image option on the displayed menu to show up several Button Images. You may select one of the Images you prefer to use.

    If you would like to create a button image yourself, you can do so by selecting the Edit Button Image Option from the Menu. I have selected the image of a fish, after all, it is a Floating Calendar. Right-click the button again to display the menu and type &Calendar; in the Name: &Name; control. We can set the button style to Image only, Text only, or Image and Text. The default style, which is already selected is just fine for now.

  4. Now, we must link our Calendar() program to the toolbar button. Right-Click on the toolbar button and select Properties from the displayed menu. Type =Calendar() (don't forget the = sign) in the On Action : text control and click Close.

    Our Toolbar Button is ready. We can use this Button to run our Calendar on the Form and we can stop here if we need to. But we plan to go a little further and add a copy of this button in the Form View Control Shortcut Menu so that we can right-click on a Date Field on our target MS-Access Form and click the button on the Shortcut Menu to display the Calendar. We are trying to make things a little easier for the Users. The Calendar is not visible till you click on the button.

  5. Select the Toolbars Tab on the Customize Dialogue Control and put a tick-mark for Shortcut Menus.

    Before attempting to copy the button let us locate the target spot, where we are going to place our new button. On the Shortcut Menus Options, you will find the heading Form, click on it to display a lengthy list of Sub-menu groups, among them find the one Named 'Form View Control' and click on it. A set of Options like Filter by Selection, Cut, Copy, Paste, etc., can be seen. This is the default shortcut menu that we see when we right-click on a Text Control, on Form View. Look at the image given below:

    The shortcut menus are different when you right-click on an empty area of the Form or on the record selector at the left border etc. So it is important that you make a copy of our new button on this particular Shortcut menu. There are some side-effects to this method, like when you click on the new toolbar button, on a Form without the Calendar Control on it or the Calendar control's name is not Cal1, etc., the program will show Error Messages. We have more Articles on this topic, which explore in detail, designing Microsoft Access Custom Menu-bars, Toolbars, Shortcut Menus, and how to use them on Forms and their links are given at the end of this page for your reference.

  6. Now, let us get on with our project. Press and hold Ctrl Key with one hand, click on the new toolbar button, and keep the mouse button pressed with the other hand to make a copy of the button (if you don't keep the Ctrl Key pressed the button will be dragged out of the Toolbar, rather than making a copy), drag and point on the Form Menu on the Shortcut Menu-bar to display the Submenu groups and drag towards the Form View Control Sub-Menu and point on it to display the Shortcut Menu options. Drop the button at the left border of the menu at a convenient location where other icons appear. Click the Close button to come out of the customization process.

Trial Run

We are ready to try out the Animated Floating Calendar. Open your Access Form in Form View and right-click on a Date Field. The new button on the Shortcut Menu, with the Fish Icon, and the Calendar Caption should be visible as shown below:

Click the Calendar Button on the shortcut menu. The ActiveX Calendar control will slowly unfold with the yellow background. It will appear to the right and below the Date Field that you have right-clicked. Not necessary that it should be a Date Field, you can right-click on any field and run the Calendar.

If we need to restrict inserting dates into date-type Fields only from the calendar, then we have to validate the control source of the active field from the underlying Query or Table's Field Type and decide whether to show the calendar or display a warning message and block the Calendar from showing up. That kind of refining can be done, but it takes a lot more code to implement. For the time being, we will stick to the simple thing and look into those aspects at a later stage.

Click on a date on the Calendar to insert that date into the field that you have right-clicked. If you don't want to insert a date and put away the Calendar instead, as I mentioned above, click on an empty area on the detail section of the Form, the Calendar will disappear.

Normally the Calendar will appear to the right and bottom edge of the control that you have right-clicked. This may change if the Date Field is too close to the bottom or right edge of the Form. Then the Calendar will appear within the Form itself in an appropriate place nearest to the field, to fit its size. This may overlap the field that you have right-clicked. But you can click on the Calendar and insert a date into that field.

NB: The Program may not work correctly if you attempt to use the method on a Sub-form.

Download


Download AnimatedCalender.zip



  1. Calendar and Toolbars
  2. Custom Menus and Tool-Bars
  3. Custom Menus and Tool-Bars2
  4. Startup Screen Design
Share:

3 comments:

  1. This is a great tool, and with some modification, I was able to make it work in a sub-form scenario.

    First, you will have to give up the ability for a person to manually enter a date. The calendar becomes the only form of entry. Second, (for best results) the calendar should be set to appear in a fixed point, on the screen.

    Assuming you can live with the above, then making the following code changes should help make things work on a sub-form.

    Step 1: Install the calendar contorl on the main form.

    Step 2: The three Private Sub code-sets, listed after step 5, should be added to the Main Form, and not the sub-form.

    Step 3: Change the third sub (Form_Load) to read:

    Private Sub Form_Load()
    [Forms]![mainform]![subform]![Cal1].Value = Date
    End Sub

    Even though you are selecting a control from a sub-form, that control is considered a member of the main form, and thus triggers the code from the main form.

    Step 4: In the code for function Calendar, add the following line before the line "Set CalCtrl = Screen.ActiveForm.Controls("Cal1")". This tells the calendar to always open to the date on the control you selected, rather than the last date entered into the calendar.:

    Screen.ActiveForm.Controls("Cal1") = ctl

    Step 5: Modify the m_left & m_top paramters to be actual numbers. These are in twips so perhaps 3000 & 800 respectively, would be good starting points. Adjust as needed.

    Step 6: Within the code of function Cal1Click() add a line between "DoEvents" and "m_cal.Visible = False" like:

    [forms]![formname]![controlname].setfocus

    Otherwise the calendar control can't be hidden again...


    Also, if you wish to change the size of your calendar, you can modify the twips multiplier (.17 and .105), within the following code. (Code is from Calendar function):

    w = CalCtrl.Width + (0.17 * twips) ' 0.17"

    h = CalCtrl.Height + (0.105 * twips) ' 0.105"

    Hope this helps.

    Sharkbyte

    ReplyDelete
  2. Hi,

    Thanks a lot for the good review and suggestions for improvement. I am glad that you like it and using it too.

    Regards,

    ReplyDelete
  3. Interesting article for all the .net framework students and learners
    nice to know new and interesting topics like this

    ReplyDelete

Comments subject to moderation before publishing.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code