Calendar and Toolbars
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:
- Advantages & disadvantages of Custom Toolbar Button & Shortcut Menu Button for displaying the Calendar Control and whey we need both?
- When there is no need for a Calendar Control on a Form how to disable the Buttons?
- When the Target Control is not a Date type field how to prevent the Calendar from appearing?
- 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.
- 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.
- 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 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
- 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() 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.