Date Picker

[David44Coder]

I'd like to put a date picker on a Form but having trouble with the Access one.
The Form was Unbound, but a text box bound to empty table with a date field was added.
But I don't want to see the text box, or click it, just have the calendar icon available. Is that possible ?
Also can't see how to change the year. Is that a limitation of this control ?
Is there a workaround or solution? Thanks.

 

[bob fitz]

...But I don't want to see the text box, or click it, just have the calendar icon available. Is that possible ?

What do you want to see after a selection has been made?

 

[David44Coder]

Hi Bob, What I envisaged was the calendar would disappear after selecting a date. Then I use that date to do other stuff within the Form.
At the moment a date is typed into a text box, but this (might) be more elegant?
PS Even after a lot of searching I can't see how to change the Year, if that's not possible I can't use it.

 

[isladogs]

There are many alternative date pickers available which are far better and which make it easy to change the year
This is my version:

Better Date Picker

A better date picker that works in both 32-bit & 64-bit Access and uses no ActiveX controls. The latest version automatically adjusts the form size depending on screen resolution and optionally translates the form captions to match the Office language. It also includes a week number feature

www.isladogs.co.uk

 

[KitaYama]

@isladogs You've done a great job there.
We've been using your date picker since last month. I Just wanted to thank you for offering this utility and tell you we had to add a piece of code.

Our database has been used in a multi-language environment. We added WeekdayName function (abbreviated parameter set to on) and MonthName function (abbreviated parameter set to off) to On_Load event of your form to change the day captions and month combo box.

I'm not a vba expert by any means and I'm not sure if it will work for every language, but for now it suits us just fine.
I thought you may want to know and consider to add it if it can be used on a wide range of languages.

Thanks again for your nice job.

 

[arnelgp]

i also have my own version.
i wonder if it is "Intl" ready?

 

[KitaYama]

i also have my own version.
i wonder if it is "Intl" ready?

It's Saturday 21:27 here, Let me test yours as soon as I'm back to work on Monday. I'll test it and will let you know.
thanks.

 

[arnelgp]

i adjusted my code, because the i want the form to be "embeddable" (Form2).

 

[KitaYama]

@arnelgp I just tested your version on a Japanese system.
Several points.

