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

  • 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


    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


    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