How can my date picker on the main form be used for my sub-form records input?

I have a time input system with a form and sub-form. In the sample database attached - open frmEmployeeWeeklyInput - select employee "Dave" and select week of "Date" and this filters the date for the desired week for the records below in the sub-form.
In the sub-form below - how can I eliminate the weekly date or have it automatically entered using the select week date (txtWeekOf) on the main form?
I.E. we don't need individual dates for the week - how can this data be automatically entered into the record?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'm not quite sure what you're after. Looks like you have some form of "schedule", where you enter the number of hours for a task?

I don't think your tblEmployeeWeeklyInput is properly designed, but without knowing more about your goal it's hard to give good advice.
Dale FyeOwner, Developing Solutions LLCCommented:
cannot look at your database right now, but you might consider setting the default value for the WeekDate control in the subform to something like:

Default Value: = Forms!ParentFormName.txtWeekDate

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
In the BeforeUpdate event of the subform, copy the date from the main form.

Me.WeeklyDate = Me.Parent.txtWeekOf

PS - your schema is incorrect.  Each day should be a separate row.  Don't let presentation dictate your schema.  You will have a lot more work searching and reporting with this not normalized table than you would have if you normalized it.

I've attached a sample database that will help you get the presentation you want but still store the data correctly.  The db includes two examples.  One is monthly forecasting (which is most like what you need) and the other is expenses.  The monthly forecast form shows 12 columns, one for each month but the data is stored correctly with one month per row.  The form is made by joining several queries together.  This example should be easy to translate to 7 days for a week and your subform will look the same as it does now.

There is a power point presentation included.  Look at that first.  To implement, you will create 8 queries and write a couple of lines of code so that the correct date gets saved for each record.  You would need to take the week start date and that would be the Sunday date.  Then you would add the number of days necessary for each other week day.

Sorry, I just realized that the computer I am using doesn't have the sample.  I've posted it before.  Look for "bound denormalized form".  Ping me if you can't find it and I'll upload another copy tonight.
DJPr0Author Commented:
Thanks Dale - worked great!

Thanks Pat - will have to carve out some time and look into your suggestions for improvement.
The sooner you do it, the fewer changes you will need to make.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.