How to Disable certain days in the Date Time Picker Control 6.0 ActiveX control in Microsoft Access 2016

Question

In my system we are giving dates (appointments) for surgeries. User (one who gives appointments) needs to pick dates from DT Picker for surgeries. We don't do surgeries on public holidays. Therefore those days should be disabled being selected in DT Picker. I got a tblHolidays including those holidays. How can I customize DT Picker so the user can only pick the dates which are not holidays?

Answer 1

I doubt you can with the built-in DT Picker and would recommend using a Form based DT Picker which you can easily automate to suit any of your needs. See: http://www.devhut.net/2010/06/10/ms-access-calendar-date-picker-control/ for a few alternative DT Pickers that you could implement.

---

Daniel Pineault, 2010-2016 Microsoft MVP Professional Support: http://www.cardaconsultants.com MS Access Tips and Code Samples: http://www.devhut.net

Answer 2

On a side note, I found the following: https://msdn.microsoft.com/en-us/library/system.windows.controls.datepicker.blackoutdates(v=vs.110).aspx but have no clue if it can be implemented in Access.  Many advance functionalities are often disabled in Access, so you'd have to test it out.

---

Daniel Pineault, 2010-2016 Microsoft MVP Professional Support: http://www.cardaconsultants.com MS Access Tips and Code Samples: http://www.devhut.net

Answer 3

You can't disable specific dates that I know of. Date Picker is a built-in control. You can, however, test to see if the date picked using the picker is in your table, and if it is, then send a message to the user that it is a holiday and set the date text box to null so the user will need to pick a different date. Say you have a field in tblHolidays by the name of 'Holiday' and the name of your text box field is 'SurgeryDate'. In the 'SurgeryDate' text box bound to the 'SurgeryDate' field on which the date picker is active on your form, create vba in the After Update Event such as the following:

Me.Recalc

If DCount("Holiday","tblHolidays", "[Holiday]=Forms![Your Form Name Here]![SurgeryDate]") > 0 Then

Me.SurgeryDate = Null

Msgbox("The date you selected is a holiday. Please select a different date.")

End If

Answer 4

Dear Lawrence Ellefson,

Thank you so much for your reply. This is what I was looking for the past two days. You have given a simplified way. Thank you very much again