July 2007 ~ LEARN MS-ACCESS TIPS AND TRICKS

Custom Menus and Tool Bars2

a.p.r. pillaiJuly 27, 2007Menus and Toolbars, msaccess menus 3 comments

Continuation of Custom Menus and Toolbars - Access 2003.

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 the 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 the category list), the built-in commands pertaining to that Menu are 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.

Custom Menu Options

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 the only description, New Menu to create a Sub-Menu option with a drop-down control & New for Buttons with an icon or without it, from the Menu Bar/Toolbar Customize Control.

13. The Select Files in the Categories List of Customize Control, click and drag the Custom control from the Commands list and place it in 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 the Programs and should not be the same name as 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 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 has a drop-down control on the right side, where we will add a 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 design 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 extent 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.

Opening Data Files and Reports

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 to 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 at the top of the list on the Data files Menu and we will pass 1 as a 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 the Case 1 statement to open the Form. For the Next Button number 2 as a parameter and so on.

Defining Actions for Menu Items.

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 keep in mind that this number you are going to check with 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 the macro has not been touched, the Report is open with earlier data and displayed.

The 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 long list of macros in the macro Tab of your database.

Toolbars & Shortcut Menus.

Now, that you have become an expert on creating Custom Menus, it is not different from 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 the action to it combined with the most frequently used built-in toolbar buttons like Sort, Filter, Copy, Paste, etc.

For the custom, Shortcut Menu Bar creates a Custom Toolbar and defines 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 concepts of presenting the Application to the User. One method that we have already seen is, to 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 shutdowns 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 for the Menus at the top, provided if we remove the checkmark 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:

Click to Enlarge

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.

Menu Bar and Shortcut Menu Bar

The Menu Bar and Shortcut Menu Bar controls on the Startup Setup control above are set with the Menu names that we have designed to ensure 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 to 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 has 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.

Form/Report MenuBar Toolbar Properties

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 the 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 the 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&
quot;)

' 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 
Else
  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
nextform:
Next

'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
Next

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

MenuToolSet_Exit:
Exit Function

MenuToolSet_Err:
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 the design view, in hidden mode, changes the Toolbar properties, and saves them automatically. In the case of Reports, there is no hidden mode for the design view. So we can see the Reports are opening and closing very fast after changing the properties of the above program.

Custom Menus and Tool Bars

a.p.r. pillaiJuly 20, 2007Menus and Toolbars, msaccess menus 4 comments

Introduction - Access 2003.

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

MENU BARS.

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 becomes easier because the approach is the same and you will be able to do it yourself with little guidance.

Customizing Toolbars.

Select Customize command from Toolbars submenu of View Menu.
Select New from the Toolbars Tab.
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.
Click on the MyMenuBar item in the Toolbars List in the Customize Dialogue Control, if it is not already selected.
Click Properties
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 checkmark on the MyMenuBar item in the Toolbars Tab and put the checkmark 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 the drop-down facility where we can add our Menu Options. Click on the Commands Tab of the Customize Control. Here, we have two sets 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 a 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 on 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.
Customizing Menu Options.
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.
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 the M character of the word Main enables us to select the Main Menu by using the 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 the safer side, we will not give the 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 the 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. The first time anything is difficult or may find too cumbersome, but further on you will find them easy and rewarding too.
Drag-Drop Menu Items.
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 the 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.
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.
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

a.p.r. pillaiJuly 13, 2007Menus and Toolbars, msaccess animation 4 comments

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:

Advantages & disadvantages of Custom Toolbar Button & Shortcut Menu Button for displaying the Calendar Control and, when 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 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.

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

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.

Animated Floating Calendar

a.p.r. pillaiJuly 06, 2007Animations, Downloads, Forms, msaccess animation 3 comments

Introduction.

Calendar (the ActiveX Control) is a good object for clicking and inserting Date values into Fields quickly, instead of typing manually.

There is only one problem, it occupies a lot of space on the form when it is placed for more Date Fields and it comes in the way of placing other controls on the Form, In short, a very useful and convenient object cannot go into our design and we may have second thoughts and decide to go with the traditional method: typing everything manually, after all, somebody else is going to do that, not the developer.

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

Microsoft Access Floating Calendar 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 VBA 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
    DoEvents
End If
Loop

Calendar_Exit:
Exit Function

Calendar_Err:
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"
mm_actctl.SetFocus
DoEvents
m_cal.Visible = False

Cal1Click_Exit:
Exit Function

Cal1Click_Err:
MsgBox Err.Description, , "Cal1Click"
Resume Cal1Click_Exit
End Function

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

MS-Access Calendar Control

Open the Form in Design View.
Select Ms-Access ActiveX Control from Insert Menu.
Scroll through the Displayed List and find Calendar Control as shown in the image below:
Select it and click OK. A Calendar Control is inserted into your Form.

Click to Enlarge
Drag it anywhere at a convenient place for the time being. We are going to change their properties. Click on it and display its property sheet and change the following property values as shown below:

Ms-Access Calendar Control Properties

Name = Cal1
Visible = False
Special Effect = Raised
Border Color = 0
Back Color = 11139322
Month Length = System (Medium) - Access 2003, e.g.: Jul 2007
Grid Lines Color = 2147483632
Grid Font Color = 10485760
Title Font Color = 10485760
Width = .1458"
Height = .1563"

After changing the width & height properties the Calendar becomes 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()
   Cal1Click
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 the default value for the Calendar with the Form_Load() Event Procedure. The Calendar will always open up with the current date.

If you are new to customizing ms-Access Menus and Toolbars you may be a little confused about the next part of this article. Don't worry, we are going to familiarize something that is already there and we have to learn its usage now or later, the earlier the better.

Creating Ms-Access Toolbar Button

We will create a new toolbar button and attach the program, that you have copied earlier into the Global Module. We will place a copy of the toolbar button in a Shortcut Menu as well, for easier usage of Animated Floating Calendar, on MS-Access Form.

Select the following Menu Option:

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.

Click to Enlarge
Select the Commands Tab. Click and drag the New button and place it on the new Toolbar.
Right-click on the new toolbar button, and point the cursor on the Change Button Image option on the displayed menu to show up several Button Images. You may select one of the Images you prefer to use.

Click to Enlarge
If you would like to create a button image yourself, you can do so by selecting the 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. The default style, which is already selected is just fine for now.
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 a 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 MS-Access Form and click the button on the Shortcut Menu to display the Calendar. We are trying to make things a little easier for the Users. The Calendar is not visible till you click on the button.
Select the Toolbars Tab on the Customize Dialogue Control and put a 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 Sub-menu 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, on Form View. Look at the image given below:

Click to Enlarge
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, like when you click on the new toolbar button, on a Form without the Calendar Control on it or the Calendar control's name is not Cal1, etc., the program will show Error Messages. We have more Articles on this topic, which explore in detail, designing Microsoft Access Custom Menu-bars, Toolbars, Shortcut Menus, and how to use them on Forms and their links are given at the end of this page for your reference.
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 dragged out of the Toolbar, rather than making a copy), drag and point on the Form Menu on the Shortcut Menu-bar 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 appear. Click the Close button to come out of the customization process.

Trial Run

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

Click the Calendar Button on the shortcut menu. The ActiveX Calendar control will slowly unfold with the 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 dates 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. That kind of refining can be done, but it takes a 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.