MS Access How can I have a drop down in a form control the user input in the datasheet below?

I have a project/task sample database attached. The form frmEmployeeWeeklyInput includes a datasheet of projects and a Select Employee drop down. How can this drop down control the datasheet input for the employee & filter the records from the tblEmployeeweekyinput table?

I.E.
-User selects name from Select Employee drop down.
-Users records from the tblEmployeeweekyinput are displayed below in the datasheet (frmEmployeeweeklyinputSub)
-When user inputs a new record there name is recorded in the WEEKLYEMID field in the tblEmployeeweeklyinput table.

One of the goals here is for the user not have to select his/her name for each record input.
TimeInputTest.accdb
DJPr0Asked:
Who is Participating?
 
PatHartmanConnect With a Mentor Commented:
I fixed many of the problems but not all.
1. I added RI
2. I bound the main form to tblEmployee but locked all the fields so they cannot be updated.  I did this because of the relationships you have defined in tblEmployeeMulti.  You have created a pathological connection because you have parallel paths to tblEmployeeweeklyinput.  More on this later.
3. Once the main form was bound, I could set the master/child links on the subform so that they would work to propagate the FK when new records were added.
4. I fixed both the filters by changing the RecordSource queries on both the main and subforms and adding .Requery to force them to update.
5. I removed the dead code from the main form.
6. I changed the method of picking a week.
7. I changed many of the control names so they would make sense.

Problems remaining.
1. TaskID does NOT belong in the projejts table.  This relationship is backwards.  ProjID belongs in tblTasks.  OR if the relationship is many-many, then you need an additional junction table between tblProjects and tblTasks.  
2. Because of the problem with tblProjects, the relationships of projects and tasks to the input table are both incorrect.
3. tblEmployeeweeklyinput violates first normal form.  You have a repeating group - 7 week days.  There should be a separate table with one row per weekday, per task which leaves the input table with only the first 3 fields and the last field.  
4. Because of the relationship of projects to employees, you need to change the RowSource queries of both the project and the tasks to account for this.  As it is, you can add any task or any project to the input table and that is not valid based on your schema so you need to fix it with the Combo's RowSources.
TimeInputTest_ModifiedByPat.accdb
0
 
PatHartmanCommented:
The copy of the database you posted in a different thread works.
TimeInputTest---Copy--2-.accdb
0
 
DJPr0Author Commented:
I need the employee inputted in the tblEmployeeweeklyinput table.

The version you have will not display separate records per employee. 2 employees could input on the same record which is not desired.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
DJPr0Author Commented:
Thanks Pat,

3. tblEmployeeweeklyinput violates first normal form.  You have a repeating group - 7 week days.  There should be a separate table with one row per weekday, per task which leaves the input table with only the first 3 fields and the last field.

- Create one table with the 7 days of the week
- How do I combine the tables for employee input? (for the datasheet in frmEmployeeWeeklyInput)
0
 
PatHartmanCommented:
For reporting, you can simply use a crosstab which will give you all the days as columns but for data entry, each row should be for a single date (day) and a single project.  So if you worked on 5 projects on Monday, you'll have 5 rows for Monday.

If you are feeling adventurous, you can try to adapt my "Bound denormalized form example" for this purpose.  It translates very well to what you want to do.  Using it, you would have one row on the form for a project with 7 columns for time entry.  The data will actually be stored as I described earlier.  You will need a small amount of code behind the controls because the actual date f the week day will need to be calculated.  The form header will have either week ending or week starting date and using that anchor, the date of the weekday must be calculated.

I'll attach the database so you can see how it works.  The relevant example would be expenses by month which you would translate as projects by day.

Sorry, I'm getting some kind of "stream" error and cannot upload the file so I attached a PPT that explains how it works.  If you think you understand the concept (the PPT includes the queries for each month (day in your case) as well as the code behind the month(day) control so you see how the non-visible value is calculated), then send me a PM asking for "bound denormalized sample" so I'll know what you are talking about.
Bound-Denormalized-Forms.pptx
0
 
DJPr0Author Commented:
Pat,

I could not open your .pptx file - I receive an error due to corruption.

My customer for this project wants to input in a datasheet view, with a view of filtering weekly via a date selector, this may be a little inefficient however I would like to satisfy the customer. Should I keep my existing format for the (tblEmployeeweeklyinput) table or would you suggest another method?
0
 
PatHartmanCommented:
The download opened for me but I saved it as a PDF in case you have an old version of PP.

Leaving the design as you have it will result in analysis and reporting issues later on.  Take a look at the  pdf and see if you understand the concept.  It really takes very little code to implement and you will have fewer queries than I did because I was doing an example for 12 months.

Usually customers can be convinced by cost if you can show them the difference.  Remind the Boss that he still will get to look at reports that replicate the old data entry forms because you can make them very easily with crosstab queries. The only thing that would change would be the actual data entry form.  The data entry form is the smallest part of the problem.  I can see where it sure looks simple but until you get on with the rest of what the app has to do, you won't realize the impact of the improper design.  This is a case of 7 columns vs 1 and here's an example of two different queries that do the same thing.

Select EmpID, ProjectID,  Nz(Mon,0) + Nz(Tue,0) + Nz(Wed, 0), +Nz(Thrus, 0) + Nz(Fri, 0) + Nz(Sat, 0) + Nz(Sun,0) As WeekHours
From tblHours Where WeekStartDT = Forms!yourform!WeekStartDT

Select EmpID, ProjectID, Sum(DayAmt) As WeekHours, Avg(DayAmt) As AvgDailyHours
From tblWeeks Left Join tblHours Where tblHours.WeekStartDT = Forms!yourForm!WeekStartDT

Guess what you have to do to the first query to make an average?  Just try it.  I'll give you a hint - you need to count the number of days where there are hours worked and use that as the divisor.  With the second query, access works this out.  But it is important that you understand the ramification of the non-normalized schema in order to sell the client.  Of course, if you understand my example, you can create the normalized tables but still give him an unnormalized data entry form.  But trust me.  You really need to add the average to the first query to fully understand the impact of a repeating group on even simple analysis operations.
Bound-Denormalized-Forms.pdf
0
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.