1. Comparing to Access native date picker, your version has a lag time while opening. (@isladogs's version has the same issue). Access default date picker is much faster on opening.
2. As isladog's version, It doesn't open at mouse position. It's window centered. ( I can manage this). Recent monitors are wide screen and you don't want user's mouse move far to the center of the screen and back.
3. Though the months are listed correctly in combobox, the default selected month is in English. Clicking the buttons to move to next or previous month will choose English months. I mean it will change to Jan, Fab, etc instead of local months ( I can manage this too.)
   
   

I'll do more tests on other languages on Monday.
Please don't take it as criticizing. I just thought you may want to know.

 

[arnelgp]

you check the "Month" combobox,

 

[isladogs]

@KitaYama
As requested, I've updated my Date Picker as requested to show the day & month names in the regional language
Tested in French, German, Spanish, Greek and Japanese and all looks OK to me

I hadn't realised until now that Japanese/Chinese & several other languages represent months as a number followed by the month symbol e.g. April is '4 Month' ... as in post #9 for month names.

Two international versions attached
DatePicker_v2206 - days shown from Sun to Sat
DatePickerM2S _v2206 - days go from Mon to Sun

Please can you test & let me know if its OK (or not)

The new versions have also been posted to my website:

Better Date Picker

A better date picker that works in both 32-bit & 64-bit Access and uses no ActiveX controls. The latest version automatically adjusts the form size depending on screen resolution and optionally translates the form captions to match the Office language. It also includes a week number feature

www.isladogs.co.uk

BTW it would be easy enough to change the date picker form so it appears just next to the textbox being populated

EDIT: 8 June 2022
The attached versions have a couple of bugs.
Please instead use the updated version 1.4 attached to post #25

 

[KitaYama]

@KitaYama
As requested, I've updated my Date Picker as requested to show the day & month names in the regional language
Tested in French, German, Spanish, Greek and Japanese and all looks OK to me

I hadn't realised until now that Japanese/Chinese & several other languages represent months as a number followed by the month symbol e.g. April is '4 Month' ... as in post #9 for month names.

Two international versions attached
DatePicker_v2206 - days shown from Sun to Sat
DatePickerM2S _v2206 - days go from Mon to Sun

Please can you test & let me know if its OK (or not)

The new versions have also been posted to my website:

Better Date Picker

A better date picker that works in both 32-bit & 64-bit Access and uses no ActiveX controls. The latest version automatically adjusts the form size depending on screen resolution and optionally translates the form captions to match the Office language. It also includes a week number feature

www.isladogs.co.uk

BTW it would be easy enough to change the date picker form so it appears just next to the textbox being populated

Million thanks.
I will test this new version on our PCs and will report back in 2,3 hours.

 

[KitaYama]

@isladogs
I tested your latest version. It was exactly the same as the edits I had done.

Just FYI, maybe I have to add two other edits I've done to your version:

1. Here in Japan, it's kind of a common sense to put a link or credit for any free utility we use. So I have deleted Selected Date & Current Date textboxes and added a link to your site inststead.
2. We prefer to be able to see and select dates from the end of previous month or the first days of next month. Just like Access default date picker or Windows Date. So I modified DrawDateButtons subroutine. Now it's what I have :

It's just a matter of personal perference, not a problem.
To have above result, I have deleted btn.Visible=False and added the following.

    ' btn.Visible = False
    OutOfCurrentMonth = True
    OutOfDate = DateAdd("d", I - 1, DateSerial(Year(myDate), Month(myDate), 1))
    btn.Caption = Day(OutOfDate)
    btn.Tag = btn.Caption
    btn.Visible = True
    If I > MonthLength And Weekday(OutOfDate) = vbSunday Then VisibleFlag = True
    If VisibleFlag = True Then btn.Visible = False

I have also added these variables to the top of the subroutine.

    Dim OutOfCurrentMonth As Boolean
    Dim OutOfDate As Date
    Dim VisibleFlag As Boolean

 

[isladogs]

Thanks for the additional info/ideas ... and for adding the link to my website

 

[isladogs]

@KitaYama
Just tried to send you a private message (conversation) but it seems you don't allow that feature...

Before reading your reply this morning, I had updated my DatePicker webpage & have a YouTube video on this item as well

Two more questions:
1. I see your week is displayed from Sat to Fri. Is that standard in Japan and specified in Windows settings? Perhaps I need to code a generic version that pulls that info
2. Out of interest, how did you code the different forecolour on the out of month days?

If you are willing to email me your version, it might save me some time in further coding if I decide to include these extra features
See my signature line for email link

Thanks in advance

 

[KitaYama]

@KitaYama
Just tried to send you a private message (conversation) but it seems you don't allow that feature...

Before reading your reply this morning, I had updated my DatePicker webpage & have a YouTube video on this item as well

Two more questions:
1. I see your week is displayed from Sat to Fri. Is that standard in Japan and specified in Windows settings? Perhaps I need to code a generic version that pulls that info
2. Out of interest, how did you code the different forecolour on the out of month days?

If you are willing to email me your version, it might save me some time in further coding if I decide to include these extra features
See my signature line for email link

Thanks in advance

@isladogs Here's how I did it.
I'm not an expert in VBA and I'm sure you can do it much smarter.

 

[KitaYama]

@isladogs
forgot to answer your question.
No, we have Sunday at the first column in our calander. Your code shows Saturday as the first column. I have corrected this in our database:

This is from your database posted above #11

    Me.Label19.Caption = WeekdayName(7, True)
    Me.Label20.Caption = WeekdayName(1, True)
    Me.Label21.Caption = WeekdayName(2, True)
    Me.Label22.Caption = WeekdayName(3, True)
    Me.Label23.Caption = WeekdayName(4, True)
    Me.Label24.Caption = WeekdayName(5, True)
    Me.Label25.Caption = WeekdayName(6, True)

I have corrected to

    Me.Label19.Caption = WeekdayName(1, True)
    Me.Label20.Caption = WeekdayName(2, True)
    Me.Label21.Caption = WeekdayName(3, True)
    Me.Label22.Caption = WeekdayName(4, True)
    Me.Label23.Caption = WeekdayName(5, True)
    Me.Label24.Caption = WeekdayName(6, True)
    Me.Label25.Caption = WeekdayName(7, True)

 

[isladogs]

Which of the two versions did you use as one of them is Sun to Sat .
The version you posted shows as Mon to Sun on my PC.

I will have a look at your code later. Thanks

 

[KitaYama]

The left one

 

[KitaYama]

@isladogs

There's a difference between Access date picker and windows Date.
Windows Date has a fixed 6 Rows. Access data picker differs depending on how many weeks in month.

At present we are discussing it which one to choose. The attached version is your file with a little modification to use fixed number of rows.

Access Date picker for June

Windows type date for June

You can check the attachment.