Results 1 to 2 of 2
  1. #1
    Rod is offline Expert
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Metro Manila, Philippines

    Another date picker.

    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.


    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:
    1. frmExample – a simple example form containing the date prompt as a subform;
    2. frmDatePrompt – the small form intended to be imbedded as a sub form;
    3. frmDatePick – the pop-up form enabling selection of a date.

    One module:

    1. basCalendar – contains some useful date manipulation routines.

    One table:

    1. tblPeriod – a typical accounting period table.

    How to Implement

    1. Copy frmDatePrompt, frmDatePick, basCalendar and tblPeriod into your own project. (I shall assume you do not change my names in the following instructions.)
    2. Embed frmDatePrompt as a sub form in any of your forms.
    3. Include a declaration for the sub form in your own form’s declaration section similar to:
      Private WithEvents mfrmDatePrompt As Form_frmDatePrompt
    4. Reference the sub form in your own form’s Load event such as:
      Set mfrmDatePrompt = Me.sfrDatePrompt.Form
      (My sub form control is named sfrDatePrompt.)
    5. 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).


    1. 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.
    2. 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.)
    3. The sub system demonstrates a simple use of user events.
    4. 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!).
    5. I have removed all error handling.


  2. #2
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010

    Don't ever worry about "stepping on my toes"! I'm a big BIG believer that the more examples from others, the more it will help others which may in turn then post examples to help more developers. You posted a very nice example which I want to take a closer look at as soon as I get a bit more free time. Thanks for helping by posting another date picker example! I was getting worried that I was the only one that posted an example for something I think Microsoft should've incorporated into MsAccess 2010. Maybe we'll get lucky and they'll put the calendar control back into their next version. Nice job!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Date Picker?
    By gazzieh in forum Forms
    Replies: 9
    Last Post: 02-09-2013, 09:35 AM
  2. Date picker
    By SFC in forum Forms
    Replies: 2
    Last Post: 02-07-2012, 04:00 PM
  3. Date Picker
    By kelly in forum Database Design
    Replies: 3
    Last Post: 09-15-2011, 12:42 PM
  4. Date Picker
    By otisdaddy in forum Database Design
    Replies: 2
    Last Post: 07-01-2011, 12:30 PM
  5. Date picker
    By BI4K12 in forum Access
    Replies: 1
    Last Post: 06-09-2010, 11:11 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Other Forums: Microsoft Office Forums - Senior Forums