Top Contributors

Looking for beta testers

I decided to write a DatePicker replacement for the DateTimePicker control, since Microsoft no longer support it, the various solutions I found on the internet were nowhere near satisfying.

 - Reference DatePicker.d.accde
 - On a form, create a TextBox, with a Picture right next to it.
 - From VBA, instanciate a DatePicker object by calling the createDatePicker function, it require a TextBox and a Picture control as arguments.
 - Once you no longer need the instance, you must manually destroy it, by explicitly calling the Dispose method.

 - The form must not be modal.
 - Controls must be in the Detail section
 - The DatePicker class take control of the textbox and picture provided as arguments, so avoid messing up with these.
 - It also take control of the form's current event Handler, as well as the section Detail's mouseDown event Handler.
 - The class only support choosing a date, nothing was done about time.

 I provide 2 files:
 - a Test database, wich is a usage sample.
 - DatePicker.d.accde, a debug version, wich in case of error, will provide an error log in the execution window.

 Comments welcome.
View Previous CommentsLoad All Comments (11)
Rank: Prodigy

Expert Comment

Dale Fye2018-10-02 10:34 AMID: 2268182
Can you compile it in 2007?  Would be far more useful for those who are working in 2007, 10, or 13.

Also, can it be used in both 32 and 64 bit Office, or do you have a separate version for each of those two versions of Office?
Rank: Sage

Author Comment

Fabrice Lambert2018-10-02 11:16 AMID: 2268195
Nope, I don't have the luxury to own multiple version of access.

Guess I'll upload the ACCDB
Rank: Prodigy

Expert Comment

Dale Fye2018-10-03 07:33 AMID: 2268593

I've tested it and it appears to work as expected.
Rank: Sage

Author Comment

Fabrice Lambert2018-10-05 01:26 PMID: 2269732
Don't be afraid to run whataver tests you want, torture the Library at your liesure ;).

Also, it would be great if more ppl could test it.
Rank: Genius

Expert Comment

Bitsqueezer2019-05-13 05:31 AMID: 2472238
Hi Fabrice,

I've tested your database, first with the initial ACCDE where immediately (with A2007/German version/32Bit on W10 64Bit) when opening the test database the error appears that the VBA project is corrupted. I removed the reference to the ACCDE and inserted it again (to make sure it is no path problem) with the same result.

Then I removed it and used the DatePicker.accdb you provided above and inserted the reference to it and when opening the form nothing happened, no reaction on any click. In the VBA immediate window there is a "13 - Type mismatch" error. After some single step debugging it is this row of code:

Cast_IEventGenerator(mCalendar).Register Me

Open in new window

It's the same error when I try to comment that out and use the following line of code:

Cast_IEventGenerator(mMonthSelector).Register Me

Open in new window

It seems to be a problem with the Interface as the error is fired in this line of code:

Set Cast_IEventGenerator = Item

Open in new window

in the module "Cast" in the procedure "Cast_IEventGenerator".

For me it looks as if the Interfaces are implemented as usual so it seems to be because "Item" is a Variant and not a direct object.

I implemented these new functions for test purpose and after that the module works:

Public Function Cast_IEventGenerator2(ByVal Item As Form_Calendar) As IEventGenerator
    Dim ErrorLogger As ErrorLogger
    Set ErrorLogger = CreateErrorLogger(ProjectName, ModuleName, "cast_IEventGenerator")

    Set Cast_IEventGenerator2 = Item
End Function

Public Function Cast_IEventGenerator3(ByVal Item As Form_MonthSelector) As IEventGenerator
    Dim ErrorLogger As ErrorLogger
    Set ErrorLogger = CreateErrorLogger(ProjectName, ModuleName, "cast_IEventGenerator")

    Set Cast_IEventGenerator3 = Item
End Function

' and in DatePicker:

    Set mCalendar = New Form_Calendar
    Cast_IEventGenerator2(mCalendar).Register Me
    Set mMonthSelector = New Form_MonthSelector
    Cast_IEventGenerator3(mMonthSelector).Register Me

Open in new window

Maybe MS changed that behaviour for Interfaces in newer versions of Access but my A2007 doesn't want to use variants as objects.

After successful testing I changed the code of the procedure to this:
Public Function Cast_IEventGenerator(ByVal Item As Variant) As IEventGenerator
    Dim frm As Access.Form
    Dim ErrorLogger As ErrorLogger
    Set ErrorLogger = CreateErrorLogger(ProjectName, ModuleName, "cast_IEventGenerator")

    On Error Resume Next
    Set frm = Item
    On Error GoTo 0
    If Not frm Is Nothing Then
        Set Cast_IEventGenerator = frm
        Set Cast_IEventGenerator = Item
    End If
End Function

Open in new window

Which successfully avoided the type mismatch error.

When trying out your date picker I also saw some problems in behaviour:
  • open the dropdown (datepicker appears) and then click i.e. on the form's window header. The datepicker disappears (correct) but when trying to open it again it reacts only on the second click, means, it did'nt get the status of the datepicker that it is currently invisible again.
  • clicking the arrow buttons in the datepicker (up/down for year or left/right for month) reacts only if you click slow. If you try to change that faster it reacts only on each second click (seems that the double click event may be the reason)
  • Clicking on the month name opens the month name dropdown, but if you select one the datepicker is closed and the date is entered into the field. Instead it should stay on the datepicker and show the selected month.
  • Clicking on the year doesn't open a list of years. Hard to set the date to i.e. 1975 as birth date of someone using the date picker (without entering the date manually.
  • The possibility to enter the date into the field manually is blocked. So it would take a lot of time to select 1975...
  • The day names are displayed in French in the datepicker, should be a list of day names of the current selected Windows region, there are Windows API functions which allow to return a list of names in the local region (where the months names are correct in (in my case) German).
  • The datepicker doesn't allow to use the keyboard to select a date.


Rank: Sage

Author Comment

Fabrice Lambert2019-05-14 02:28 PMID: 2472595
Thank you for your feedback, I'll take a look.

Concerning the issue with previous versions of Access, afraid I can't do much as I do not own multiple version, thus can't guarantee backward compatibility (don't think anyone can, new technology can run old codes, new code can't be run on old technologies).

- F.Lambert