Improving the Usability of the Calendar Control in Microsoft Access

  • Article

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

 

Frank C. Rice
Microsoft Corporation

September 2002

Applies to:
   Microsoft® Access

Summary: The Calendar ActiveX® control is very handy when working with dates in a Microsoft Access form or data access page. In this article you will discover how to enhance the existing features of the Calendar control with just a few lines of code.

Contents

Introduction
Changing Dates with the Calendar Control
Selecting a Range of Dates in the Calendar Control
Conclusion

Introduction

One of the ActiveX controls included with Microsoft Access is the Calendar control. The Calendar control can be embedded in an Access object, such as a form or data access page, or used as a pop-up control. The Calendar control can improve usability when working with dates by providing a graphical representation of a calendar. In addition, using the control will help make your forms more professional looking.

With just a few lines of code, you can enhance the existing capabilities of the Calendar control. For example, let's say that you have a form with two text boxes; one that is bound to a field in a table and displays a start date from the Calendar control, and another text box bound to a different field that displays an end date from the Calendar control. When you move from one date selection to the other date in the Calendar control, the first date is not automatically saved to the field (specified in the text box's ControlSource property) that stores data for the control. Additionally, you cannot select a series of dates with the Calendar control—only one date can be selected.

In this article, we will look at how we can integrate these features into the control with just a few lines of Microsoft Visual Basic® for Applications (VBA) code.

Changing Dates with the Calendar Control

Typically, when you use a Calendar control on a form or other object in an Access database, a selection in the control represents a date stored in an Access data table. The saved date is then available for other uses in your database such as in a query or in another form or page. You specify which field in the table to bind the control to by using the control's ControlSource property. Setting the ControlSource property (available from the control's property sheet) of the control to a field binds the control to that field.

If you click on a date in the control, and then click on another date, you might expect the date in the bound field to change to reflect the new value. However, this isn't the case. Instead, you either have to click into another control on the form or save the current record displayed on the form in order for the date change to be reflected in the field. The good news is that it is relatively easy to overcome this limitation with a few lines of VBA code.

