In our earlier discussion on Animated Floating Calendar, the method which we have used to display the Calendar Control needs refinement, as I have mentioned there. We will look into the following points and how to deal with them:

  1. Advantages & disadvantages of Custom Toolbar Button & Shortcut Menu Button for displaying the Calendar Control and whey we need both?
  2. When there is no need for a Calendar Control on a Form how to disable the Buttons?
  3. When the Target Control is not a Date type field how to prevent the Calendar from appearing?
  4. Do we need the Custom Toolbar or Shortcut Menu Button to display the calendar, is there any other method?

Let us look into the above Questions, one by one.

The Custom Toolbar Button creation method was easy to explain without going too deep into the Shortcut Menus. Shortcut Menus are Popup Class of Menus and different from Toolbars. We will learn more about creating Custom Menus, Toolbars & Popup Menus for our Applications later.

To run our Calendar, the easiest option is using the Shortcut Menu button. We can right-click on a field to make that field active and then click on the Shortcut Menu button, which appears near to the field.

To use the Toolbar button, which normally appears at the top, first you must click on a field on the Form to make that field active and then go up and click on the toolbar button. And every time we will be going up and down this way to use the Calendar.

Since, the button that we have copied is on a built-in Shortcut Menu, when you open another database on the same machine (with the Calendar Control and its associated programs of course) the button on the shortcut menu will be available to display the Calendar Control, but not the New Toolbar that we have created in the other database.

This trick will work only on the Machine that you have created the button on the built-in Shortcut Menu. If you open the same database on another machine the shortcut menu button will not appear there. But the Custom Toolbar Button goes along with the Database to the new machine. We can easily make a copy of our toolbar button on the shortcut menu while installing our Application there.

If we need a copy of the Custom Toolbar in a different database then open that database and Import it from the one, which already have the Custom Toolbar. Select File – – > Get External Data – – > Import Option and select the database that you already have the Custom Toolbar and click Import button. Click the Options>> button on the Import Objects dialogue control to display the import options section and put a check mark in Menus and Toolbars. Don’t select any of the other objects like Tables, Query and Forms etc. Only the Menus and Toolbars will be imported.

Import Toolbar image

So we have the advantages of both options, in such situations.

There must be a way to restrict the use these Buttons on specific locations where we actually need it and put away or disable it in other times. This way we can prevent the user from running the program by mistake.

Then, there must be a way to address our Button through Code and enable or disable it at situations that demands it. The best approach is disabling the buttons at startup. Enable the button when the user opens a form with the Calendar Control. When the form is being closed disable it again.

