PopUp Date Picker Form

[James A. Fortune]

Perhaps some will find the following useful. With more and more date
fields showing up on forms lately, I decided to try to come up with a
better way to choose and validate date fields.

I created a form called frmPopUpDatePicker containing the following
controls:

PopUpDatePicker A97 Calendar ActiveX Control 8.0 (Note: A custom
calendar control/form can be used here instead.)
cmdDone Command Button labeled 'Insert Date' indicating that the
selected date is to be inserted into the control
cmdReturnBlank Command Button labeled 'Erase Date' indicating that the
control is to be set to Null
cmdCancel Command Button labeled 'Cancel' indicating that no action is
to be taken

I lock the control on a separate form, then put in a call to
frmPopUpDatePicker as shown below.

A control on any form (a textbox in this example) used as a date
container can call it something like:

Private Sub txtMyDateField_Click()
DoCmd.OpenForm "frmPopUpDatePicker", , , , , , Me.Name & "!" &
Me.ActiveControl.Name
End Sub

A similar control on a subform would call it something like:

Private Sub txtMyDateField_Click()
DoCmd.OpenForm "frmPopUpDatePicker", , , , , , "frmMain" & "!" & Forms!
frmMain!SubformMain.Name & "!" & Me.ActiveControl.Name
End Sub

Code behind frmPopupDatePicker:

----'begin code behind form----
Option Compare Database
Option Explicit

Dim boolSelection As Boolean
Dim boolBlank As Boolean
Dim boolCancel As Boolean
Dim varOriginal As Variant
Dim dtPopUp As Date
Dim ctl As Control
Const TwipsPerInch = 1440

Private Sub cmdCancel_Click()
boolCancel = True
DoCmd.Close acForm, Me.Name
End Sub

Private Sub cmdDone_Click()
DoCmd.Close acForm, Me.Name
End Sub

Private Sub cmdReturnBlank_Click()
boolBlank = True
DoCmd.Close acForm, Me.Name
End Sub

Private Sub Form_Load()
Dim strSubformControlName As String
Dim strFormName As String
Dim strControlName As String
Dim varOpenArgs As Variant

If IsNull(Me.OpenArgs) Then
MsgBox ("This form should not be opened by itself.")
DoCmd.Close acForm, Me.Name
Exit Sub
End If
boolSelection = False
boolBlank = False
boolCancel = False
varOpenArgs = Split(Me.OpenArgs, "!")
strFormName = varOpenArgs(0)
If UBound(varOpenArgs) = 2 Then
'E.g., Subform control: frmMain!SubformMain!txtX
strControlName = varOpenArgs(2) 'txtX
Set ctl = Forms(strFormName).Controls(varOpenArgs(1)).Controls
(strControlName)
Else
strControlName = varOpenArgs(1)
Set ctl = Forms(strFormName).Controls(strControlName)
End If
varOriginal = ctl.Value
'If the control contains a date then use that as the popup default
value
If Not IsNull(ctl.Value) Then
PopUpDatePicker.Value = ctl.Value
Else
PopUpDatePicker.Value = Date
boolSelection = True
End If
End Sub

Private Sub Form_Unload(Cancel As Integer)
If boolCancel = True Then
ctl.Value = varOriginal
ElseIf boolBlank = True Then
ctl.Value = Null
ElseIf boolSelection = True Then
ctl.Value = Format(dtPopUp, "mm/dd/yy")
End If
End Sub

Private Sub PopUpDatePicker_AfterUpdate()
dtPopUp = PopUpDatePicker.Value
boolSelection = True
End Sub
'----end code behind form----

When the control is clicked (I didn't consider using the OnEnter event
because most of my users use their mouse a lot), the PopUpDatePicker
form gives the user the option of inserting a date into the date
field, blanking the date field, or cancelling the action before
closing automatically. I only tested this using A97 with an an ad hoc
Split() function. Also, I did not test what would happen if two
controls on two separate forms use the PopUp form at the same time.
Furthermore, I did not consider making it work for a control on a
subform within a subform. It's not totally polished yet, but I think
I will be using it a lot.

James A. Fortune
(e-mail address removed)

