Calendar and Toolbars

Introduction

In our earlier discussion on the Animated Floating Calendar, we noted that the method used to display the Calendar Control could benefit from further refinement. Let's now address a few key areas to enhance its functionality and reliability:

• The pros and cons of using a custom toolbar button versus a shortcut menu button—and when it's practical to use both.

• How to disable these buttons when the Calendar Control is not required on a form.

• How to suppress the Calendar if the target control is not bound to a Date-type field.

• And finally, whether we truly need custom buttons at all, or if there’s a more efficient alternative to display the Calendar.

Let us take a look at 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 a pop-up class of Menus and are different from Toolbars. We will learn more about creating Custom Menus, Toolbars, and pop-up menus for our Applications later.

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

To use the toolbar button—which typically appears at the top of the window—you must first click on a field in the form to activate it, then move up and click the toolbar button to launch the Calendar. This back-and-forth between the form and the toolbar can become tedious with frequent use.

Since the button we copied resides on a built-in shortcut menu, it will remain available even when you open another database on the same machine, provided that the Calendar Control and its supporting code are present in that database. However, the custom toolbar we created in the original database will not automatically carry over to the new one.

This trick works only on the machine where the shortcut menu button was originally created. If you open the same database on another machine, the shortcut menu button will not appear. However, the custom toolbar button is saved within the database itself and will travel with it to any new machine. You can easily recreate the shortcut menu button during application setup on the new system.

Importing the Custom Toolbar.

If you need a copy of the custom toolbar in a different database, open that database and import the toolbar from the one where it already exists. To do this, go to File → Get External Data → Import, and select the database that contains the custom toolbar. Click the Import button to open the Import Objects dialog box. Then click the Options>> button to expand the advanced options, and place a checkmark next to Menus and Toolbars. Make sure you don’t select any other objects, such as Tables, Queries, or Forms—only the Menus and Toolbars will be imported.

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

The Command Bar Collection.

We need a way to restrict the use of these buttons to specific contexts where they are actually required and hide or disable them elsewhere. This will help prevent users from accidentally running the program when it’s not appropriate.

Therefore, we must find a way to address our button programmatically and enable or disable it as needed. The best approach is to disable the button at startup, then enable it only when the user opens a form that contains the Calendar Control. When the form is closed, the button should be disabled again.

Menu Bars, Toolbars, and Shortcut Menus belong to a class of objects known as the CommandBars collection. Each CommandBar has a name and an index number. We named our custom toolbar 'ToolCal' (short for 'Toolbar for Calendar')—a simple and fitting name. Each button on a CommandBar is also considered a CommandBar control. We named our button 'Calendar' for easy recall. With this background, we’re now ready to handle the enable/disable functionality.

1. Copy the following code and paste it into the same global module where you previously added the Calendar program routines. We can invoke this routine by inserting a single line of code at appropriate points within our 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 line DoCmd.ShowToolbar "ToolCal", acToolbarYes will display the custom toolbar if it is not already visible.

   • Using cbr1.Enabled = False disables the entire toolbar, meaning all buttons on it—if there are multiple—will be inactive.

   • The line cbr2.Controls("Calendar").Enabled = False targets a specific button named "Calendar" on the 'Form View Control' command bar. This disables only that button while leaving the others unaffected.

2. The ideal place to run the routine that disables all the buttons associated with the Calendar Control is in the Startup Screen’s module.

   Copy and paste the code shown below into the Form_Load() Event procedure of the Startup Screen.
   

   If the Form_Load() The event already contains code; simply insert the middle line of the snippet into the appropriate location within the existing procedure.

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

   Not all fields on a form require the use of the Calendar. Since our Calendar implementation works anywhere, we need to restrict its use to only where it's actually needed. As mentioned earlier, this requires some more code. Before we dive into that, let’s outline the necessary steps to write the code that runs when the user clicks the Calendar button.

   • First, we need to determine whether the control is an unbound text box or a bound text box linked to a field in a table or query.

   • If it’s an unbound text control but intended to accept a date, we can identify it by checking its Format or Input Mask property—ideally set during design time for the user's and our convenience.

   • If it is a bound control, we can determine the data type by validating the source data.

   If it is a Date Field, then we can show the Calendar.

   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 need the Calendar Control:

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 the Visit Planned Date and Actual Visit Date fields—and assigned them with the Calendar icon from the Command Button Wizard’s image collection. Each button is resized and positioned near the right edge of its corresponding field.

Now, we need to add two lines of code to the On Click event procedure of each button. These lines will activate the appropriate field and invoke the Calendar program to display the calendar and insert the selected date into the active field.

In this example, the command buttons are named cmdCal1 and cmdCal2. The corresponding Click event procedures are shown below:

Private Sub cmdCal1_Click()
       Me.PlannedDt.SetFocus
       Calendar
End Sub

Private Sub cmdCal2_Click()
      Me.VisitDt.SetFocus
      Calendar
End Sub

I hope this has clarified things for you and encouraged you to start experimenting with some of these methods. If you have different ideas or alternative approaches, I’d be glad to hear from you—please feel free to share them.

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