Basic form and subform question

[Lostguy]

Hello!

I want to make a main form where you enter the date and location and
task, and then a continuous subform where you select which employees
did that task.

I have:

tblTask: TaskIDpk and TaskName
tblEmployee: EmpIDpk and LName
tblEmpTask: EmpTaskIDpk, EmpIDfk, TaskIDfk, TaskDate, Task Location.

I start up the forms wizard, drag the date, location, and last name
fields to it. It tells me I need to set up relations first. So I
relate the Task and Employee pk's to the fk's with a Join Type 1 and
don't click ref integrity. I retry the forms wizard with those same
fields.

The problem is that it always want to put the Employee LName in the
main form and the Date/Location fields in the subform, but I need it
the other way around.

Why does it think that the LName is always the mainform? Does it have
something to do with how the relations are set up? What am I doing
wrong/


I appreciate the help, because I have been stumped for hours on this.

VR/

Lost

 

[Jeanette Cunningham]

Hi Lostguy,
the form setup should work if you set up like this-->
Main form fields based on tblTask only.
Subform fields based on tblEmpTask.
Use a drop down to select the employee for each task in the subform.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Lostguy]

All,

Thanks for the help, but I think that maybe I confused everyone. I
want the form/subform setup to look like this:

MainForn
Enter the date the task happened (1-1-09, etc)
Enter the location where the task happened (Home, Office, School)
Enter what the task was (Cleanup the suppy room, balance the budget,
etc.)

Continuous SubForm
Select all the people who did that task (could be 1 person; could be
50)

If the same people did that same task on the same date, but at a
different place, there should be different records for each of those
than if they all did it at the same place.

I have the table setup that I described earlier (which could be
wrong). This is what I am having trouble setting up.

HTH

VR/Lost

 

[Lostguy]

Ken,

While I am digesting this all, let me ask this:

a) if I want to print a list of tasks completed per person, it could
look like this:

Bob:

1-1-09 Balanced Budget at Home
1-2-09 Balanced Budget at Work
1-3-09 Balanced Budget at Home

This example is what I don't want (The repeat of the Task by the same
person at the same place, but on a different day). So, I can either
enter this in the database and then have a message or something that
says "Bill already Balanced the Budget on 1-1-09 at Home. If he
repeated this, go back to the original date and update the date."

So, all the database should be tracking is the most recent time that a
task was performed. I don't care about the historical stuff or that it
was done 50 times. Just the most recent.

Or should the user just enter each and every task (even if it was
repeated) and then when I do the report, just query to output just the
most recent date. This would keep all the historical performances of
the task, but the user would be redundantly entering the information
since all I need to see is the most recent performance of the task.


b) In this 4 table method, they should all be ref int/cascade/cascade
with Join Type 1?


I appreciate your help. I want to bang this out this weekend and there
is no way I can do it on my own, so you and this group are lifesavers.

VR/
Lost

 

[Lostguy]

Sir,

Here are the steps I did. Please review this to see if there is
anything else I should do (I changed "Execution" to"Do" for brevity.)

1) 4 tables

tblTask: TaskIDpk (autonumber), TaskDesc(text)
tblTaskDo: TaskDopk (autonumber), TaskIDfk (number), TaskDate (Date/
Time), TaskLocation (text)
tblEmpTaskDo: EmpTaskDoIDpk (autonumber), EmpIDfk (number), TaskDofk
(number)
tblEmployee: EmpIDpk (autonumber), EmpLName (text)

2) Enter some data:

tblEmployee EmplLName: Smith, Jones, Hill, etc.
tblTask TaskDesc: Bowling, Fishing, Camping, Wash car, etc.


3) In relationship window, drag all the similarly named pk's to fk's.
I didn't check ref in/cascade/cascade. All are Join type 1....(?)

4) Create form using wizard:
From tblEmpTaskDo: TaskDate, TaskLocation, and TaskIDfk. This is the
mainform.
From tblEmpTaskDO: EmpIDfk. This is the subform.
View by tblTaskDo

5) In the form's Design View, Change the TaskLocation, TaskIDfk, and
EmpIDfk from text boxes to combo boxes.
For the Task IDfk combo box, change the row source to Query: tblTask:
TaskIDpk and TaskDesc, Column Count 2, Column Widths 0;2, and Bound
Column 1
For the EmpIDfk combo box, do the same (change the row source to
Query: tblEmployee: EmployeeIDpk and EmpLName, Column Count 2, Column
Widths 0;2, and Bound Column 1

6) Use the Query wizard and select EmpLName, TaskDesc, TaskDate,
TaskLocation.

