Calendar and Toolbars

Introduction

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, when 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 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.

Importing the Custom Toolbar

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 has the Custom Toolbar. Select File - - > Get External Data - - > Import Option and select the database that you already have the Custom Toolbar and click the Import button. Click the Options>> button on the Import Objects dialogue control to display the import options section and put a checkmark in Menus and Toolbars. Don't select any of the other objects like Tables, Query, Forms, etc. Only the Menus and Toolbars will be imported.

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

The Command Bar Collection

There must be a way to restrict the use of these Buttons in specific locations where we actually need them and put away or disable it at 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 in situations that demands it. The best approach is to disable 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, and Shortcut Menus fall into a Class of Objects known as Commandbars Collection and each Commandbar has 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 Command bar. 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 control, 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 users 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

   Preventing inadvertent Usage

   All the fields on a form don't need the use of the Calendar. Since our Calendar is an anywhere type implementation, we need to limit its activity where we actually need it. As I have mentioned earlier, we need a 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 check the Data type of the Field is Date or not. Anyway, we must be prepared for both situations.

   Once we got the result of the above validation checks we can decide whether to show the Calendar or not.

   Code to Validate Date Field

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

Avoiding Toolbars and Shortcut Menus

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 in 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 with some of these methods and if you have different ideas, please share them with me.

Enable GingerCannot connect to Ginger Check your internet connection
or reload the browserDisable in this text fieldEditEdit in GingerEdit in Ginger×Enable GingerCannot connect to Ginger Check your internet connection
or reload the browserDisable in this text fieldEditEdit in GingerEdit in Ginger×