Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Custom Menus and Tool Bars2

Continued from earlier Post: Custom Menus and Toolbars

Open your database where we have created the Custom Menu Bar with the name MyMenuBar. Select Customize from Toolbars sub-menu of View Menu. Or you can right-click anywhere on the Menu Bar or Toolbar above and select Customize from the displayed Shortcut Menu.

We can add built-in menu options from the Customize Control also without copying/dragging from other menus as we did in step 11 in the earlier Post. Click on the Commands Tab on the Customize Control. When you click on any of the Menu items on the left (in categories list) the built-in commands pertaining to that Menu is displayed in the Commands List at the right window. From here you can click and drag (no need to press and hold the Ctrl key) any option and place it on your Custom Menu Bar or Toolbar to organize the Built-in Options for the User.

12. We will concentrate on our totally independent Menu Options: Data Files, Process & Reports and create our own Programs/Macros to define the action on each one of them. A sample image of the Data File Options that I have added to the menu is given below:

When you click on any of the options in the Data Files Menu it will open a Form. I have selected three types of Command items to build the Menu Options. Custom for a single item with only description, New Menu to create a Sub-Menu option with a drop-down control & New for Buttons with icon or without it, from the Menu Bar/Toolbar Customize Control.

13. Select Files in the Categories List of Customize Control, click and drag the Custom control from the Commands List and place it on the Data Files menu's drop-down control. Right-click on the control and type Address Book in the Name: control.

NB: The name that you have typed in the Name: control is used for referencing the menu item in Programs and should not be the same name of a Built-in Menu. If it is, then Access will show a warning message. By default the Menu Name is displayed for its Caption also (or Menu description).

For example: Assume that we have named Data File as DataFile for our Menu without a space in between to use it as a single word in programs but we need Add/Edit Data as the menu description. Right-click on the Data Files menu, select Properties from the displayed menu and type Add/Edit Data in the Caption: box.

Repeat the process for the Department List option. The third item Other Data is a group item.

14. Find the New Menu item at the end of the Categories list and select it to display the New Menu control in Commands List. Click, drag and place it on the menu as shown and rename it as Other Data. The sub-menu have a dropdown control at the right side, where we will add few New control buttons.

15. Click on the Files Category in the Categories List (in the Customize Control). The Custom & New options are visible in the Commands list. Click and drag the New button and drop it on the Other Data option's drop-down control at the right side and change the name as Staff List. Add one more item similarly and change its name as Branches in the Name: control and select a button image from the Change Button Image option.

16. Add 3 Options on the Reports menu following the same procedure as explained above. Name the Options as Address Labels, New Employees List & Department Code List.

17. On the Process Menu add a single Custom control and name it as Process Report. Our Menu designing is complete. All that is left to do is to define some Action on each of these Buttons.

I have news for you, the difficult part is over. It was difficult because you are doing it for the first time. If you understood the overall concept then you can quickly assemble the menu within no time. Besides, if you design the menu to some extend as a standard one, keeping in mind your forthcoming Applications, then you can Import this Menu Bar into the New Application and easily customize it and redefine Actions on them.

Tip: You can redefine the Action of any built-in Menu Option as well.

We need two small Programs to run our Data Files and Reports Menu Options and a Macro to run the Process Report Option. VB Routines for Data Files & Reports are given below:

Public Function DataFiles(ByVal intOption As Integer)

Select Case intOption
    Case 1
        DoCmd.OpenForm "Address Book", acNormal
    Case 2
        DoCmd.OpenForm "Staff List", acNormal
    Case 3
        DoCmd.OpenForm "Department List", acNormal
End Select

End Function

Before going for the Menu let us look at the code above. When we call the Function DataFiles() we have to pass a number for the Menu Option in the Order in which we have placed them on the Menu. For Example: The Address Book Option is in the top of the list on the Data files Menu and we will pass 1 as parameter to the Function like =DataFiles(1) in the Action Control of the Menu Option. We test this value with the Select Case statement in the code and the action can be programmed underneath Case 1 statement to open the Form. For the Next Button number 2 as parameter and so on.

18. Let us go for the first button to define its action. Display the Toolbar Customize Control (Right-click on the Toolbar above and select Customize) if it is not already on. Click on the Data Files menu to display the drop-down list and right-click on the Address Book Option and select Properties. Type =DataFiles(1) in the Action Control. Repeat the process with successive numbers as Parameter to the function for other buttons keeping in mind that this number you are going to check in the program and sequence the action there.

