Paul, I do not wish to 'step on your toes' but present my date picker as a similar but slightly different solution to the same problem. We both obviously saw a need and ended up with similar solutions. I however used user events and an OOP approach to form opening. My solution was also geared to an accounting project and takes notice of accounting period status. For this reason I offer it here since these features may be of interest.
History
I developed this sub system well before the advent of Access’ Date Picker. At that time I experienced a few problems with the date picker control (ocx?) and wanted a sub system that was more orientated toward an accounting project.
What’s Included
Three forms:
- frmExample – a simple example form containing the date prompt as a subform;
- frmDatePrompt – the small form intended to be imbedded as a sub form;
- frmDatePick – the pop-up form enabling selection of a date.
One module:
- basCalendar – contains some useful date manipulation routines.
One table:
- tblPeriod – a typical accounting period table.
How to Implement
- Copy frmDatePrompt, frmDatePick, basCalendar and tblPeriod into your own project. (I shall assume you do not change my names in the following instructions.)
- Embed frmDatePrompt as a sub form in any of your forms.
- Include a declaration for the sub form in your own form’s declaration section similar to:
Code:Private WithEvents mfrmDatePrompt As Form_frmDatePrompt- Reference the sub form in your own form’s Load event such as:
(My sub form control is named sfrDatePrompt.)Code:Set mfrmDatePrompt = Me.sfrDatePrompt.Form- You are now free to refer, anywhere in your own form, to the selected date as mfrmDatePrompt.gdteSelected and the period status as mfrmDatePrompt.PeriodStatus (or to use the arguments returned by the user event).
Notes
- If you don’t need the period status feature then you need to ‘unravel’ the code referring to this. frmDatePick needs no changes. Remove the text box PeriodStatus from frmDatePrompt and remove all references to it in the accompanying vba module. Delete the first seven lines in the DisplayDate procedure in that module. Remove the second argument from the DateChange event. Some procedures in basCalendar need removing.
- basCalendar contains some freebie date manipulation routines. (Beware that some of these assume a 13 period accounting year where the 13th period is used purely for year-end adjustments and accruals.) Of particular interest is the procedure DateSQL for returning a string date formatted for inclusion in SQL comparisons. (The property declarations are a hang-over from the fact that this was originally a class module.)
- The sub system demonstrates a simple use of user events.
- Forms are opened using the more OOP approach of instanciating the Access form class. I believe this gives more control (and allows multiple copies of the same form!).
- I have removed all error handling.
DateSelect.accdb