Let's see how you can do this with an example. First, create a new table and add one field. Then, create a form (see Figure 1) based on this table, and add a text box and Calendar control to the form. And finally, add code to the Calendar control. Here's how to do it:

  1. Start Access. Open an existing database or create a new database by clicking New on the File menu.

  2. Open a new table in Design view.

  3. Add one field, formatted as a Date/Time value, to the table.

  4. Name the field Current_Date, and then close and save the table as Calendar_Table. Click Yes to add a primary key if a message appears asking you to do so.

  5. In the Database window, in the Objects list, click Forms.

  6. Click the New button on the Database window toolbar.

  7. In the New Form dialog box, click Design View.

  8. Click the arrow by the Choose the table or query where the object's data comes from list, click Calendar_Table, and then click OK.

  9. In the Field List dialog box (on the View menu, click Field List, if the dialog box isn't already displayed), drag the Current_Date field toward the bottom of the form. The text box is bound to the field in the table so any changes to the value in the field will be reflected in the text box.

  10. Click the More Controls button in the Toolbox (on the View menu, click Toolbox, if the Toolbox isn't visible), click Calendar Control 10.0, and then click on the form to place the control. Position the control above the text box.

  11. Right-click the Calendar control, and then click Properties.

  12. Click the All tab. In the Name box, type calMyCalendar.

  13. Click the ControlSource box, click the arrow in the box, and then click Current_Date. Close the property sheet. This binds the Calendar control to the field in the table so that when you click a date in the control, that value will be saved to the field in the table.

  14. On the File menu, click Save. In the Save As dialog box, type Calendar_Form, and then click OK.

  15. Right-click the Calendar control, and then click Build Event.

  16. In the Choose Builder dialog box, click Code Builder, and then click OK.

  17. In the code window, click the arrow by the Procedure list (upper right-side of the window), and then click the Click event.

  18. Insert the following code between Private Sub calMyCalendar_Click and End Sub and then close the Visual Basic Editor:

    If Me.Dirty = True Then
    Me.Dirty = False
End If

  19. Open the form in Form view (see Figure 1), and then click on different dates in the Calendar control. Notice that the date displayed in the text box changes with each click.

Aa139958.odc_acdates01(en-us,office.10).gif

Figure 1. Form with Calendar control displaying the current date.

The Dirty property is used to determine whether a record has been changed since it was last saved. Each time you change the date in the Calendar control, the Dirty property is set to True. The next time you click the control, the OnClick event for the control is executed, which sets the Dirty property to False. Whenever the Dirty property is set to False, the current record (and the value of the control) is saved to the table. Because the text box on the form is also bound to the field in our table, a change in the value of the field is also reflected in the text box.

Selecting a Range of Dates in the Calendar Control

Another shortcoming of the Calendar control is that you can't select a range of dates in the control. For example, let's suppose that you are using the Calendar control in a form to indicate the dates that you will start and return from your vacation. You select a date in the control and then hold down another key such as the SHIFT key to try and select the end date. However, clicking the end date moves the date selected to that date without specifying a range. In other words, only one date can be selected—not a series of dates.

Fortunately, this limitation is also relatively easy to overcome with a few lines of code. To demonstrate, use the same form that you created in the previous section. First, add three text boxes representing the desired date range, the start date, and the end date. Then, add a command button which will contain the code to calculate the range of dates from the start date. Here's how to do it:

  1. With the form you created in the previous section in Design view, click a Text Box control in the Toolbox, and then click on the form to place the control. Position the text box below the Current_Date text box.

  2. Double-click the new text box, click the All tab, and in the Name box, type txtRange.

  3. Click on the Label control next to the text box, click the All tab, and in the Caption box, type Range.

  4. Click a Text Box control in the Toolbox, and then click on the form to place the second text box control.

  5. Double-click the text box you just added, and in the Name box, type txtStartDate.

  6. Click on the Label control next to the text box, and in the Caption box, type Start Date.

  7. Click a Text Box control in the Toolbox, and then click on the form to place the third control.

  8. Double-click the third text box you just added, and in the Name box, type txtEndDate.

  9. Click on the Label control next to the text box, and in the Caption box, type End Date.

  10. Click the Command Button control in the Toolbox, and then click on the form to place the control.

  11. In the Command Button Wizard, click Cancel. Double-click the command button, and in the property sheet, click the All tab, and in the Name box, type cmdDisplayRange.

  12. In the Caption box, type Get Range.

  13. Next, click the Event tab in the property sheet, click the On Click event, and then click the build button (the button with ellipses …) in the box. In the Choose Builder dialog box, click Code Builder, and then click OK.

  14. Insert the following code between Private Sub cmdDisplayRange_Click and End Sub, and then close the Visual Basic Editor:

    Dim dt As Date
Dim intRange As Integer

dt = Me!calMyCalendar.Value
intRange = Me!txtRange.Value
Me!txtStartDate = dt
Me!txtEndDate = DateAdd("d", intRange, dt)

  15. Open the form in Form view, and then click a date in the Calendar control.

  16. Next, type a range, such as 5 or -5, in thetxtRangetext box, and then click the Get Range button. Notice that thetxtStartDatetext box displays the day that you clicked in the calendar, and thetxtEndDatetext box contains a date that is offset by the number of days you typed into thetxtRangetext box (see Figure 2).

Aa139958.odc_acdates02(en-us,office.10).gif

Figure 2. Calendar control displaying a start and end date.

Note   Use a positive range number to indicate an end date that is ahead of the start date. A negative range number results in an end date that is before the start date.

The code in the On Click event of the command button uses theDateAddfunction to calculate the end date from the start date and range which was specified on the form.

Conclusion

I encourage you to explore the Calendar control to discover other ways to make working with dates in your Access databases even easier. For more information on the Calendar control and Access, there is a good section on the Calendar control in the article Using ActiveX Controls on the Microsoft Developer Network.