19. Follow the same procedure for Reports Menu too. In the Reports() Function given below, under the Case 2 statement I have introduced a Macro running line before opening the Report in Print Preview assuming that a sequence of Action Queries that we have already organized in the Macro to run and create the Report Table before opening the Report that linked to it.

Public Function Reports(ByVal intOption As Integer)

Select Case intOption
    Case 1
        DoCmd.OpenReport "Address_Labels", acViewPreview
    Case 2
        If MsgBox("Re-process Report Data...?", vbYesNo +vbQuestion + vbDefaultButton2, "Reports()") = vbYes Then
              Docmd.RunMacro "ProcessData"
        End if
        DoCmd.OpenReport "New_Employees_List", acViewPreview
    Case 3
        DoCmd.OpenReport "Dept_Codes", acViewPreview
End Select
End Function

We have not forgotten our Process button in the middle. The word Process itself denotes that a lot of action goes into it. Since, the entire process running is mostly sequenced in a Macro the Action of this button is only to call this macro directly rather than call a function and check the number and run the macro etc. The result of the process may be displayed or printed later.

20. Assume that we have prepared a Macro with the name MyProcess and we will connect this macro to the Process Option. Display the Toolbars Customize control, if it is not already on, click on the Process menu and right-click on the Process Report Option and select Properties. In the Action control type MyProcess. Note, here we are not typing the = sign for the macro action.

I don't like this method at all. Assume that the User clicks on this button by mistake the macro runs and goes through with all the Action Queries put in there. A better method is passing the control into a Program as we have seen in the Reports() function under the Case 2 statement where we are asking the user whether to run the macro and re-create the Report Table or not. If she says no then macro is not touched the Report is open with earlier data and displayed.

Macro can be used for opening Forms and Reports. If the user opens it by mistake she can easily close it. Then, if you have several Forms, each form should have one macro and you need a lengthy list of macros in your macro Tab of your database.

Toolbars & Shortcut Menus.

Now, that you have become expert on creating Custom Menus, it is not different for creating Toolbars or Shortcut Menus. I don't want to talk at length on this subject and bore you to death. You can organize your Custom Toolbar with your own buttons and define action to it combined with most frequently used built-in toolbar buttons like Sort, Filter, Copy, Paste etc. for the User.

For the custom Shortcut Menu Bar create a Custom Toolbar and define it as a Shortcut Menu by selecting Popup in the Type control on the Toolbar Properties. Create only one Drop-down sub-Menu (by placing the New Menu option from the Categories List on the Customize control ) and add built-in menu options like Cut, Copy, Paste, etc. and add custom menu buttons to run your own programs like we did for the Calendar Control. When you change the Type of the Toolbar to Popup, Access will display a message saying that the Toolbar will be placed under the Custom Group in the built-in Shortcut Menu. Built-in Shortcut Menu Bar we have already seen in the Animated Floating Calendar topic.

Here, naturally a question arises, how we are going to use the Custom Shortcut Menu on our Forms? We have seen that when we right-click on a control on the Form the built-in Shortcut Menu will appear on which we have inserted the Calendar displaying button and used earlier.

Before going into that let us look into two basic concept of presenting the Application to the User. One method that we have already seen is, design a Control Screen and organize all our centrally controllable options like Opening the Data Files, Processing Data & Printing Reports etc. on the Screen and keep this screen open till the user shut downs the Application.

Another method is that design a Menu Structure as we did just now with Menu bars and Toolbars instead of a Control Screen. In that case the Application background will be always empty, except the Menus on the top, provided if we remove the check mark from the Display Database Window option in the Startup Setup. When a Screen or Report is open that object will occupy the empty area.

The Startup options on the Tools menu can be modified to disable all the built-in Menus to prevent them from appearing and prevent the user from modifying the Menus or Toolbars as well. An Image of the Application Startup Option Setting is given below:

On the Display Form/Page control we have inserted the Start_up Screen that we have designed earlier so that when the Application is open the Start-up screen will show up for a few seconds and disappear advertising that the Application is loaded and ready for use.

