Calendar Control

[Johnny Drama]

I apologize if I'm posting this in the wrong location, but I'm not sure where else to post it.

I'm building a form that will have a date field on it. I want the date to always be the first day of the week, regardless of what the user selects from the date picker. Example, if the user were to open the calendar with the date picker and selected 10/5/2011, I would want the date field to be populated with 10/2/2011. Is there anyway to control this?

 

[Mr. B]

Take a look at this post:
http://www.tek-tips.com/faqs.cfm?fid=3103

You should be able to use the

"FirstDayofWeek:([DateField] - Weekday([DateField]))+ 1"

statement in the AfterUpdate event of the control of your date to change the selected date to the correctly calculated date.

 

[vbaInet]

... and as an addendum to Mr. B's good advice, if you want the start of the week to be Monday, just change the 1 to 2.

For the Calendar control, I think the AfterUpdate doesn't fire so you will need to use the Change event of the control in this form:

If IsDate(Me.[COLOR=Red]TextboxName[/COLOR]) Then
    Dim myDate As Date

    myDate = (Me.[COLOR=Red]TextboxName[/COLOR] - WeekDay(Me.[COLOR=Red]TextboxName[/COLOR])) + 1
    If Me.[COLOR=Red]TextboxName[/COLOR] <> myDate Then
        Me.[COLOR=Red]TextboxName[/COLOR] = myDate
    End If
End If

 

[Mr. B]

Thanks, vbaInet, for point this out. As usual, you are quite correct in pointing out the use of the On Change event to execute code after the user has selected a date using the date picker. That event is a very good choice for where to use the code. In most cases using the On Change event to execute code after the user has selected a date using the date picker may very well be the best choice.

vbaInet is also correct that the After Update event does not fire when the selection is made using the date picker. However, the After Update event does fire when you exit the text box if a change has been made to the data.

So, you really have some options. Using the On change event will cause the code to be executed each time a change of any kind is made to the data in the control. If you notice, the code is designed in a way so that only when a valid date value has been entered will the code to calculate the FirstDayOfTheWeek be executed. Using the On Change event, the value that appears in the text box will immediately be the date returned by the calculation and that may be what you are wanting.

On the other hand, if you used the After Update event, the date provided by your user would remain as provided by the user until the user moved the focus away from the text box control. When the user moved the focus off of the control, the date provided would immediately be updated to be the results of the calculated value.

You see, it just depends on when you want the results to be displayed.

As with so many things in Access, there are many ways to accomplish the same thing, but with a ever so slightly different user experience.

 

[Johnny Drama]

Hey guys,

thanks for the help. Mr. B's post is for a query, this is going to a table so the code provided in the link doesn't work. I'll have to fiddle with it.

I also tried vbaInet's code in the Change and After Update event, but no dice. It just populated the field with what ever date was selected. I'll need to muddle through and fiddle a little.

 

[vbaInet]

As with so many things in Access, there are many ways to accomplish the same thing, but with a ever so slightly different user experience.

Very true!

@Johnny Drama: Can you show us exactly what you've tried?

 

[Johnny Drama]

Hey vbaInet,

For testing I simply created a db with a table and a form. The table has two fields: WeekEnding and Report, with WeekEnding being a date field.

Then I created a form that has source data from the table. The form has two fields: a text box that is bound to WeekEnding and a text box bound to Report.

For the WeekEnding field I set the On Change property to run the code you provided below, substituting "TextboxName" with "WeekEnding". Once it didn't work with the code in On Change, I set the After Update property to use the same code. Didn't work either time.

 

[Johnny Drama]

Hey vbaInet,

For testing I simply created a db with a table and a form. The table has two fields: WeekEnding and Report, with WeekEnding being a date field.

Then I created a form that has source data from the table. The form has two fields: a text box that is bound to WeekEnding and a text box bound to Report.

For the WeekEnding field I set the On Change property to run the code you provided below, substituting "TextboxName" with "WeekEnding". Once it didn't work with the code in On Change, I set the After Update property to use the same code. Didn't work either time. Oh, BTW, I'm doing this in Access 2007

 

[vbaInet]

Let's see your db and we'll tell what's going on.

 

[Mr. B]

Johnny Drama,

Did you name the controls on your form the same as the name of the field to which they are bound?

It appears to me that you have used the name of the fields in the code when you should be using the actual name of the controls.

 

[Johnny Drama]

Hey guys,

Attached my test DB.

 

[vbaInet]

See attached.

By the way, you changed the name of control and didn't ensure the event was still active. It's always good to check all your via the property sheet event list to ensure that when you click on the elipsis button it takes you to the event. Sometimes it may show [Event Procedure] but it's not linked to the right event.

 

[Johnny Drama]

I'm still pretty new to all of this, so it doesn't surprise me that the event wasn't active. Thanks for your help on this. The sample you provided works like a charm!

 

[vbaInet]

Glad to help!

That was not the only problem. I had to add a few bits to the code too.