Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

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

    • 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

    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:

    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.

    Share:

    4 comments:

    1. This is the first time I am coming to this page. Good stuff! :-) Is it possible to write an access macro for a linked excel table that can ativate another macro within the excel table?

      ReplyDelete
    2. Welcome to the Site and thank you for the compliments.

      It is Possible. I will publish a Post at a later date with more details. In the meantime try this: Write an Automacro first in your Excel File.

      Open your Excel File (say Test.xls) and display the VB Module (Tools - > Macro - > Visual Basic Editor).

      Double-Click on the Thisworkbook object at the left side window. Its empty Code Module will display at the right side . Select Workbook from the top control where (General) is shown. Two lines of Code will display automatically as shown below except the middle line. The middle line you have to write. If the Code displayed is not the Workbook_Open() Event Procedure then select Open from the (Declarations) control above.

      Private Sub Workbook_Open()
      MsgBox "Hi, Muscat Santra"
      End Sub

      Save the Excel file as C:\Test.xls.

      Write the following Code inside a Click Event Procedure of a Command Button in a Form to Open the Excel File:

      Call Shell("C:\Program Files\Microsoft Office\Office\Excel.exe C:\Test.xls")

      The Excel File will open and show a warning message about the Macro. Click on Enable Macros button after that you will see the Welcome Message that we have written in the Workbook Open Event Procedure.

      I will bring out more details on this, like how to check and find which excel file is linked into Access and how to find its Name with code and automatically open the Linked Excel File. How to Link External Data Files, like Dbase, Excel, ODBC etc. will be explored.

      Regards,

      ReplyDelete
    3. I am the first time on this site and am really enthusiastic about and so many good articles. I think it's just very good.
      Always yours Mr. Cialis

      ReplyDelete
    4. Conveyancing Solicitor...

      [...]here are some links to sites that we link to because we think they are worth visiting[...]...

      ReplyDelete

    Comments subject to moderation before publishing.

    PRESENTATION: ACCESS USER GROUPS (EUROPE)

    Translate

    PageRank

    Post Feed


    Search

    Popular Posts

    Blog Archive

    Powered by Blogger.

    Labels

    Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code