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.

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.

    

3. 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
   Else
       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
       Next
   
   '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
   
   Check4Date_Exit:
   Exit Function
   
   Check4Date_Err:
   MsgBox Err.Description, , "Check4Date_Err"
   Resume Check4Date_Exit
   End Function

4. 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:

Click to Enlarge

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()
       Me.PlannedDt.SetFocus
       Calendar
End Sub

Private Sub cmdCal2_Click()
      Me.VisitDt.SetFocus
      Calendar
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.