The Menu Bars, Toolbars & Shortcut Menus falls into a Class of Objects known as Commandbars Collection and each Commandbar have a name and an index number. We baptized our Custom Toolbar (Commandbar) as ToolCal (Toolbar for Calendar in short, what a name!). Each Button also falls into the category of Commandbar.Controls; we have named our button as Calendar, so easy to remember. With this background knowledge we will deal with the enable/disable business.

  1. Copy the following Code and paste it into the same Global Module where you have copied our earlier routines of the Calendar programs. We can run this routine by adding a line of code in the appropriate locations on the Forms.
    Private Sub EnableDisable(Byval intStatus as integer)
    Dim cbr1 As CommandBar, cbr2 as Commandbar
    DoCmd.ShowToolbar "ToolCal", acToolbarYes 
    Set cbr1 = CommandBars("ToolCal")
    Set cbr2 = CommandBars("Form View Control")
    Select Case intStatus
          Case 0
             cbr1.Enabled = False
             cbr2.Controls("Calendar").Enabled = False
          Case 1
             cbr1.Enabled = True
      cbr2.Controls("Calendar").Enabled = True
    End Select
    End Sub
    • The Docmd.ShowToolbar "ToolCal", acToolbarYes will bring the Toolbar up if it is not already visible
    • Cbr1.Enabled = False will disable the full Toolbar controls, which means, if you have more than one Button on the Toolbar all of them will be disabled.
    • cbr2.Controls ("Calendar").Enabled = False: Here we are addressing a particular button on the ‘Form View Control’ CommandBar to disable only that button leaving others untouched.
  2. The best point to run a routine to disable all the buttons, that runs our Calendar Control, is on the Startup Screen Module. Copy and paste the Code given below into the Startup Screen’s Form_Load() Event Procedure. Since we already have a Form_Load() Event Procedure running there we need to copy the middle line into the module:
    Private Sub Form_Load()
       EnableDisable 0 ' 0 to disable and 1 to enable.
    End Sub

    Now we can easily provide our user the facility to use the Calendar without running into trouble and attend to his/her distress calls every now and then. All we have to do is to introduce the following lines of code into the Form Module where we have installed our Calendar control:

    Private Sub Form_Load()
       EnableDisable 1
    End Sub
    Private Sub Form_UnLoad()
     EnableDisable 0
    End Sub

    All the fields on a Form don’t need the use of the Calendar. Since, our Calendar is any time anywhere type implementation we need to limit its activity where we actually need it. As I have mentioned earlier we need little more code to do this. Before we go into the code let us list out the steps that we need to take, to write the code when the user clicks on the Calendar Button.

    • We must see whether this control is an Unbound Text Control or a Bound Text Control linked to a table or Query field.
    • If it is an Unbound Text Control but it is intended to accept a date then we must identify the control by checking its Format Property or Input Mask Property, which of course we will be designing and setting it up for the convenience of the User and for us too.
    • If it is a Bound Text Control and the Format or Input Mask Property is already set with appropriate string values it will make the task easier.

      Otherwise we have to take the Control Source and Record Source Property Values and go deep into the Control Source Table or Query and pull out the Data Field and check its type and determine whether it is a Date type field or not. Any way, we must be prepared for both situations.

      Once we got the result of the above validation we can determine to show or not to show the Calendar.

    • Copy the following Code into the same Global Module that you have copied the Calendar() Program:
      Public Function Check4Date() As Boolean
      Dim ctl As Control, frm As Form
      Dim RecSource As String, ctlSource As String
      Dim dtFormat As String, ctlType As Integer
      Dim fldType As Integer, fldformat As String
      Dim ctlName As String, statuscode As Integer
      Dim tblDef As TableDef, qryDef As QueryDef
      Dim tblDefFlag As Boolean, cdb As Database
      On Error GoTo Check4Date_Err
      dtFormat = "dd/mm/yyyy"
      Set frm = Screen.ActiveForm
      Set ctl = Screen.ActiveControl
      ctlType = ctl.ControlType
      If ctlType = 109 Then 'not a Textbox Control, terminate the function
         Check4Date = False
         Exit Function
      End If
      ctlSource = ctl.ControlSource
      If Len(ctlSource) = 0 Then
          statuscode = 1 ' unbound textbox
          statuscode = 2 ' bound textbox
      End If
      fldformat = ctl.Format
      'bound/unbound if format is date then valid
      If fldformat = dtFormat Then
         Check4Date = True   
      Exit Function
      End If
      If statuscode = 2 Then
          RecSource = frm.RecordSource
          ctlName = ctl.Name
          Set cdb = CurrentDb
      'Check it is a Table or not
          tblDefFlag = False
          For Each tblDef In cdb.TableDefs
              If tblDef.Name = RecSource Then
                  tblDefFlag = True
                  Exit For   
              End If    
      'if it is table then check the field type    
      If tblDefFlag Then
              Set tblDef = cdb.TableDefs(RecSource)
              fldType = tblDef.Fields(ctlName).Type
                 If fldType = 8 Then
                    Check4Date = True
                    Exit Function
                  End If
      End If
      'it is not a table check in Query Definitions    
      Set qryDef = cdb.QueryDefs(RecSource)
          fldType = qryDef.Fields(ctlName).Type
              If fldType = 8 Then
                  Check4Date = True
                  Exit Function 
             End If
      End If
      Exit Function
      MsgBox Err.Description, , "Check4Date_Err"
      Resume Check4Date_Exit
      End Function
    • Add the following Code snippet at the beginning of the Calendar() Program immediately below the Dimension statements to run the Check4Date() routine and decide whether to show the Calendar or display a message that the active Control doesn’t deserve the use of a Calendar:
    Dim fldstatus As Boolean
    Fldstatus = false ' initialize variable
    fldstatus = Check4Date() ' check the control type 
    If fldstatus = False Then
      MsgBox "Sorry, Not a Date Type Control."  
    Exit Function
    End If

    Without going through all the trouble of creating Custom Toolbars, Shortcut Menus, Validation checks etc. you can use the Calendar on specific fields on the Form by doing some extra work on every form that we insert our Calendar Control. Look at the sample Screen given below:

    We have created two small Command Buttons one each for Visit Planned Date & Actual Visit date fields and selected its button image Calendar from the picture collection of the Command Button Wizard. The command button is resized and placed close to the Field’s right edge. Now we need to run two lines of code on the On Click Event Procedures of both buttons to set their corresponding field active and call the Calendar Program to display the Calendar and insert the date clicked into the active field. In our example, the command buttons were named as cmdCal1 and cmdCal2 and the Click event procedures are given below:

    Private Sub cmdCal1_Click()
    End Sub
    Private Sub cmdCal2_Click()
    End Sub

    I hope this will clear things up and you will be encouraged to start experimenting some of these methods and if you have different ideas, please share with me.