The Menu Bar and Shortcut Menu Bar controls on the Startup Setup control above are set with the Menu names that we have designed ensuring that the Custom Menu Bar is displayed in place of the normal built-in Menu Bar. When we right-click on a text control on a form our custom Shortcut Menu Bar will show up instead of the built in one.

There is one more setting for all the three menu types that we can update to ensure that our Menus and Toolbars are up all the time. On the Form & Report Properties, there are four Properties dedicated for the Menu Bar & Toolbars setup one each for Custom Menu Bar, Custom Toolbar & two for Custom Shortcut Menu where you can insert your Menu and Toolbar Names. Short Cut Menu have one more setting, which is to show or not to show the menu where Yes to show the menu and No to prevent the shortcut menu from appearing when right clicked on a Screen, like Control Screen, where you don't need a shortcut menu.

For Reports, a special Toolbar must be designed with printing specific built-in options like Page Setup, Print, Export to Word or Excel etc.

If several Forms and Reports are there in our Application then opening them one by one in design view and changing these Menu Bar/Toolbar Properties and saving them is a difficult task. Instead we can do this at design time of each object or write a program to do this job for us.

Design a small Form with a Command Button on it and change the Name property of the Command button as cmdMenus. Create a label on the Top of the Form with the Caption: Menu Bar/Toolbar Setup. Change the properties of the form to remove unwanted features like, Record Navigation Buttons, Record Selector, Scroll bars, Dividing Lines etc. Display the Code Module of the Form (select Code from View menu when the Form is open in design view). Copy and paste the following code into the Module of the Form:

Private Sub cmdMenus_Click()
Dim msg As String, resp As Integer, X As Integer

msg = "Menu Bar/Toolbar Setting " & vbCr & vbCr & "Changes on Forms/Reports" & vbCr & vbCr & "Proceed...?"
resp = MsgBox(msg, vbYesNo +vbQuestion + vbDefaultButton2, "cmdMenus_click")
If resp = vbYes Then
 X = MenuToolSet()
End If
End Sub

Private Function MenuToolSet()
Dim ctr As Container, doc As Document
Dim docName As String, cdb As Database

On Error GoTo MenuToolSet_Err

