With MS Access 2010, the date method no longer returns the current date - instead it produces a compile error!
This has turned out to be much more involved than I thought. There are 2 Calendar Control examples
I sometimes work with the Toolbox docked at the bottom of the screen. In this configuration, the More Controls icon won't work - sometimes nothing happens, but more frequently the kernel produces a general protection fault and MS Access crashes. (Of course, you loose all un-saved changes.) The solution is to un-dock the Toolbox before selecting More Controls. Of course, you could simply use the menu instead.
(Unbelievable. Don't they test anything? Verified with Microsoft Access 97 SR-2.)
This control is easy to use - just place it on your form. It does not require any special code. Click the down arrow to display the calendar. As soon as you click on a date, the calendar closes and the date is copied to the edit field.
Warning: This control does not like null values. In its default configuration, if you create a record and don't automatically assign default values to all the date fields, you will get an error.
In order to allow null values, you must enable the integrated check box. When the box is not checked, the value is Null, and the field shows today's date as grayed out; when checked, you can set the date. (It looks pretty dorkey.)
I prefer the Calendar Control discussed next because it shows a null value as just a blank field.
I found a solution to this 5-04-05.
First, on a form, place 3 components
Dim Calendar_UIEdit As TextBox, Calendar_UIToggleButton As ToggleButton
ShowCalendar TheTextBoxName, TheToggleButtonName
This code is designed so that, if there are several text controls associated with dates, you only need one Calendar Control and you don't need to manage several copies of almost identical code, one set per control. Instead, each toggle button calls ShowCalendar and passes the names of the appropriate Text Box / Toggle Button pair. The code even positions the calendar under the associated text box.
There are a number of instances where the autopositioning algorithm fails (when your text box is too close to the edge of the form, the algorithm places the calendar so that it overlaps the edge of the form) and you will get an error. To solve this, I have provided ShowCalendar_xy which requires a Text Box / Toggle Button pair and the x/y position where you want the calendar displayed. (The x/y values must be in inches.)
In order to get the correct x/y position values, I suggest manually placing the calendar control where you want it displayed and using its Left and Top properties as your x/y values, respectively.
Both Click and DblClick are available this way and missing in the Properties viewer.
Use this to update a text control when the calendar is clicked
Private Sub Calendar_1_Click() Calendar_UIEdit.Value = Calendar_1.Value End Suband this to close the Calendar when it is double clicked
Private Sub Calendar_2_DblClick() Text14.SetFocus Calendar_2.Visible = False End Sub
I spent 5 hours getting the double click to work. Every time Text14.SetFocus was executed, Access showed
Run-time error '2110':Eventually, I added another Calendar control and the code worked. Apparently, Access has some sort of design problem.
Microsoft Access can't move the focus to the control Text14.
Use with Multiple Detail Records
The "solution" is to use a Push Button (Command Button) control. This does not render the button as depressed while the Calendar is displayed (the Windows standard), but it is better than having them all depressed. Set the properties the same as for the Toggle Button above.
If you use the routines below, notice that they require you to pass a Toggle Button. I just place a hidden button (Visible = No) somewhere on the form and pass that.
For some forms (particularly those with multiple detail records and/or a subform), no section is large enough to display a Calendar. In those cases, I use a Calendar dialog box to provide this functionality.
Code that works with the "Calendar Control"
Option Compare Database Option Explicit Dim Calendar_UIEdit As TextBox, Calendar_UIToggleButton As ToggleButton Private Sub Calendar_1_Click() Calendar_UIEdit.Value = Calendar_1.Value End Sub Private Sub Calendar_1_DblClick() Calendar_UIEdit.SetFocus ' This line fails in some cases - probable MS Access design problem Calendar_1.Visible = False End Sub Private Sub Calendar_1_LostFocus() If Not IsEmpty(Calendar_1.Value) Then Calendar_UIEdit.Value = Calendar_1.Value End If Calendar_UIToggleButton.Value = False Calendar_UIEdit.SetFocus Calendar_1.Visible = False End Sub Private Sub OtherDate_UIToggle_Click() ShowCalendar OtherDate_UIEdit, OtherDate_UIToggle End Sub Private Sub Toggle12_Click() ShowCalendar_xy Text10, Toggle12, 1.5417, 0 End Sub Private Sub Toggle3_Click() ShowCalendar TestDateUI_Edit, Toggle3 End Sub Private Sub ShowCalendar_xy_Inches(EditField As TextBox, ToggleButton As ToggleButton, _ x As Single, y As Single) Set Calendar_UIEdit = EditField Set Calendar_UIToggleButton = ToggleButton Calendar_1.Value = EditField.Value Calendar_1.Top = y * 1440 ' 1440 converts inches to twips Calendar_1.Left = x * 1440 Calendar_1.Visible = True Calendar_1.SetFocus End Sub Private Sub ShowCalendar_xy_Pixels(EditField As TextBox, ToggleButton As ToggleButton, _ x As Integer, y As Integer) Set Calendar_UIEdit = EditField Set Calendar_UIToggleButton = ToggleButton Calendar_1.Value = EditField.Value Calendar_1.Top = y ' no conversion Calendar_1.Left = x Calendar_1.Visible = True Calendar_1.SetFocus End Sub Private Sub ShowCalendar(EditField As TextBox, ToggleButton As ToggleButton) Dim i Set Calendar_UIEdit = EditField Set Calendar_UIToggleButton = ToggleButton Calendar_1.Value = EditField.Value i = EditField.Top + EditField.Height + 100 If (i + Calendar_1.Height) < Detail.Height Then Calendar_1.Top = i Else Calendar_1.Top = Detail.Height - Calendar_1.Height - 8 ' - 300 End If i = EditField.Left - Calendar_1.Width / 3 If i < 300 Then Calendar_1.Left = 300 Else Calendar_1.Left = i End If If (Calendar_1.Left + Calendar_1.Width) > Me.Width Then Calendar_1.Left = Me.Width - Calendar_1.Width End If Calendar_1.Visible = True Calendar_1.SetFocus End Sub
A Calendar Dialog Box
Calendar_1.Value = #3/4/2004#fails in Form_Open.
For a basic Calendar dialog box
Code for a Calendar Dialog Box
Private Sub ShowCalendar_UICommand_Click() Dim DialogName As String Dim ResultFlag As Integer DialogName = "Calendar_frm" ' The name of the dialog box 'DoCmd.OpenForm "FormName", , , , , , "Passed Parameter" DoCmd.OpenForm DialogName, , , , , acDialog, Date_ ResultFlag = Forms(DialogName).Form.Tag ' Uses the standard MsgBox constants If ResultFlag = vbOK Then ' Test that OK was pressed Date_ = Forms(DialogName)!Calendar_1.Value End If ' Without an explicit "DoCmd.Close", ' * The dialog box's Form_Open is run only ' the first time "DoCmd.OpenForm" is called ' * When the form is merely hidden, ' "DoCmd.OpenForm" does not always wait for the ' user to hide the dialog box - very unpredictable DoCmd.Close acForm, DialogName End SubThe following code is placed in the Calendar dialog box
Private Sub Form_Load() If Not IsNull(Me.OpenArgs) Then Calendar_1.Value = Me.OpenArgs End If End Sub Private Sub Cancel_UICommand_Click() ' Hide dialog box so the parameters are still available Me.Visible = False Tag = vbCancel End Sub Private Sub OK_UICommand_Click() ' Hide dialog box so the parameters are still available Me.Visible = False Tag = vbOK End SubIn Form_Load, either of these will work
Calendar_1 = Me.OpenArgs Calendar_1.Value = Me.OpenArgs
MS Access 2007
MS Access 2010
Dim intDate, intYear, intMonth intDate = Date intYear = Year (intDate) intMonth = Month(intDate)
'intDate = Date ' removed no longer works intYear = Year (CDate(Now)) ' changed to a function call intMonth = Month(CDate(Now)) ' changed to a function call
(By the way, the spacing shown above is not allowed in MS Access - it simply removes the extra spaces to make the code less readable!)
I am going out on a limb here and making a guess. It appears that date is now a variable type and, therefore, no longer gets the current date. As a result, MS Access no longer knows what to do with it. (Unfortunately, I no longer have access to an older system to check any of this.)
At any rate, this problem happened because the corporate bosses pushed the MS Office "update" to every machine .. without warning. One day, everything was simply broken. Of course, these clueless morons were not able to fix (or even admit to) the disaster they caused.
My rule still stands - never update working software. EVER!!