Results 1 to 9 of 9
  1. #1
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682

    MSAccess 2007/2010 Date Picker Replacement - Version 3

    Here is a new updated (non-api and non-activeX) calendar popup form and subform for MSAccess 2010 which you can utilize in your existing programs. If you are converting from 2003 or prior, you'll note that the Calendar activeX control is no longer supported (you'll need to remove it in all your forms as well as in the References). You then have the option of using the (new) DatePicker built into any date defined data fields (you can disable this by selecting "Never" for the "Show Date Picker" property of the control - 8th property down when the All tab is selected.) Otherwise if this is set to "For Dates" (which it defaults to), you'll see a small calendar icon to the right of the control when the field has focus or is clicked on. The DatePicker control is fairly limited on navigating versus the old activeX Calendar Control but you may be able to customize it somehow. (I have yet to figure out why MS didn't build a better Calendar Control replacement versus the DatePicker. I was also surprised that there was no conversion. I had to manually remove the old Calendar Control and coding everywhere throughout the project.)

    To use either the popup calendar or subform in the attached, you only need to import the form called: CalendarPopUp or SubCalendar form into your *.accdb file (all the code/functions are contained within the forms - ie. you don't need to set any additional references, load any activeX components, or register any dll files, etc!!) I designed this so it could quickly be implemented into any existing or new design. This version has all MSAccess 2010 buttons for the dates versus the old version I posted which had 2000 buttons.

    For example, to open the popup calendar, you would add code in your event such as:

    Private Sub cmdCalBirthDate_Click()
    Me.BirthDate.SetFocus


    DoCmd.OpenForm "CalendarPopUp"
    Forms!CalendarPopUp!vFormName = "frmExamplePersons"
    Forms!CalendarPopUp!vFieldName = "BirthDate"
    End Sub

    That's it. The CalendarPopUp form does the rest. In the subform if you want to make it interactive whenever a date is clicked on (versus using the "Use Selected Date" button), you would add any code you want to fire (when a date is clicked on) in the: FireOffDateValue function.

    Fixed on both forms:
    Fixed is clicking the prev or next arrow buttons (when the 1st day of the month or last day of the month is selected) will now go to the prev or next month versus a message box saying "At the beginning of the month" or "At the end of the month".

    CalendarPopUp form:
    Fixed when popup form opens it goes to the date field value (where the SetFocus command was set to prior to the openform code).
    Attached Files Attached Files

  2. #2
    gazaway is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2012
    Posts
    5
    I'm having a problem getting the popup calendar to work. It appears when clicked, but none of the buttons work; that is, clicking them produces no result. The only thing I can change is the month from the pull down menu. Even "Cancel" is non-responsive. I have to close the calendar with the "x". It's been about 5 years since I've done anything with Access, so I'm no doubt forgetting something important here. I am running Access 2010 on Windows 7. I imported the CalendarPopUp and SubCalendar forms. My form is named "frmAddStudent", the calendar button is "cmdCalBirthDate", and the form field for the date is "datBDate". The table is "tblGrads" and the birthdate field is "datBDate". I am able to fill other fields in the table from this form. I changed the lines in the cmdCalBirthDate_Click procedure as follows:
    Me.datBdate.SetFocus
    Forms!CalendarPopUp!vFormName = "frmAddStudent"
    Forms!CalendarPopUp!vFieldName = "datBdate"
    What am I missing?
    Thanks in advance!

  3. #3
    gazaway is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2012
    Posts
    5
    Figured it out. The form hadn't imported properly -- or I selected the wrong one. I deleted the one in my database, then imported the correct one and it works great now.

  4. #4
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682

    Fix

    PLEASE NOTE: Edit the d22_doubleClick event and change the code to reflect the number 22. I'll try to send out a revised version.

  5. #5
    delphinas is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    3
    It's absolutely awesome stuff. One thing I can't figure out. At the moment it displays date like mm/d/yyyy . I've changed it through the fireof function(at the end) to show date like this: dd/m/yyyy. Now I need it to show like this dd/MM/yyyy. E.g if I select 1st day, it shows like 1/02/2012 but I need "0" in front like 01/02/2012.

    How to do it ?
    Thank you

  6. #6
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682
    You could possibly create a table which 'translates' a month value of 2 to 02 or you could dimension a variable as variant or string and use syntax like this and use the LEN code as follows:

    Dim vMonth as variant
    vMonth =format(me.mydate.value,"MM")
    if len(vMonth) < 2 then vMonth = "0" & vMonth

    myDateVariable = format(me.myDate.value,"d") & "/" & vMonth & "/" & format(me.myDate.value,"yyyy")

    You can do something similar for the day value as well.

    ie..
    Dim vDay as variant
    vDay = format(me.mydate.value,"dd")
    if len(vDay) < 2 then vDay = "0" & vDay

    myDateVariable = vDay & "/" & vMonth & "/" & format(me.mydate.value,"yyyy")

  7. #7
    burdge is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Sep 2014
    Posts
    1

    Newbie unsuccessful at modifying old MS Access database to convert calendar control to DatePicker

    Greetings!
    I stumbled upon this forum and was excited to see that it appears to have a solution to my problem!!!
    I am using an pre MS Access 2010 database which relied on the calendar control to generate reports for a given time period for any given individual (flight surgeon). It worked just fine until the USAF decided to move forward with MS Access 2010, and now the calendar feature does not work?! This post seems to solve the problem, however, I am a Newbie to Access and have no idea how to implement the recommended changes...I imported/exported the forms, etc., but can't seem to figure out what I need to modify to get the calendar to work again or to select start/end dates. I have spent two hours trying, without success...HELP!

    I tried to attach the access file, but it is 4 Mb unzipped and 1.4 zipped, both apparently exceeding the fourm maximum of 500Kb...

    Here is a DropBox Link to the database:

    https://www.dropbox.com/s/uh68myxa4t...-2014.mdb?dl=0

    Can anyone help me with this issue...

    Apologies, again, for the intrusion on your time and cyberspace,

    ~Eric

  8. #8
    JohnSymbol is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2015
    Location
    Faversham, Kent, UK
    Posts
    2

    Just what I was looking for!

    Quote Originally Posted by pkstormy View Post
    Here is a new updated (non-api and non-activeX) calendar popup form and subform for MSAccess 2010 which you can utilize in your existing programs. If you are converting from 2003 or prior, you'll note that the Calendar activeX control is no longer supported (you'll need to remove it in all your forms as well as in the References). You then have the option of using the (new) DatePicker built into any date defined data fields (you can disable this by selecting "Never" for the "Show Date Picker" property of the control - 8th property down when the All tab is selected.) Otherwise if this is set to "For Dates" (which it defaults to), you'll see a small calendar icon to the right of the control when the field has focus or is clicked on. The DatePicker control is fairly limited on navigating versus the old activeX Calendar Control but you may be able to customize it somehow. (I have yet to figure out why MS didn't build a better Calendar Control replacement versus the DatePicker. I was also surprised that there was no conversion. I had to manually remove the old Calendar Control and coding everywhere throughout the project.)

    To use either the popup calendar or subform in the attached, you only need to import the form called: CalendarPopUp or SubCalendar form into your *.accdb file (all the code/functions are contained within the forms - ie. you don't need to set any additional references, load any activeX components, or register any dll files, etc!!) I designed this so it could quickly be implemented into any existing or new design. This version has all MSAccess 2010 buttons for the dates versus the old version I posted which had 2000 buttons.

    For example, to open the popup calendar, you would add code in your event such as:

    Private Sub cmdCalBirthDate_Click()
    Me.BirthDate.SetFocus
    DoCmd.OpenForm "CalendarPopUp"
    Forms!CalendarPopUp!vFormName = "frmExamplePersons"
    Forms!CalendarPopUp!vFieldName = "BirthDate"
    End Sub

    That's it. The CalendarPopUp form does the rest. In the subform if you want to make it interactive whenever a date is clicked on (versus using the "Use Selected Date" button), you would add any code you want to fire (when a date is clicked on) in the: FireOffDateValue function.

    Fixed on both forms:
    Fixed is clicking the prev or next arrow buttons (when the 1st day of the month or last day of the month is selected) will now go to the prev or next month versus a message box saying "At the beginning of the month" or "At the end of the month".

    CalendarPopUp form:
    Fixed when popup form opens it goes to the date field value (where the SetFocus command was set to prior to the openform code).
    I had tried other calendar options - but none seemed to work, or had proved to complicated. This is simple yet effective - thanks!

  9. #9
    JohnSymbol is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2015
    Location
    Faversham, Kent, UK
    Posts
    2
    Just noticed a problem! When any day from 1 - 12 in the month is clicked, the date in the box shows the day equal to the selected month and the month equal to the selected day - e.g. if February is selected, and the day 12 clicked, then the date is displayed as 02 December!

    I've tried to find some errant code, but maybe you can solve this one - I'd be most grateful

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. Access 2010 Date Picker Replacement
    By pkstormy in forum Code Repository
    Replies: 3
    Last Post: 07-31-2012, 04:26 AM
  3. Replies: 5
    Last Post: 10-28-2011, 12:12 PM
  4. Date Picker
    By kelly in forum Database Design
    Replies: 3
    Last Post: 09-15-2011, 12:42 PM
  5. Date Picker
    By otisdaddy in forum Database Design
    Replies: 2
    Last Post: 07-01-2011, 12:30 PM

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