Code for Date Picker Use AND User Date Entry

[JessicaGomo]

I have a textbox on my form that is for "week of", always a Monday. There is vba to populate the appropriate Monday when the user selects something other than Monday via the date picker. This code is in the on change event. However, this is not friendly to a user entering a date in this field.

• I did try after update event, but that requires user to leave the "week of" field. This is not acceptable in this instance, there are other form fields that change as a result of this "week of" value.
• I also tried evaluating the length of the text or else exiting the code. However, I was expecting the date being returned from the date picker to always be 10 positions, but for March 3rd, it would return 3/3/2015...which is only 8 positions.

Any help or related info. is very much appreciated!!

 

[JHB]

It is not clear for me what exact you want, so can you explain it a little deeper?

 

[Solo712]

I have a textbox on my form that is for "week of", always a Monday. There is vba to populate the appropriate Monday when the user selects something other than Monday via the date picker. This code is in the on change event. However, this is not friendly to a user entering a date in this field.

• I did try after update event, but that requires user to leave the "week of" field. This is not acceptable in this instance, there are other form fields that change as a result of this "week of" value.
• I also tried evaluating the length of the text or else exiting the code. However, I was expecting the date being returned from the date picker to always be 10 positions, but for March 3rd, it would return 3/3/2015...which is only 8 positions.

Any help or related info. is very much appreciated!!

Why not "Before Update" ? What are the criteria for "user friendly" ? Are you using the date function Weekday to determine the day of the week ? You can easily calculate the value of any preceding Monday as

mydate=DateAdd("d", -(Weekday(mydate,vbMonday) -1), mydate)

Best,
Jiri