Custom Menus and Tool Bars2

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 the View Menu. Or you can right-click anywhere on the Menu Bar or Toolbar above and select Customize from the displayed Shortcut Menu.

You can also add built-in menu options directly from the Customize dialog, without copying or dragging them from existing menus, as we did in Step 11 of the earlier post. Click on the Commands tab in the Customize window. When you select a category from the list on the left, its corresponding built-in commands will appear in the list on the right. From there, you can simply click and drag any option onto your custom menu bar or toolbar—no need to press the Ctrl key. This makes it easy to organize frequently used built-in options for your users.

Custom Menu Options

12. Next, we’ll focus on creating completely independent menu options: Data Files, Process, and Reports. We’ll design custom programs or macros to define the actions for each of these menu items. Below is a sample image of the Data Files options I’ve added to the menu:

When you click on any option in the Data Files menu, it opens a corresponding form. To build these menu options, I’ve used three types of command items from the Menu Bar/Toolbar Customize dialog:

• Custom – for a single menu item with text only,

• New Menu – to create a submenu with a dropdown list, and

• New – for button-style options, with or without an icon.

13. Select Files from the Categories list in the Customize dialog. Then, click and drag the Custom control from the Commands list and drop it into the Data Files menu’s dropdown area. Right-click on the newly added item, select Name, and type Address Book in the textbox.

NB: The name you enter in the Name: field is used to reference the menu item in your code, so it must not conflict with the name of any built-in menu. If a duplicate name is used, Microsoft Access will display a warning message. By default, the same text entered in the Name: field is also used as the menu caption (i.e., the label visible to the user).

For example, suppose we've named our menu item DataFile (without spaces) so it can be easily referenced in code. However, we want the user to see a more descriptive label, such as Add/Edit Data. To do this, right-click on the DataFile menu item, select Properties from the context menu, and enter 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. Scroll to the bottom of the Categories list and select New Menu. This will display the New Menu control in the Commands list. Click and drag it onto your menu, then rename it to Other Data. This sub-menu includes a drop-down area on the right, where we can add several New control buttons.

15. Click on the Files category in the Categories list (within the Customize dialog). The Custom and New options will appear in the Commands list. Click and drag the New button, drop it into the drop-down area of the Other Data sub-menu, and rename it to Staff List. Similarly, add another item and rename it to Branches in the Name field. Then, choose a button icon using the Change Button Image option.

16. Add three options to the Reports menu using the same steps as described earlier. Name the options as Address Labels, New Employees List, and Department Code List.

17. On the Process menu, add a single Custom control and name it Process Report. With that, our menu design is complete. All that remains is to define actions for each of the buttons.

The good news is that the hard part is behind you! It felt challenging only because this was your first time. Once you grasp the overall concept, you'll be able to build menus quickly and confidently. Moreover, if you design the menu with some level of standardization—keeping your future applications in mind—you can import this Menu Bar into new projects and easily customize it by redefining the associated actions.

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 handle the Data Files and Reports menu options, along with a macro to execute the Process Report option. The VBA routines for the Data Files and Reports options are provided 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 we begin assigning actions to the menu options, let’s understand how the above code works. When we call the DataFiles() function, we must pass a numeric parameter corresponding to the position of the menu item. For example, if Address Book is the first item in the Data Files menu, we pass 1 as the parameter—like this: =DataFiles(1) In the Action property of that menu option.

Inside the function, this value is evaluated using a Select Case statement. Based on the case number (e.g., Case 1) we define the specific action to take, such as opening a form. Similarly, the next item on the menu will be used 2 as its parameter, and so on.

Defining Actions for Menu Items.

18. Let’s define the action for the first button on the Data Files menu. If the Customize Toolbar control is not already visible, right-click on the toolbar area and select Customize.

Next, click on the Data Files menu to open the drop-down list. Right-click on the Address Book option and choose Properties. In the Action field, enter:

=DataFiles(1)

This tells Access to call the DataFiles() function with the parameter 1. Repeat this process for the remaining buttons, incrementing the parameter each time (2, 3, etc.). Make sure to keep track of the order, as this number will be evaluated in the code using a Select Case block to determine which action to perform.

19. Follow the same procedure for assigning actions to the Reports menu options. In the Reports() function (given below), notice that under the Case 2 statement, I’ve included a line to run a macro before opening the report in Print Preview mode.

