The Access Wizard Newsletter
Tips, Tricks, and Traps for Access Users and Developers.

Speed Through Entering DatesPowerful Keyboard Shortcuts

Introduction

Have you been in one of those mazes where it seems like it takes forever to get out? We see that a lot of New England, where farmers will turn a cornfield into elaborate maze. If you are looking for fun, that’s fine; but if you are working in a database, taking the long way to get something done is not what you want. 



If you have ever used the program Quicken, you have experienced some great keyboard shortcuts to enter dates. In this month’s Wizard, I will show you how to get those very powerful shortcuts to work for you in Microsoft Access. 
 

Overview

If you have a form that requires a date entry, you probably know that you can have a default date. With the properties of the field, or form control, you can choose things like today’s date or now. You probably are aware also of the fact that any date field in a form can have a calendar pop-up when the user clicks into the field.

However, what if you want to enter a date easily without a lot of keystrokes?  For one record, maybe you want it be the first of this month and for the next, the last day of this month. Or you might want the first or last day of the year, or maybe the day after whatever is currently in the control. With powerful keyboard shortcuts, these become nothing more than a keystroke away.
 

The Critical Code

I have put the code at the very end of this Wizard because it is fairly long and involved. You are welcome to pull it apart to understand it or, if you prefer to cut and paste, feel free.

The code is called every time a user presses a key in a date field for which you want a shortcut to a requested date.

The code, when called, will inspect the keystroke and, if it is any of the shortcut keys, the program will convert the keystroke into a date. We will take one shortcut as an example but know that, conceptually, they all work the same.

Let’s say that the user has pressed the t key and the desired effect is to insert today’s date. The code below, when called, will translate that keystroke into the current date. The code takes three inputs: the keystroke, the name of the form, and the name of the control. The heart of the code is in the select case section – there it will test to see if it is a keystroke shortcut and, if so, will translate it into a date; otherwise, it accepts the keystroke as entered.

The other important piece of the puzzle is how to call the code. To do this, use the keypress event. For example, I have a text box on one of my forms called txtDateDue. To program this control to accept the shortcut, I open the form in design view, right-click on the control and choose properties. Then I choose the event tab, followed by the ellipsis, which appears once you click into the on keypress event.



If When you press the ellipsis, the code window will show up and is ready for the following:
Private Sub txtDateDue_KeyPress(KeyAscii As Integer)
    ChangeDate KeyAscii, Me, "txtDateDue"
End Sub
 
This code is very simple; it calls the change date code and passes in 3 parameters:
  • KeyAscii - the key that the user pressed
  • me  - the name of the form
  • the name of the control, in this case txtDateDue.
The Change Date code will take a look at the keystroke, determine whether or not it is something that it can translate into a shortcut date, and if so will supply the date. Otherwise, it will just accept the keystroke so that the user can type a full date.
 

Extensibility

If you read the code, you will see that I have set things up for the following shortcuts (with the shortcuts in bold):
  • Today
  • first of the Month
  • last montH
  • first day of Year
  • last day of yeaR
  • one day greater than whatever is in the cell (+)
  • one day less than whatever is in the cell (-)
There is even a provision for cases where it will calculate the first or last day of a fiscal year.

The shortcuts I chose were designed to be easily remembered mnemonics, M for first day of month, H for last day of the month, etc. You could certainly code to the first or last day of the week or virtually anything that you could logically conceptualize. If you go down this route, you will definitely want to take a look at this month’s Tip Of The Month, which will point you in the right direction.
 

Conclusion and Invitation

This month, I showed you how to quickly speed around dates using shortcuts. Using this approach, you cut through a maze of potential keystrokes to get a date with ultimate ease. As a result, you dramatically cut down on typing as well as the potential of making a data input error.

Invitation
If you are a regular reader of the Access Wizard, you know that I am very much in favor of getting more work done with less effort. If you have some problem that you think would be helped by some type of shortcut, please send it along to me and I'll be happy to see if I can come up with an easy way to get things done.
 