Disclaimer: Any programming examples shown are for illustration
purposes only, without warranty either expressed or implied. This
includes, but is not limited to, the implied warranties of
merchantability or fitness for a particular purpose. This post assumes
that you are familiar with the programming language that is being
demonstrated and with the tools that are used to create and to debug
procedures. I might explain the functionality of a particular
procedure, but I am under no obligation to modify these examples to
provide added functionality or to construct procedures to meet your
specific requirements. Any code samples posted contain no known
hidden material defects. However, anyone who uses any code sample
posted does so with the understanding that they are responsible for
any testing of any illustrative code sample for any particular use.
Furthermore, anyone using an illustrative code sample I provide or
code derived from it does so at their own risk.

 

[Keven Denen]

Jim,

How about this .........

You have a form named FrmOrder and on the form is a textbox bound to
OrderDate. You want to use a calendar to enter OrderDate rather than having
the user type it in.

1.    Create a popup form named PFrmCalendar with a calendar control named
MyCalendar.
2.    Also add a button named Cancel on PFrmCalendar.
3.    In the AfterUpdate event of the calendar control put the following
code:
        Me.Visible = False
4.    In the Click event of the Cancel button put the following code:
        DoCmd.Close

5.    On FrmOrder, lock and disable OrderDate.
6.    Add a button named Enter Order Date beside OrderDate.
7.    Put the following code in the Click event of Enter Order Date:
        DoCmd.OpenForm "PFrmCalendar",,,,,acDialog
        If SysCmd(acSysCmdGetObjectState, acForm, "PFrmCalendar")=
acObjStateOpen Then
            Me!OrderDate = Forms!PFrmCalendar!Calendar.Value
            DoCmd.Close "PFrmCalendar"
        End If

Clicking Enter Order Date on FrmOrder opens the calendar pop-up form. At
this point he user has two options. He can Cancel or click on a date on the
calendar. The calendar pop-up form opened in acDialog so the code for the
button on the main form is waiting for something to happen on the calendar
form. If the user cancels, the calendar form closes. The If code in the
Click event of Enter Order Date fails and OrderDate remains empty. If the
user clicks on a date in the calendar, the calendar form becomes invisible.
The If code in the Click event of Enter Order Date executes and OrderDateis
filled with the date that was clicked on. The calendar form then closes.

Steve
(e-mail address removed)

























- Show quoted text -

I prefer to use the Microsoft Date and Time Picker Control 6.0 (SP4).
Everything is built in and I've yet to find any problems with it as a
standard date picker. Of course in 2007 this isn't an issue anymore as
Date fields have this functionality built in.

Keven Denen

 

[James A. Fortune]

I prefer to use the Microsoft Date and Time Picker Control 6.0 (SP4).
Everything is built in and I've yet to find any problems with it as a
standard date picker. Of course in 2007 this isn't an issue anymore as
Date fields have this functionality built in.

Keven Denen

Thanks to both for the input. A97 is almost phased out where I work,
but A2K3 will linger on for a few more years. I suppose that both
Microsoft and I should have done this about 10 years ago ,

James A. Fortune
(e-mail address removed)

 

[Bob Quintal]

m:

Thanks to both for the input. A97 is almost phased out where I
work, but A2K3 will linger on for a few more years. I suppose
that both Microsoft and I should have done this about 10 years ago
,

James A. Fortune
(e-mail address removed)

Or you can download Brendan Kidwell's datepicker, import the form and
code, and follow the instructions.

http://www.glump.net/software/microsoft_access_date_picker

 

[James A. Fortune]

Perhaps some will find the following useful. With more and more date
fields showing up on forms lately, I decided to try to come up with a
better way to choose and validate date fields.

I created a form called frmPopUpDatePicker containing the following
controls:

PopUpDatePicker A97 Calendar ActiveX Control 8.0 (Note: A custom
calendar control/form can be used here instead.)
cmdDone Command Button labeled 'Insert Date' indicating that the
selected date is to be inserted into the control
cmdReturnBlank Command Button labeled 'Erase Date' indicating that the
control is to be set to Null
cmdCancel Command Button labeled 'Cancel' indicating that no action is
to be taken

I lock the control on a separate form, then put in a call to
frmPopUpDatePicker as shown below.

A control on any form (a textbox in this example) used as a date
container can call it something like:

Private Sub txtMyDateField_Click()
DoCmd.OpenForm "frmPopUpDatePicker", , , , , , Me.Name & "!" &
Me.ActiveControl.Name
End Sub

A similar control on a subform would call it something like:

Private Sub txtMyDateField_Click()
DoCmd.OpenForm "frmPopUpDatePicker", , , , , , "frmMain" & "!" & Forms!
frmMain!SubformMain.Name & "!" & Me.ActiveControl.Name
End Sub

Code behind frmPopupDatePicker:

----'begin code behind form----
Option Compare Database
Option Explicit

Dim boolSelection As Boolean
Dim boolBlank As Boolean
Dim boolCancel As Boolean
Dim varOriginal As Variant
Dim dtPopUp As Date
Dim ctl As Control
Const TwipsPerInch = 1440

Private Sub cmdCancel_Click()
boolCancel = True
DoCmd.Close acForm, Me.Name
End Sub

Private Sub cmdDone_Click()
DoCmd.Close acForm, Me.Name
End Sub

Private Sub cmdReturnBlank_Click()
boolBlank = True
DoCmd.Close acForm, Me.Name
End Sub

Private Sub Form_Load()
Dim strSubformControlName As String
Dim strFormName As String
Dim strControlName As String
Dim varOpenArgs As Variant

If IsNull(Me.OpenArgs) Then
MsgBox ("This form should not be opened by itself.")
DoCmd.Close acForm, Me.Name
Exit Sub
End If
boolSelection = False
boolBlank = False
boolCancel = False
varOpenArgs = Split(Me.OpenArgs, "!")
strFormName = varOpenArgs(0)
If UBound(varOpenArgs) = 2 Then
'E.g., Subform control: frmMain!SubformMain!txtX
strControlName = varOpenArgs(2) 'txtX
Set ctl = Forms(strFormName).Controls(varOpenArgs(1)).Controls
(strControlName)
Else
strControlName = varOpenArgs(1)
Set ctl = Forms(strFormName).Controls(strControlName)
End If
varOriginal = ctl.Value
'If the control contains a date then use that as the popup default
value
If Not IsNull(ctl.Value) Then
PopUpDatePicker.Value = ctl.Value
Else
PopUpDatePicker.Value = Date
boolSelection = True
End If
End Sub

Private Sub Form_Unload(Cancel As Integer)
If boolCancel = True Then
ctl.Value = varOriginal
ElseIf boolBlank = True Then
ctl.Value = Null
ElseIf boolSelection = True Then
ctl.Value = Format(dtPopUp, "mm/dd/yy")
End If
End Sub

Private Sub PopUpDatePicker_AfterUpdate()
dtPopUp = PopUpDatePicker.Value
boolSelection = True
End Sub
'----end code behind form----

When the control is clicked (I didn't consider using the OnEnter event
because most of my users use their mouse a lot), the PopUpDatePicker
form gives the user the option of inserting a date into the date
field, blanking the date field, or cancelling the action before
closing automatically. I only tested this using A97 with an an ad hoc
Split() function. Also, I did not test what would happen if two
controls on two separate forms use the PopUp form at the same time.
Furthermore, I did not consider making it work for a control on a
subform within a subform. It's not totally polished yet, but I think
I will be using it a lot.

James A. Fortune
(e-mail address removed)

Disclaimer: Any programming examples shown are for illustration
purposes only, without warranty either expressed or implied. This
includes, but is not limited to, the implied warranties of
merchantability or fitness for a particular purpose. This post assumes
that you are familiar with the programming language that is being
demonstrated and with the tools that are used to create and to debug
procedures. I might explain the functionality of a particular
procedure, but I am under no obligation to modify these examples to
provide added functionality or to construct procedures to meet your
specific requirements. Any code samples posted contain no known
hidden material defects. However, anyone who uses any code sample
posted does so with the understanding that they are responsible for
any testing of any illustrative code sample for any particular use.
Furthermore, anyone using an illustrative code sample I provide or
code derived from it does so at their own risk.

An additional line should be added at the end of the Form_Load()
subroutine:

dtPopUp = PopUpDatePicker.Value

Without that line, if the user accepts the default date, a value from
1999 can populate the control.

James A. Fortune
(e-mail address removed)