This assumes that you've already organized a series of Action Queries within the macro to generate the data table required by the report. The macro is executed first to prepare the data, and only then is the report linked to the resulting table opened for preview.

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 haven't forgotten our Process button in the middle. The word Process itself suggests that it involves multiple actions. Since the entire sequence is already handled within a macro, the action for this button is straightforward: just call the macro directly. There's no need to route it through a function or check option numbers. The output of the process—whether it's a message, a display, or a printout—can follow afterwards as needed.

20. Assume that we have created a macro named MyProcess and we want to link it to the Process Report menu option. To do this, display the Toolbars Customize control if it's not already open. Click on the Process menu, then right-click on the Process Report option and choose Properties. In the Action box, type MyProcess.

Note: When assigning a macro to a menu option, do not include the = sign—simply type the macro name.

I don't prefer assigning a macro directly to a menu option. If the user accidentally clicks the button, the macro will execute immediately and run all the embedded Action Queries, potentially altering or recreating important data without confirmation.

A better and safer method is to pass control to a VBA program, as demonstrated in the Reports() function under Case 2. There, we first prompt the user to confirm whether they want to run the macro to recreate the Report Table. If the user selects No, the macro remains untouched, and the Report simply opens using the previously generated data.

This approach not only prevents unintended data manipulation but also provides the user with control over when to refresh the report data.

Although macros can be used to open Forms and Reports, they are not ideal in every scenario. If a user accidentally opens a Form or Report through a macro, it can be easily closed, so the risk is minimal. However, if you have multiple forms, assigning a separate macro to each one means you'll end up with a long, cluttered list of macros under the Macros tab in your database. This not only makes management more difficult but also reduces the overall maintainability of your application.

Toolbars & Shortcut Menus.

Now that you've mastered the art of creating Custom Menus, you'll find that building Toolbars or Shortcut Menus is no different. I won’t go on and on about it and risk boring you to death! Just know that you can easily organize your own Custom Toolbar—adding your personalized buttons along with frequently used built-in ones like Sort, Filter, Copy, Paste, and more. Define the actions as you did with the menu items, and you’ll have a streamlined interface tailored to your needs.

To create a custom Shortcut Menu Bar, start by creating a Custom Toolbar and setting its Type to Popup in the Toolbar Properties. This tells Access that the toolbar is intended to function as a shortcut (right-click) menu.

Next, add a single drop-down submenu by dragging the New Menu item from the Categories List on the Customize dialog box onto your toolbar. Inside this submenu, you can include frequently used built-in options like Cut, Copy, Paste, etc., along with your custom menu buttons that trigger your own programs—just like we did earlier with the Calendar Control.

When you change the toolbar type to Popup, Access will display a message informing you that this toolbar will now appear under the Custom group within the built-in Shortcut Menus. We’ve already seen how this works in the Animated Floating Calendar example.

Naturally, this raises the question: how do we use our custom Shortcut Menu on Forms? As we've seen earlier, when you right-click on a control in a Form, the built-in Shortcut Menu appears—where we previously inserted the Calendar display button and used it successfully.

Before we move on, let’s look at two basic approaches to presenting an application to the user. One method—which we have already explored—is to design a Control Screen (also known as a Switchboard or Dashboard). This screen acts as the central hub of the application, where all essential options like opening Data Files, processing data, and printing Reports are organized. The Control Screen remains open and accessible until the user shuts down the application.

The second method is to design a menu-driven interface, like the one we just created using Menu Bars and Toolbars, instead of a Control Screen. In this approach, the application's background remains empty, displaying only the menus at the top. This is particularly effective when you uncheck the "Display Database Window" option in the Startup settings. When a Form or Report is opened, it will occupy the otherwise blank area, providing a clean and focused user interface.

You can configure the Startup Options from the Tools menu to disable all built-in Menus and Toolbars, preventing them from appearing when the application opens. This also stops users from modifying your custom Menus or Toolbars. An example image of the Startup settings is shown below:

Click to Enlarge

In the Display Form/Page control, we have selected the Startup Screen designed earlier. When the application opens, this screen briefly appears for a few seconds—signaling to the user that the application has successfully loaded and is ready for use.

Menu Bar and Shortcut Menu Bar