Set cdb = CurrentDbSet ctr = cdb.Containers("Forms&

' Set MenuBar, Toolbar properties change on FormsMsgBox "Form Toolbars Setup."

For Each doc In ctr.Documents
 docName = doc.Name
 If docName = "ToolbarSetup" Then ' Not on this Form
   GoTo nextform 
  DoCmd.OpenForm docName, acDesign, , , , acHidden
  With Forms(docName)
   .MenuBar = "MyMenuBar" ' change name, if different.
   .Toolbar = "AgrMainToolBar" ' change name, if different.
   .ShortcutMenu = True
   .ShortcutMenuBar = "AgrShortCut" ' change if different.
  End With
  DoCmd.Close acForm, docName, acSaveYes
End If

'MenuBar toolbar properties change on ReportsMsgbox "Menu Bar/Toolbar Setup on Reports."

Set ctr = cdb.Containers("Reports")

For Each doc In ctr.Documents
 docName = doc.Name
 DoCmd.OpenReport docName, acViewDesign Reports(docName)
.MenuBar = "MyMenuBar"
 Reports(docName).Toolbar = "AgrmReport"
 DoCmd.Close acReport, docName, acSaveYes

Msgbox ("Menubars/Toobars : On Forms & Reports" & vbCr & vbCr & "Setup Finished.")
  Set ctr = NothingSet cdb = Nothing

Exit Function

MsgBox Err.Description
Resume MenuToolSet_Exit
End Function

Save the Form with the name ToolbarSetup. Open and Click the command button on the Form to run the program. Each form will open in design view, in hidden mode, and changes the Toolbar properties and saves them automatically. In the case of Reports there is no hidden mode for design view. So we can see the Reports are opening and closing very fast after changing the properties by the above program.


Custom Menus and Tool Bars

We already had a formal introduction to Custom Tool Bar, Submenus & Shortcut Menus through the earlier posts: Animated Floating Calendar & Calendar and Toolbars. We have seen how to reference individual Button to enable/disable it based on situations that demands it.

Here, we will take a closer look at Custom Menu bars, Toolbars & Shortcut Menus. Since, it is a lengthy subject we are forced to split this into 2 or 3 Posts.


Windows based Applications have a Menu bar at the top with titles like File, Edit, View, Insert etc. When we click on them a long list of Options like Sub-menus, Items with Description alone or with Button Images and Description appears in a drop down list. When we click on any of them the designated action defined on the menu option runs and we will get our work done. We can create a Custom Menu bar with our Application specific Menu Descriptions like Data Files to open Forms linked to Tables/Queries, Reports to preview and print Reports, Utilities for other common tasks etc., add menu items into them and assign Actions to each of them. Initially we will concentrate on designing the Menu Bar alone. When this is understood the task of designing Toolbars and Shortcut Menus become easier, because the approach is same and you will be able to do it yourself with little guidance.

  1. Select Customize command from Toolbars submenu of View Menu.
  2. Select New from the Toolbars Tab.
  3. Type MyMenuBar in Toolbar Name: control and click OK. The new Toolbar Item is added in the Toolbars List and a small empty Toolbar appears in the Application surface, not necessarily at the top.

    You may be wondering that we have started discussing Menu bars then why we have to deal with Toolbars. We need to create a Toolbar first and then define it as a Menu bar, that's how it goes. For Shortcut Menu, create a Toolbar and define it as a Popup Menu (Shortcut Menu). So, the starting point is Toolbar. The Toolbar's name alone will not change its class, except referencing it in programs, and we must see that the new name is not clashing with any of the other Built-in Menus. Let us define our MyMenuBar Toolbar into a real Menu bar.

  4. Click on the MyMenuBar item in the Toolbars List in the Customize Dialogue Control, if it is not already selected.
  5. Click Properties
  6. The Selected Toolbar & Toolbar Name Controls already filled with the menu name. In the Type Property drop down list we can see three options: Menu Bar, Toolbar & Popup(Shortcut Menu). To define our Toolbar into a Menu Bar select Menu Bar from the list.

    Leave the other properties as it is. While installing your Application for the Users you can change these properties to determine how to behave the Menu Bar. Click Close to come out of the Toolbar Properties Sheet. It is likely that the small empty toolbar that we have seen earlier while creating it, is now disappeared and wondering where it went. To find out remove the check mark on MyMenuBar item in the Toolbars Tab and put the check mark back again and repeat this process a few times in quick succession and you can see an empty Menu bar is playing hide and seek on the top. Once we have spotted our celebrity we can continue our work.

    First we must create some meaningful menu headings with dropdown facility where we can add our Menu Options. Click on the Commands Tab of the Customize Control. Here, we have two set of List: Categories & Commands. In the Categories List the File item is already selected, if it is not select it now. In the Commands List we will be using the first two items: Custom & New for building our Menu Options. The Custom item is used for creating a single menu item with description only and New is used for creating single option with a Button Image and Description, which can be displayed with Image only, Text only or both. But both of these items will not work for a Drop-down List, which we see normally when we click on File, Edit, or View Menus. We have to use another control for that.

    Move the Scrollbar down in the Categories List and you will find an item called New Menu at the bottom of the list, select it. After selecting it, in the Commands List at the right side you will find a New Menu option. This Menu is a drop-down list like control. We need all these three options to build our Custom Menu Bar.

  7. Click and drag the New Menu item from the Commands window and drop it on our new empty Menu Bar. After that click on it to display a small empty dropdown control where we will be adding our menu options.
  8. Right click on the New Menu to display the options to change its Name. Type &Main; (we need a different name in place of File we see everywhere) in place of New Menu in the Name: control and press Enter to close. The & (ampersand) symbol at the left side of M character of the word Main enables us to select the Main Menu by using Alt+M Keyboard shortcut instead of clicking. You can put the & symbol left side of any character in the name to use that character as a keyboard shortcut. The character that you have chosen will appear with an underline in the Menu Name. If you look at the Built-in Menu descriptions at the top you can see one character in each Menu Name is underlined. But you must be very careful while selecting the character and see that you are not setting up a clash with the built-in keyboard shortcuts.

    For example: Assume that you have given & symbol at the left side of D in Data Files Menu and you are in the database window and the Forms Tab or Report Tab is active, if you execute the Alt+D keyboard shortcut, the selected Form, Report or whatever object currently have the focus on will open up in Design View. For safer side we will not give ampersand symbol in any of the other menu options for now.

    We will create three more Menu headings: Data Files, Process, Reports, Utilities (another name for Tools). Follow the same steps explained above under 8 & 9 and create the Menu Headings.

    We will mainly concentrate on Data Files, Process and Reports options for our own program/macro driven methods and in Main & Utilities Menus we will copy and add some essential built-in Options.

    We will do the easier part first, adding built-in options into the Main & Utilities menu. For easy handling of our MyMenuBar we need to push it up above the Built-in Menubar so that we can easily copy and drop (I didn't say drag and drop and you guessed it right) the Submenus or Options into our Menu. If you are not already in Customize option select Customize from Toolbars Submenu from View Menu. We will introduce four sample options: Close, Page Setup, Print and Exit into our Main menu from the Built-in File Menu.

    There are too many references to use for creating Custom Menus/Toolbars. First time anything is difficult or may find too cumbersome, but further on you will find them easy and rewarding too.

  9. Click on the built-in File menu to display the drop-down list. Click and hold the mouse button on the Close menu item and press Ctrl key with the other hand. When you press the Ctrl key, a plus (+) symbol will appear next to the selection indicating that a copy of the selected option is going to be made rather than drag the option out and leave that area empty for the built-in File Menu. Now, drag the mouse up and point to the Main item of our Menu Bar to display the empty dropdown control. Drop the Close option there.
  10. Follow the same procedure for Page Setup, Print & Exit options to make a copy of each and place it one below the other, or in between wherever you prefer.

    After dropping the menu items, if you prefer to define the option as part of a Group (for example: Page Setup & Print can be members of a common group) then right click only on the top item within the group and select Begin a Group from the displayed options menu. A horizontal line will appear above the selected option.

    NB: Any changes that you have made in any of the Built-in MenuBars (for example: If you have dragged the Print option out of the File Menu rather than copying an instance of it, it will not be available in File Menu), Toolbars or Shortcut Menus can be restored to its original state. Select Menu Bars in the Toolbars Tab of the customize control and click the Reset Button.

  11. Follow the same procedure to introduce two options from the built-in Tools Menu into Utilities Menu: Spelling, User and Group Accounts from Security Submenu. The User and Group Accounts option is used by Users for changing their Login Password in a secured Application.

We will continue our discussion shortly on Data Files, Process & Reports Menu options where we plan to introduce our own simple Programs & and Macros to open Forms, Reports etc.


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:

  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.

    • 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
          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    
      '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
      Exit Function
      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()
    End Sub
    Private Sub cmdCal2_Click()
    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 them with me.


    Animated Floating Calendar

    Calendar (the ActiveX Control) is a good piece of object for clicking and inserting a Date into a field instead of typing. There is only one problem, let us say more than one, it occupies lot of space on the form and it comes in the way of designing other elements of the screen as well. If you need to enter date in more than one field, each field requires different Calendar Controls and we may not be able to accommodate all those copies in one place. Finally, a convenient method become totally inconvenient and we may have second thoughts and decide to go by the traditional method: typing everything manually, after all somebody else is going to do that.

    We are going to use the Calendar Control and we don't accept defeats and all those above reasons will not prevent us from using it. The method that we are going to try out here is kind of hard to implement for the first time. But it will be very easy on other Forms in the same Project.

    Open your Database with a Form that you have already designed to use or design a new one with at least two date fields on the Form. Let us do the easy part of our project first. Copy and paste the following VB Code into a Global Module and save it:

    Option Compare DatabaseOption Explicit
    'Global Declarations
    Public Const twips As Long = 1440
    Dim mm_actctl As Control
    Public Function Calendar()
    Dim sngStart As Single, CalCtrl As Control
    Dim ctl As Control, frm As Form, t_height As Long
    Dim m_left As Long, m_top As Long, i As Double
    Dim w As Long, h As Long, y As Double
    Dim caltop As Long, calheight As Long
    Dim secHeight As Long
    Dim frmWidth As Long, t_width As Long
    On Error GoTo Calendar_Err
    Set ctl = Screen.ActiveControl
    Set mm_actctl = ctl
    Set CalCtrl = Screen.ActiveForm.Controls("Cal1")
    Set frm = Screen.ActiveForm
    CalCtrl.Width = 0.1458 * twips ' 0.1458"
    CalCtrl.Height = 0.1563 * twips ' 0.1563"
    m_left = ctl.Left + ctl.Width
    m_top = ctl.Top + ctl.Height
    caltop = m_top
    calheight = ctl.Height + (15 * twips * 0.106) '0.105"
    secHeight = frm.Section(acDetail).Height
    frmWidth = frm.Width
    t_height = caltop + calheight
    t_width = m_left + (15 * twips * 0.17) '0.17"
    If t_height > secHeight Then
        m_top = secHeight - (calheight + (0.106 * twips))
    End If
    If t_width > frmWidth Then
       m_left = frmWidth - (15 * twips * 0.17) ' 0.17"
    End If
    CalCtrl.Left = m_left
    CalCtrl.Top = m_top
    CalCtrl.Visible = True
    sngStart = Timer
    i = 0.05: y = i
    Do While Timer < (sngStart + 0.75)
    If Timer >= sngStart + y Then
        y = y + i
        w = CalCtrl.Width + (0.17 * twips) ' 0.17"
        CalCtrl.Width = w
        h = CalCtrl.Height + (0.106 * twips) ' 0.105"
        CalCtrl.Height = h
    End If
    Exit Function
    MsgBox Err.Description, , "Calendar"
    Resume Calendar_Exit
    End Function
    'Insert Date into the active Field
    Public Function Cal1Click()
    Dim m_cal As Control, m_ctl As Control
    On Error GoTo Cal1Click_Err
    Set m_cal = Screen.ActiveForm.Controls("Cal1")
    mm_actctl.Value = m_cal.Value
    m_cal.Width = 0.1458 * twips ' 0.1458"
    m_cal.Height = 0.1563 * twips ' 0.1563"
    m_cal.Visible = False
    Exit Function
    MsgBox Err.Description, , "Cal1Click"
    Resume Cal1Click_Exit
    End Function

    NB: While running this code if you end up with some error, please refer my earlier Post Command Button Animation and link the essential Library Files to your Project as explained there.

    1. Open the Form in Design View.
    2. Select ActiveX Control from Insert Menu.
    3. Scroll through the Displayed List and find Calendar Control as shown in the image below:
    4. Select it and click OK. A Calendar Control is inserted into your Form.
    5. Drag it anywhere at a convenient place for the time being. We are going to change its properties. Click on it and display its property sheet and change the following property values as shown below:
    1. Name = Cal1
    2. Visible = False
    3. Special Effect = Raised
    4. Border Color = 0
    5. Back Color = 11139322
    6. Month Length = System (Medium) - Access 2003, e.g.: Jul 2007
    7. Grid Lines Color = 2147483632
    8. Grid Font Color = 10485760
    9. Title Font Color = 10485760
    10. Width = .1458"
    11. Height = .1563"

    After changing the width & height properties the Calendar become a small rectangle and you can place it anywhere on the Form at a convenient place.

    We need to copy a few more lines of Code in our Form's Code Module. When the Form is in
    Design View Select Code from Toolbar Button above or select Code from View menu and paste the following code into the Form Module:

    Private Sub Cal1_Click()
    End Sub
    Private Sub Detail_Click()
       Me.Cal1.Visible = False
    End Sub
    Private Sub Form_Load()
       Me.Cal1.Value = Date
    End Sub

    When you click on a Date on the Calendar it calls the Routine Cal1Click() and inserts the clicked date into the Active Field and the Calendar disappears.

    You have displayed the Calendar but you don't want to click on it to insert a date and at this point you want to hide the calendar, then click on an empty area of the detail section of the Form, the Calendar will disappear. This is achieved through the Detail_Click() Routine.

    When the Form with the Calendar is open the System Date is set as default value for the Calendar with the Form_Load() Event Procedure. The Calendar will always open up with current date.

    If you are new to customizing Menus and Toolbars you may be little confused on the next part of this article. Don't worry, we are going to familiarize something that is already there for us to use and we must know about it sooner or later, earlier the better.

    In short, we are going to create a new toolbar button and attach our program, that we have copied into the Global Module, to it. We will place a copy of the toolbar button in a Shortcut Menu as well, for easy running of the Animated Floating Calendar.

    Select the following Menu Option:

    1. View - - > Toolbar - - > Customize. Click New to create a new Toolbar and name it as ToolCal then click OK. A new small empty Toolbar will show up on top.
    2. Select the Commands Tab . Click and drag the New button and place it on the new toolbar.
    3. Right-click on the new toolbar button, point the cursor on the Change Button Image option on the displayed menu to show up several Button Images. Select one of the Images.

      If you prefer to create a button image yourself you can do so by selecting Edit Button Image option from the menu. I have selected the image of a fish, after all it is a Floating Calendar. Right-click the button again to display the menu and type &Calendar; in the Name: &Name; control. We can set the button style to Image only, Text only or Image and Text. Default style, which is already selected is just fine for now.

    4. Now, we must link our Calendar() program to the toolbar button. Right-Click on the toolbar button and select Properties from the displayed Menu. Type =Calendar() (don't forget the = sign) in the On Action : text control and click Close.

      Our Toolbar Button is ready. We can use this Button to run our Calendar on the Form and we can stop here, if we need to. But we plan to go little further and add a copy of this button in the Form View Control Shortcut Menu so that we can right-click on a Date Field on our target Form and click the button on the Shortcut Menu to display the Calendar. We are trying to make things little easier for the Users. The Calendar is not visible till you click on the button.

    5. Select the Toolbars Tab on the Customize Dialogue Control and put tick mark for Shortcut Menus.

      Before attempting to copy the button let us locate the target spot, where we are going to place our new button. On the Shortcut Menus Options you will find the heading Form, click on it to display a lengthy list of Submenu groups, among them find the one Named 'Form View Control' and click on it. A set of Options like Filter by Selection, Cut, Copy, Paste etc. can be seen. This is the default shortcut menu that we see when we right-click on a Text Control in Form View. Look at the image given below:

      The shortcut menus are different when you right-click on an empty area of the Form or on the record selector at the left border etc. So it is important that you make a copy of our new button on this particular Shortcut menu. There are some side effects to this method that we are going to use, like if you click on the new toolbar button when there is a Form open without a Calendar Control on it or the Calendar control's name is not Cal1 etc., the program will show Error Messages. We will look into those things when we discuss about Custom Menu-bars, Tool-bars, Shortcut Menus and how to use them on Forms etc. under a different topic.

    6. Now, let us get on with our project. Press and hold Ctrl Key with one hand, click on the new toolbar button and keep the mouse button pressed with the other hand to make a copy of the button (if you don't keep the Ctrl Key pressed the button will be draged out of the Toolbar, rather than making a copy), drag and point on the Form Menu on the Shortcut Menubar to display the Submenu groups and drag towards the Form View Control Sub-Menu and point on it to display the Shortcut Menu options. Drop the button at the left border of the menu at a convenient location where other icons are appearing. Click the Close button to come out of the customization process.

    We are ready to try out the Animated Floating Calendar. Open your Form in Form View and right-click on a Date Field. The new button on the Shortcut Menu with the Fish Icon and Calendar Caption should be visible as shown below:

    Click the Calendar Button on the shortcut menu. The Calendar control will slowly unfold with yellow background. It will appear to the right and below the Date Field that you have right-clicked. Not necessary that it should be a Date Field, you can right-click on any field and run the Calendar.

    If we need to restrict inserting date into date type Fields only from the calendar then we have to validate the control source of the active field from the underlying Query or Table's Field Type and decide whether to show the calendar or display a warning message and block the Calendar from showing up etc. That kind of refining can be done but it takes lot more code to implement. For the time being we will stick to the simple thing and look into those aspects at a later stage.

    Click on a Date on the Calendar to insert that date into the field that you have right-clicked. If you don't want to insert a date and put away the Calendar instead, as I mentioned above, click on an empty area on the detail section of the Form, the Calendar will disappear.

    Normally the Calendar will appear to the right and bottom edge of the control that you have right-clicked. This may change if the Date Field is too close to the bottom or right edge of the Form. Then the Calendar will appear within the Form itself in an appropriate place nearest to the field, to fit its size. This may overlap the field that you have right-clicked. But you can click on the Calendar and insert a date into that field.

    NB: The Program may not work correctly if you attempt to use the method on a Sub-form.

    Download Demo Database



    Your email address:

    Delivered by FeedBurner


    Infolinks Text Ads

    Blogs Directory

    Popular Posts

    Search This Blog

    Blog Archive

    Powered by Blogger.


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

    Featured Post

    Function Parameter Array Passing

    Last week we have explored the usage of ByVal (By Value) and ByRef (By Reference),  in the Function Parameter, to pass the value from  a Va...


    Blog Archive

    Recent Posts