Prev: dynaPDF.dll and Runtime A2003 = Crash
Next: Linking an Excel file in Access with mixed data types in columns and #Num!
From: James A. Fortune on 23 Oct 2009 15:26
On Oct 20, 4:54 pm, "James A. Fortune" <CDMAPos...(a)FortuneJames.com>
wrote:
> 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
> CDMAPos...(a)FortuneJames.com
>
> 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
CDMAPoster(a)FortuneJames.com
First  |  Prev  | 
Pages: 1 2
Prev: dynaPDF.dll and Runtime A2003 = Crash
Next: Linking an Excel file in Access with mixed data types in columns and #Num!