Answered by:
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?Tuesday, November 22, 2016 3:39 PM
Answers
-
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
- Edited by Lawrence Ellefson Tuesday, November 22, 2016 5:02 PM
- Marked as answer by Nalin Abeysinghe Tuesday, November 22, 2016 6:55 PM
Tuesday, November 22, 2016 4:55 PM
All replies
-
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
Tuesday, November 22, 2016 4:33 PM -
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
Tuesday, November 22, 2016 4:34 PM -
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
- Edited by Lawrence Ellefson Tuesday, November 22, 2016 5:02 PM
- Marked as answer by Nalin Abeysinghe Tuesday, November 22, 2016 6:55 PM
Tuesday, November 22, 2016 4:55 PM -
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
Tuesday, November 22, 2016 6:57 PM