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?
Dale FyeConnect With a Mentor Commented:
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
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.
PatHartmanConnect With a Mentor Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.