Fill in weeks worth of dates from Mondays date

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have searched many of the discussions but have not found a solution, so I
apologize if this is an easy solution, but would appreciate any direction.

I have a form with fields for every day of the week. I want to have the
users enter Monday's date and then the other fields fill in for the rest of
the week automatically, dependent on Mondays date (which will be selected
from a calendar)

Example:

DtMonday 09/03/07 (Enter this date in Mondays field)
DtTuesday 09/04/07 (this date is entered in Tuesday automatically)
DtWed 09/05/07 (this date in Wednesdays, and so on.

Please let me know if you need more info to assist.
Thanks
 
Do you want to store these values in your database or just display them on
the form?

Steve
 
Ok. In the AfterUpdate event of DtMonday set the values of the other fields.
For example:

Private Sub DtMonday_AfterUpdate()
DtTuesday = DateAdd("d",1,Me.DtMonday)
DtWednesday = DateAdd("d",2,Me.DtMonday)
DtThursday = DateAdd("d",3,Me.DtMonday)
DtFriday = DateAdd("d",4,Me.DtMonday)
End Sub

You may have to first validate that the user entered a valid date. You could
further validate the entry to ensure it is a Monday. You should also lock the
other controls that are being populated automatically.

Steve
 
Steve, thanks for helping me out.
I tried the code below. When I enter Mondays date, nothing happens in the
other fields. The fields just stay blank. Any suggestion on why this would
occur.
Thanks again.
JAD
 
No reason the posted code should not work, but I do have a couple of comments.
1. You do not need to store all 5 dates in your table. If you have the
Monday date, you can calculate the others and your form should show the other
dates.

2. It is always best to qualify your objects so neither you nor Access will
get confused about where they belong. Instead of:
DtTuesday
I would use
Me.txtTuesday
The Me. says it is on the current form.
txtTuesday means it is a text box control (dt usually means a date/time
memory variable.

Now, here is how I would do it. I would put the expression in the Control
Source properties of Tuesday through Friday

=DateAdd("d",1,[Forms]![MyFormName]![txtMonday]) For Tuesday
And, of course, the appropriate number of days for the rest of the week.
Notice also the reference to the control for Monday. In this context, it
will not understand Me. or Me!
 
I suspect you just pasted the code into your module?

Go to the combo's property sheet and select [Event procedure] in the After
Update property and try it again...

Steve
 
Steve,
Thanks for all your help.
I did not cut and paste, but I did not have the form specified. I did what
Dave suggested and between both of your guys' help, it works great.
Thanks. This is going to save my users alot of time.
JAD

SteveM said:
I suspect you just pasted the code into your module?

Go to the combo's property sheet and select [Event procedure] in the After
Update property and try it again...

Steve

JAD said:
Steve, thanks for helping me out.
I tried the code below. When I enter Mondays date, nothing happens in the
other fields. The fields just stay blank. Any suggestion on why this would
occur.
Thanks again.
JAD
Click to expand...
Click to expand...
 
Dave,
Thanks for your response. This works great. I appreciate the direction.

Klatuu said:
No reason the posted code should not work, but I do have a couple of comments.
1. You do not need to store all 5 dates in your table. If you have the
Monday date, you can calculate the others and your form should show the other
dates.

2. It is always best to qualify your objects so neither you nor Access will
get confused about where they belong. Instead of:
DtTuesday
I would use
Me.txtTuesday
The Me. says it is on the current form.
txtTuesday means it is a text box control (dt usually means a date/time
memory variable.

Now, here is how I would do it. I would put the expression in the Control
Source properties of Tuesday through Friday

=DateAdd("d",1,[Forms]![MyFormName]![txtMonday]) For Tuesday
And, of course, the appropriate number of days for the rest of the week.
Notice also the reference to the control for Monday. In this context, it
will not understand Me. or Me!

--
Dave Hargis, Microsoft Access MVP


JAD said:
Steve, thanks for helping me out.
I tried the code below. When I enter Mondays date, nothing happens in the
other fields. The fields just stay blank. Any suggestion on why this would
occur.
Thanks again.
JAD
Click to expand...
Click to expand...
 
Back
Top