The Menu Bar and Shortcut Menu Bar fields in the Startup Options are set to the names of the custom menus we created. This ensures that our custom Menu Bar replaces the default built-in menu at the top of the application. Similarly, when we right-click on a text control within a form, our custom Shortcut Menu Bar will appear instead of the standard built-in shortcut menu.

There is one more important setting that applies to all three types of menus—Menu Bar, Toolbar, and Shortcut Menu—to ensure they remain consistently available. On the Form and Report property sheets, you will find four specific properties related to menu and toolbar configuration:

• Menu Bar – Assign the name of your custom menu bar.

• Toolbar – Assign the name of your custom toolbar.

• Shortcut Menu Bar – Assign the name of your custom shortcut menu.

• Shortcut Menu – Set this to Yes to enable the shortcut menu, or No to suppress it.

For example, on screens like a Control Screen, where right-click functionality isn’t needed, you can set Shortcut Menu to No to prevent the shortcut menu from appearing.

For Reports, it’s recommended to design a dedicated custom toolbar that includes print-specific built-in options such as Page Setup, Print, and Export to Word or Excel, among others. This provides users with quick access to essential report-handling functions.

Form/Report MenuBar Toolbar Properties

If your application contains many Forms and Reports, manually opening each one in Design View to update the Menu Bar/Toolbar properties and saving them can be tedious and time-consuming. A better approach is to either set these properties during the design phase of each object or write a VBA routine to apply these settings programmatically.

Instructions: Designing the Setup Form

1. Create a New Form in Design View (unbound, no table/query).

2. Add a Label control at the top and set its Caption to:
   Menu Bar/Toolbar Setup

3. Add a Command Button to the form and set its Name property to:
   cmdMenus
   Optionally set the Caption to: Apply Menu Settings

4. Set the following Form Properties to clean up its appearance:

   • Record Selectors: No

   • Navigation Buttons: No

   • Scroll Bars: Neither

   • Dividing Lines: No

   • Auto Center: Yes

   • Border Style: Dialog (optional)

   • Pop Up: Yes (optional for a floating window)

   • Modal: Yes (optional for focus)

5. With the Form still open in Design View, go to View > Code or press F7 to open the Form's module.

---

Sample VBA Code to Paste in the Form’s Module

This code will loop through all Forms and Reports in your database and update their menu and toolbar properties.

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

Final Step: Save and Run the Setup Form

1. Save the Form with the name:
   ToolbarSetup

2. Open the Form normally (not in design view).

3. Click the Button labeled Apply Menu Settings (the one named cmdMenus).

---

What Happens When You Click the Button:

• All Forms in your database:

  • Will open in design view (in hidden mode),

  • Will have their Menu Bar, Toolbar, and Shortcut Menu properties updated automatically,

  • Will be saved and closed silently.

