Show date picker in VBA

Dear experts -
This one has me scratching my head.
I want to show the date picker for a date field on a form under certain conditions, but not in others.
I see there is a property 'showdatepicker' available in VBA; apparently the values are 0 (=never) and 1 (=for dates).

I've tried setting these in multiple places (open event, load event, current event) and nothing seems to work. For what it's worth, I've tried changing the property and when I query the value in the immediate window it seems to have been changed to those above.

But the darn date picker still shows up in all cases.

If I go into the form and manually turn the setting to 'never' for the relevant fields, the date picker does not show up in any case.

Any thoughts?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

To hide a control, set it's Visible property to False.

Have this code inside the else clause of your "certain conditions" (meaning "not in the others").

Me.DTPicker1.Visible = False

Open in new window

Of course, it depends how you got your datepicker (I just got it from the MS controls), and may not even work if you got a specialised one that doesn't have the Visible property.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You don't want to hide the control (I assume), but rather to NOT allow users to use the datepicker associated with that control?

This code works for me:

Me.Text2.ShowDatePicker = 0

Text2 is bound to a Date field in my table. If I do that in the Current event of my form, the Date Picker is not shown. If I comment it out, the Date Picker is shown.

I haven't tried it in other events, but the syntax definitely works. I'm using Access 2013.

If that does not work, try creating a new, blank database, add a table with a Date field, and create a Form from that Table, and then use the ShowDatePicker code in that database. If it works there, then you've got troubles with your DB, and you need to perform maintenance. If it does NOT work in that new db, then be sure your machine is fully up to date regarding Windows and Office updates.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

terpsichoreAuthor Commented:
I'll award the points here - but the behavior is still anomalous for me.
If I view in FORM view, these properties seem to apply, but not in datasheet view.

The solution that worked for my purposes: I set the property to NEVER in the form and saved the form.
Then, in the Current event, I set the property to '1' - it only seems to apply to the form view. The Datasheet view is unaffected.

(I tested Scott's logic but turning one date field to show the picker and one not, and that works fine, just as he described.)

Thank you -
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
If I view in FORM view, these properties seem to apply, but not in datasheet view.
Ahh ... datasheet view. Yes, that would be much different than Form view, and I would not expect that toggle to work in datasheet view (since Access is essentially drawing the same control over and over, and cannot differentiate between distinct controls).
terpsichoreAuthor Commented:
thank you - I guess this gets to the root cause of the issue. I still would like a way to programmatically change this for ALL records in datasheet view, via VBA - let me know if you have a trick. otherwise, this is working ok now.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Continuous Forms view allows the toggle, and you could create one that looks very much like a Datasheet. If your form is complex, of course, it might not be worth the hassle to do that.
terpsichoreAuthor Commented:
thank you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.