This is looking good, Ken. Here's what I still need help on.

a) The "Bill-Bowling-1/1/09-Home" duplication thing when I run the
query.
b) The whole indexes thing. That went right over my head. What fields
from what tables do I create the index on? Would that stop the
duplicate events thing?
c) Do I check the ref/cascade/cascade? Is Join Type 1 OK?

A big Thank You to you! VR/Lost

 

[Lostguy]

OK.

Here's what else I/we did:

7. Created a tblDepartment (DepartmentIDpk and DepartmentName). Put
the DepartmentIDfk in the tblEmployee. Created a form off of
tblEmployee where the user can enter the employee's last name and then
department (via a combobox created like all the other combos (column
count 2, etc.)

8. Created a tblLocation (LocationIDpk and LocationDesc). Put the
LocationIDfk in the tblTaskDo. Now I can use that for the combobox
like is done for all the others.

9. Clicked Enforce Ref Int for all of the relationships. Did not click
the Cascades. They are all still Join Type 1

10. Created a Task Index (Opened tblTaskDo in Design View, clicked the
lighting bolt (Index) toolbar icon. In the popup box, typed in "Task"
as the Index Name and in the next column, selected TaskIDfk. Then on
the next two rows, I left the Index Name blank and then selected
TaskDate and LocationIDfk. Back up to the row with the Index name
(Task), for the Index Properties, I changed Unique to Yes.)

AFA the "Bill - Bowling" thing, I don't need to know when they started
the task or how long it took (That's good stuff, but not for this
application.) I just want to know when it was done. So the TaskDate is
the date the task was finished.

So, right now, on the report, it is showing two "Bill Bowling" events.
So do I understand that the best way to handle this is just to let the
user enter this date, and then on the query for the report, just put
Max (Date) or something to display just the latest date of that task?
I was just thinking that we have some tasks (CPR class, etc.) that
have 25 people. So Secretary 1 could enter that task (enter the date,
dropdown the task desc, dropdown the location, then use the subform to
dropdown the 25 employees). But Secretary #2 may not see/know what
Secretary #1 did (she did not first look at the report), and she types
in all the same information for the 25 employees. Now on the report,
25 emmployees have two entries of "CPR 1/1/09" IF there was some way
that the database would alert Secretary #2 that that task had already
been entered, she would know not to enter it.


??

Thanks for all your help. I think that this last thing is the final
hurdle. Another question: If this Access database had been set up with
only 3 tables (and the one not decomposed), yes, it would have had
redundant data, but then could you use that multi-field index method
to prevent entry of the duplicate rows?

BTW, I have tried to be specific about what steps I took to make it
easier for you to catch the things I am doing wrong and maybe help
somebody else like me. If you have any more suggestions, I am all
ears. This is good stuff!

VR/Lost

 

[Lostguy]

Sir,

Continuing the saga:

11. Added an Index called "EmpTaskDoIndex" to the tblEmpTaskDo on
fields EmpIDfk and TaskDofk.

12. Added an error message to the main form's On Error Event

Private Sub Form_Error(DataErr As Integer, Response As Integer)
MsgBox "You may have entered a Date-Location-Task combination that
has already been entered previously (i.e. 1/1/09 Home Check-in).
Delete the fields in this new record, and then add any new Employee
names to that previous entry."
Response = acDataErrContinue
End Sub

13. I had to uncheck the Ref Int for the Relationship between
TaskDoIDpk in the tblTaskDo and the TaskDoIDfk in the tblEmpTaskDo.
When I tried to delete records in the mainform, it said that I
couldn't because it required deletion of related records. So, I
unchecked that relationship's ref int and I don't get that error
anymore (?)



Last thing (I think), then I will get out of your hair:

I would like to set up one of the reports by employee with their
respective department, last name, and then a list of their tasks done
and the dates.

So I have in the report Section Header: Department
In the LName header: LName
In the Detail Section: TaskDesc, TaskDate.

I tried moving the page break from the toolbar around to different
places, but it never works out.

What I see on the first page:

Headquarters
Smith
Fishing 1/1/09

But then Bowling 1/2/09 for the same employee (Smith) is at the top of
the next page.

I can't see a way to keep all each own employee's task records
together on their own single page (so they can't see each other's
business.)

??

VR/

Lost