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