• All Reports in your database:

  • Will also open in design view, but not hidden (because Access doesn't support hidden design mode for Reports),

  • You'll briefly see each Report flash on the screen,

  • Each will have its properties updated, saved, and then closed instantly.

---

This automated approach is far quicker and more consistent than updating each object manually, especially in larger applications.

Read More

Custom Menus and Tool Bars

Introduction - Access 2003.

We were formally introduced to Custom Toolbars, Submenus, and Shortcut Menus in earlier posts, Animated Floating Calendar, and 'Calendar and Toolbars'. There, we also explored how to reference individual buttons to enable or disable them depending on the specific context or requirements.

In this post, we’ll take a closer look at Custom Menu Bars, Toolbars, and Shortcut Menus. Since this is a broad topic, we’ll be covering it across two or three separate posts.

MENU BARS.

Windows-based applications typically feature a menu bar at the top with titles like File, Edit, View, Insert, and so on. Clicking on any of these reveals a drop-down list of options—some with only descriptive text, others with icons and text. Selecting an item from the list triggers a specific action, helping you complete your task.

Similarly, in Microsoft Access, we can design custom menu bars tailored to our application, with menu titles like Data Files (to open forms linked to tables or queries), Reports (to preview or print reports), Utilities (for common tasks), and more. Menu items can be added under each heading and linked to corresponding actions.

For now, we’ll focus on designing the menu bar itself. Once you understand that process, creating toolbars and shortcut menus becomes much easier, as the approach is largely the same—you’ll just need a bit of guidance.

Customizing Toolbars.

1. From the View menu, go to Toolbars and select the Customize command.

2. On the Toolbars tab, click New.

3. In the Toolbar Name field, type MyMenuBar then click the Ok Button. The new toolbar name will appear in the toolbars list, and a small empty toolbar will be displayed somewhere on the application surface, not necessarily at the top.

At this point, you might wonder: We’re talking about Menu Bars, so why are we creating a Toolbar? In Microsoft Access, every custom menu bar or shortcut menu begins as a toolbar. Once created, you can redefine it as a Menu Bar or as a Popup Menu (i.e., Shortcut Menu). Simply naming a toolbar won't change its category—it must be explicitly defined. Also, make sure the new name doesn’t conflict with any built-in menus when referencing it in code.

Now, let’s convert our MyMenuBar toolbar into a real menu bar:

1. In the Customize dialog box, click on  MyMenuBar in the toolbars list (if it’s not already selected).

2. Click the Properties button.

3. In the Properties window, you’ll see MyMenuBar under Selected Toolbar and Toolbar Name.

4. In the Type dropdown list, select Menu Bar from the available options (Menu Bar, Toolbar, Popup).

Leave the other properties as they are for now. Later, while installing your application for users, you can customize these properties to control how the menu bar behaves. Once you're done, click Close to exit the Toolbar Properties dialog.

You may notice that the small, empty toolbar we saw earlier seems to have vanished. Don’t worry—it hasn’t gone far. To locate it, go back to the Toolbars tab, uncheck the MyMenuBar item, then check it again. Repeat this a few times quickly, and you’ll see the empty menu bar briefly appear and disappear—it’s playing a bit of hide-and-seek near the top of the application window. Once you’ve spotted it, we’re ready to continue building it.

To begin building your custom menu, you first need to create meaningful menu headings that can expand into drop-down lists, just like the standard File, Edit, or View menus.

1. Open the Customize dialog and click on the Commands tab.

2. You’ll see two lists: Categories on the left and Commands on the right.

3. In the Categories list, select File (if it’s not already selected).

4. From the Commands list, note the first two items: Custom and New.

   • Custom creates a menu item with just a text label—no image.

   • New creates an item that includes a button image and a label. You can configure it to show just the image, just the text, or both.

However, neither Custom nor New can be used to create a drop-down menu, like those you see under File or Edit. For that, we’ll need a different command control, which we’ll explore next.

Scroll down the Categories list until you find an item called New Menu near the bottom—select it. Once selected, the Commands list on the right will display an item also named New Menu. This is the control you need to create a drop-down-style menu, just like the ones under File or Edit in standard applications.

To build a complete Custom Menu Bar, you will use:

• Custom (for plain text menu items),

• New (for items with icons and text), and

• New Menu (for drop-down menu structures).

These three options are the basic building blocks of your custom menu bar.

Customizing Menu Options. 

1. Click and drag the New Menu item from the Commands list and drop it onto your newly created empty menu bar. This will add a drop-down menu placeholder. Click on it to display a small empty drop-down where we’ll add our custom menu options.

2. Now, right-click on the New Menu item to open its properties, and change its name. In the Name: field, type &Main (We are using a custom name instead of the usual "File") and press Enter to confirm.

3. The & (ampersand) symbol before a character—here before M—enables keyboard navigation: pressing Alt+M will open this menu. You can place the & before any letter in the name to assign it as a shortcut key. That character will appear underlined in the menu bar, similar to how built-in menu names have one underlined letter each.

4. However, be cautious when choosing shortcut letters. Avoid using characters already assigned to built-in menus to prevent conflicts with default 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.

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

   We will primarily focus on creating custom menu options for Data Files, Process, and Reports—each designed to run our own program or macro-driven methods. In the Main and Utilities menus, we’ll also include some essential built-in options by copying and adding them as needed.

   Let’s start with the easier task—adding built-in options to the Main and Utilities menus. To manage our MyMenuBar more conveniently, we’ll first push it above the built-in menu bar, making it easier to copy and drop (not drag and drop—yes, you guessed that right!) menu items or submenus into our custom menu.

   If you’re not already in Customize mode, go to the View menu, select Toolbars, and click Customize.

   As a demonstration, we’ll copy four built-in options—Close, Page Setup, Print, and Exit—from the built-in File menu and add them to our Main menu.

   There are many references and options available for creating Custom Menus and Toolbars. At first, the process may seem difficult or even a bit cumbersome, but as you gain experience, you'll find it much easier—and quite rewarding too.

   Drag-Drop Menu Items.

6. Click on the built-in File menu to display its drop-down list. Click and hold the mouse button on the Close menu item, and while doing so, press the Ctrl key with your other hand. You’ll notice a plus (+) symbol appear next to the item—this indicates that a copy of the selected option is being made, rather than removing it from the built-in File menu. Now, drag the item up to the Main menu of your custom Menu Bar to display its drop-down area, and then drop the Close option there.

7. Follow the same procedure to copy the Page Setup, Print, and Exit options, placing them one below the other—or in between existing items, depending on your preference.
   

If you'd like to group certain options (for example, Page Setup and Print as part of a common group), right-click on the top item of that group and select Begin a Group from the context menu. This will insert a horizontal line above the selected item, visually separating it from other menu options.

Note: If you accidentally modify a built-in Menu Bar—for example, by dragging the Print option out of the File menu instead of copying it—the item will be removed from the original menu. However, any changes made to built-in Menu Bars, Toolbars, or Shortcut Menus can be easily restored.

To reset them to their original state, open the Customize dialog, select Menu Bar from the Toolbars tab, and click the Reset button.

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.

Enable GingerCannot connect to Ginger Check your internet connection
or reload the browserDisable in this text fieldEditEdit in GingerEdit in Ginger×

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

Read More

Calendar and Toolbars

Introduction

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

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

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

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

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

Let us take a look at the above Questions, one by one.

The Custom Toolbar Button creation method was easy to explain without going too deep into the Shortcut Menus. Shortcut Menus are a pop-up class of Menus and are different from Toolbars. We will learn more about creating Custom Menus, Toolbars, and pop-up menus for our Applications later.

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

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

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

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

Importing the Custom Toolbar.

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

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

The Command Bar Collection.

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

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

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

1. Copy the following code and paste it into the same global module where you previously added the Calendar program routines. We can invoke this routine by inserting a single line of code at appropriate points within our forms.

   Private Sub EnableDisable(Byval intStatus as integer)
   Dim cbr1 As CommandBar, cbr2 as Commandbar
   
   DoCmd.ShowToolbar "ToolCal", acToolbarYes 
   
   Set cbr1 = CommandBars("ToolCal")
   Set cbr2 = CommandBars("Form View Control")
   Select Case intStatus
         Case 0
            cbr1.Enabled = False
            cbr2.Controls("Calendar").Enabled = False
         Case 1
            cbr1.Enabled = True
     cbr2.Controls("Calendar").Enabled = True
   End Select
   End Sub

   • The line DoCmd.ShowToolbar "ToolCal", acToolbarYes will display the custom toolbar if it is not already visible.

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

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

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

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

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

   Private Sub Form_Load()
      EnableDisable 0 ' 0 to disable and 1 to enable.
   End Sub

   Now we can easily provide our users the facility to use the Calendar without running into trouble and attend to his/her distress calls every now and then. All we have to do is introduce the following lines of code into the Form Module where we have installed our Calendar control:

   Private Sub Form_Load()
      EnableDisable 1
   End Sub
   
   Private Sub Form_UnLoad()
    EnableDisable 0
   End Sub

   Preventing Inadvertent Usage.

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

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

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

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

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

   Code to Validate Date Field

3. Copy the following code into the same Global Module that you have copied the Calendar() Program:
   

   Public Function Check4Date() As Boolean
   Dim ctl As Control, frm As Form
   Dim RecSource As String, ctlSource As String
   Dim dtFormat As String, ctlType As Integer
   Dim fldType As Integer, fldformat As String
   Dim ctlName As String, statuscode As Integer
   Dim tblDef As TableDef, qryDef As QueryDef
   Dim tblDefFlag As Boolean, cdb As Database
   
   On Error GoTo Check4Date_Err
   
   dtFormat = "dd/mm/yyyy"
   Set frm = Screen.ActiveForm
   Set ctl = Screen.ActiveControl
   ctlType = ctl.ControlType
   If ctlType = 109 Then 'not a Textbox Control, terminate the function
      Check4Date = False
      Exit Function
   End If
   
   ctlSource = ctl.ControlSource
   If Len(ctlSource) = 0 Then
       statuscode = 1 ' unbound textbox
   Else
       statuscode = 2 ' bound textbox
   End If
   
   fldformat = ctl.Format
   
   'bound/unbound if format is date then valid
   If fldformat = dtFormat Then
      Check4Date = True   
   Exit Function
   End If
   
   If statuscode = 2 Then
       RecSource = frm.RecordSource
       ctlName = ctl.Name
       Set cdb = CurrentDb
   
   'Check it is a Table or not
       tblDefFlag = False
       For Each tblDef In cdb.TableDefs
           If tblDef.Name = RecSource Then
               tblDefFlag = True
               Exit For   
           End If    
       Next
   
   'if it is table then check the field type    
   If tblDefFlag Then
           Set tblDef = cdb.TableDefs(RecSource)
           fldType = tblDef.Fields(ctlName).Type
              If fldType = 8 Then
                 Check4Date = True
                 Exit Function
               End If
   End If
   
   'it is not a table check in Query Definitions    
   Set qryDef = cdb.QueryDefs(RecSource)
       fldType = qryDef.Fields(ctlName).Type
           If fldType = 8 Then
               Check4Date = True
               Exit Function 
          End If
   End If
   
   Check4Date_Exit:
   Exit Function
   
   Check4Date_Err:
   MsgBox Err.Description, , "Check4Date_Err"
   Resume Check4Date_Exit
   End Function

4. Add the following code snippet at the beginning of the Calendar() Program immediately below the Dimension statements to run the Check4Date() routine and decide whether to show the Calendar or display a message that the active Control doesn't need the Calendar Control:

Dim fldstatus As Boolean
Fldstatus = false ' initialize variable
fldstatus = Check4Date() ' check the control type 
If fldstatus = False Then
  MsgBox "Sorry, Not a Date Type Control."  
Exit Function
End If

Avoiding Toolbars and Shortcut Menus

Without going through all the trouble of creating Custom Toolbars, Shortcut Menus, Validation checks, etc., you can use the Calendar on specific fields on the Form by doing some extra work on every form that we insert our Calendar Control. Look at the sample Screen given below:

Click to Enlarge

We have created two small command buttons—one each for the Visit Planned Date and Actual Visit Date fields—and assigned them with the Calendar icon from the Command Button Wizard’s image collection. Each button is resized and positioned near the right edge of its corresponding field.

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

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

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

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

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

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

Read More

Animated Floating Calendar

Introduction.

The Calendar ActiveX Control is a convenient tool for quickly inserting date values into fields with a simple click, eliminating the need for manual typing.

The only drawback of the Calendar ActiveX Control is its large size—it occupies considerable space on the form, especially when multiple date fields are involved. This can interfere with the placement of other controls and compromise the overall layout. As a result, despite its convenience, developers may hesitate to include it in their design and instead fall back on the traditional approach of manually typing the Date value—after all, it’s the user, not the developer, who will be doing the typing.

We’re going to use the Calendar Control—because we don’t accept defeat. The concerns mentioned earlier won’t stop us from using this powerful tool wisely. The method we’ll implement here may seem a bit complex at first, but once set up, it becomes very easy to reuse across other forms within the same project.

Microsoft Access Floating Calendar Project.

Open your database and select a form you've already designed, or create a new one with at least two Date Fields. Let’s begin with the easy part of our project. Copy and paste the following VBA code into a standard (global) module, then 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

Note: If you encounter any errors while running this code, please refer to my earlier post titled Command Button Animation. It explains how to link the necessary library references to your project—an essential step for this code to work correctly.

MS-Access Calendar Control.

1. Open the Form in Design View.
2. Select Microsoft Access ActiveX Control from the 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.
   

   Click to Enlarge

5. Temporarily position the control anywhere on the form where it's convenient.
   Next, click on the control to select it, open its Property Sheet, and update the following property values as indicated below:

Ms-Access Calendar Control Properties

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 adjusting the Width and Height properties, the Calendar control becomes a small rectangle that you can easily position anywhere on the form, wherever it's most convenient.

Next, we need to copy a few additional lines of code into the Form's code module. While the form is in Design View, either click the Code button on the toolbar or choose View → Code from the menu. Then, paste the following code into the form’s 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 in the Calendar, the Cal1Click() routine is triggered, inserting the selected date into the currently active field and then hiding the Calendar.

However, if you decide not to select a date and simply want to hide the Calendar, clicking anywhere on the empty area of the form’s Detail section will do so. This behavior is handled by the Detail_Click() routine.

Additionally, when the form opens, the Calendar is automatically initialized with the system's current date, thanks to the Form_Load() event procedure. This ensures the Calendar always starts with today’s date selected.

If you're new to customizing Microsoft Access menus and toolbars, the next section might seem a bit unfamiliar. But don’t worry—we're just going to explore features that already exist within Access. It’s something every developer eventually needs to learn, and getting familiar with it now will save you time later.

Creating MS Access Toolbar Button.

We'll now create a new toolbar button and link it to the procedure you previously copied into the global module. For added convenience, we'll also place a copy of this toolbar button in a shortcut menu. This setup allows you to easily launch the animated floating Calendar control directly from your MS Access form.

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.
   

   Click to Enlarge

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, and point the cursor on the Change Button Image option on the displayed menu to show several Button Images. You may select one of the Images you prefer to use.
   

   Click to Enlarge

   If you'd like to create a custom button image, you can do so by choosing the Edit Button Image option from the menu. In this example, I selected a fish image, fitting, since it's for a Floating Calendar. After selecting the image, right-click the button again to bring up the menu and enter &Calendar the Name field. You can choose the button style as Image only, Text only, or Image and Text. The default style (Image and Text) is suitable for now, so you can keep that selected.

4. Next, we need to link our Calendar() program to the toolbar button. Right-click on the toolbar button and select Properties from the context menu. In the On Action field, type:

   =Calendar()
   

   (Be sure to include the equal sign at the beginning.)

   Click Close to save the changes.

   Our toolbar button is now ready and can be used to launch the calendar on the form. If that’s all you need, you can stop here. However, we’ll take it a step further by adding a copy of this button to the form view’s shortcut menu. This will allow users to simply right-click on a date field and select the calendar option from the shortcut menu, making it even more convenient. The calendar remains hidden until the button is clicked.

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

   Before copying the button, let’s first locate the target area where we’ll place it. In the Shortcut Menus options, locate and expand the Form section. You’ll see a long list of sub-menu groups—find and select Form View Control. This group contains standard options like Filter by Selection, Cut, Copy, Paste, and more. These are the default items that appear when you right-click on a text control in Form View. Refer to the image below for guidance.

   

   Click to Enlarge

   The shortcut menus vary depending on where you right-click—whether on an empty area of the form, the record selector, or a control. Therefore, it's essential to place a copy of the new button specifically on the Form View Control shortcut menu, which appears when you right-click on a text control in Form View.

   Be aware that this method has a few side effects. For instance, if you click the new toolbar button on a form that doesn’t contain the Calendar control, or if the control isn't named Cal1 You may encounter error messages.

   We have more articles that explore this topic in greater detail, including how to design custom Microsoft Access menubars, toolbars, and shortcut menus—and how to use them effectively on forms. Relevant links are provided at the end of this page for your reference.

6. Now, let’s proceed with placing the button. Hold down the Ctrl key with one hand, then click and drag the new toolbar button with the mouse. (Make sure to keep the Ctrl key pressed—otherwise, the button will be moved rather than copied.)

   While dragging, hover over the Form menu in the Shortcut Menu Bar to reveal its submenu groups. Then move the pointer to Form View Control to display its shortcut menu options. Drop the button at the left edge of the menu, aligning it with the existing icons for a clean look.

   Finally, click Close to exit the customization mode.

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 from the shortcut menu. The ActiveX Calendar control will unfold smoothly with a yellow background, appearing just below and to the right of the field you right-clicked. While it's typically used with date fields, you can activate it from any field on the form.

If we want to restrict calendar-based date selection to only date-type fields, we’ll need to validate the Control Source of the active control against the field type defined in the underlying table or query. Based on that, we can decide whether to display the calendar or show a warning and prevent it from appearing. While this refinement is possible, it requires additional code. For now, we’ll keep things simple and revisit those enhancements later.

Click on a date in the Calendar to insert it into the field you previously right-clicked. If you decide not to insert a date and simply want to close the Calendar instead, just click on any empty area in the detail section of the form. The Calendar will then disappear.

Normally, the Calendar appears to the right and below the control you right-clicked. If the Date field is located near the bottom or right edge of the form, the Calendar automatically repositions itself to fit within the visible area, typically as close as possible to the field. In such cases, it may slightly overlap the field, but you can still click on the Calendar to insert a date without any issue.

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

Download

Download AnimatedCalender.zip

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

Read More