The Called Code

Sub ChangeDate(KeyAscii As Integer, frm As Form, strCtl As String)
    ' If you copy this code beware of line wraps
    ' If necessary add the characters “ &_” at the end of the line or remove
    'the carriage return
    ' Comments  : This routine changes a date field via user shortcut keys as follows
    '                       t  Today
    '                       -   subtract one day
    '                       +   add 1 day
    '                       m  first day of month
    '                       h   last day of montH
    '                       y   first day of Year
    '                       r   last day of yeaR
    ' Call this routine from the Keypress event e.g., ChangeDate KeyAscii, Me, "txtDate" where
    ‘txtDate" is the name of the control containing the date to be changed
    ' Parameters: Keyascii - the key pressed
    '                       frm - sending form
    '                       strCtl - sending Control
    ' Created   : 10/20/01 12:55 by Jim Connell of Custom Software
    ' 978-392-3462, jim@custom-software.biz
    ' --------------------------------------------------
    On Error GoTo PROC_ERR
    Dim dtm As Date
    dtm = Nz(frm(strCtl), Date)
    Select Case KeyAscii
      Case Is = 43 ' move forward 1 day
         KeyAscii = 0
         frm(strCtl) = dtm + 1
        
      Case Is = 45 ' move back 1 day
         KeyAscii = 0
         frm(strCtl) = dtm - 1
        
      Case Is = 109   'm move to beginning of month
         KeyAscii = 0
         If Day(dtm) > 1 Then
          frm(strCtl) = DateAdd("d", -Day(dtm) + 1, dtm) ' this month
          Else
          frm(strCtl) = DateAdd("m", -1, dtm)  ' back one month
         End If
    Case Is = 104   ' h  ; as in montH
         KeyAscii = 0
         If frm(strCtl) < DateSerial(Year(dtm), Month(dtm) + 1, 0) Then
          frm(strCtl) = DateSerial(Year(dtm), Month(dtm) + 1, 0) ' go to end of month
         Else
          frm(strCtl) = DateSerial(Year(dtm), Month(dtm) + 2, 0) ' jump forward to end of next month
         End If
    Case Is = 116   ' t  ; as in Today
         KeyAscii = 0
         frm(strCtl) = Date
    Case Is = 121   ' y  ; as in =Year
         KeyAscii = 0
         frm(strCtl) = DateSerial(Year(dtm), 1, 1)
     Case Is = 114 ' r as in yeaR
         KeyAscii = 0
         frm(strCtl) = DateSerial(Year(Date) + 1, 1, 1) - 1
    Case Is = 102 'Fiscal Year
        'available only if there is a stored Fiscal Year Beginning date
         'KeyAscii = 0
         'frm(strCtl) = Nz(DLookup("[FYBegin]", "zhtbladmin"), Date)
    Case Is = 108 '   Fiscal Year End
         'available only if there is a stored Fiscal Year Endingdate
         'frm(strCtl) = Nz(DLookup("[FYEnd]", "zhtbladmin"), Date)
     Case Else
        'do nothing
  End Select
 
PROC_EXIT:
    Exit Sub
PROC_ERR:
    MsgBox Err.Description
    Resume PROC_EXIT
 
End Sub

Tip of the Month: A Synopsis of Date Math

If you have done any calculation dates, you know that there is no easy way around our calendar. Our months each have between 28 and 31 days. Although we say that a year is 52 weeks, when you multiply 52 x 7 you get 364, and a year is about 365 ¼ days. So mathematically, a year is not a base 10, 12, or 7; it’s not a base anything. In addition to the one day we add for every leap year (which occurs every four years), there are exceptions to that that rule as well.

To help you get down this road, I have a series of articles in the Access Wizard archives that will help you understand some of the intricacies of date math and how to manipulate them in Access. On the archive pages, do a search on the word “date” and you will see a multi part series from 2007.



Quick Links:

Custom Software Home

Access Wizard Archives

Our Services

© Copyright Custom Software
All Rights Reserved | Westford, MA 01886