Subform within subform dilemma

[TigerNoire]

Hi,

I have a database that I am trying to generate a work call form in which all data regarding hours worked is saved to a show. The basic idea:

Show and Call - Main Form
Job - Subform 1
Employee - Subform 2

The design is supposed to allow easier viewing. The main form is unbound. The job subform then associates the job to the call. These parts are working fine.

My problem comes when I try to add an employee to the job as numerous people can work one job. I keep getting an error saying that an associated record needs to be present for a call. I check the relationship - 1 to many so that 1 call can go many places and I don't know what can be wrong - ideas???

Val

 

[TheAceMan1]

How are ya TigerNoire . . . . .

Are the forms [blue]Job & Employee[/blue] linked with [purple]Link Master/Child Properties[/purple]?

If they are then you [purple]can't enter Employees in a blank job[/purple]. The job has to be entered first.

If they are not, then you'll have to devise a way to [purple]add the JobID to the employee[/purple] entered, or link as above.

See Ya! . . . . . .

 

[willir]

Hi Val

Hmmmm...

numerous people can work one job .... 1 to many so that 1 call can go many place

You may want to check your design and relationships.

Unless an employee only works on ONE call, and will never work on another call ever again, you really have a many-to-many relationship. You need an intermediary or joiner table. For example...

tblEmployee
EmployeeID - primary key
EmployeeLN - last name
EmployeeFN - first name
...etc

tblCall
CallID - primary key
CallDate
CustomerID - foreign key to employee for customer, EmployeeID
JobID - foreign key to Job table (not shown)
...etc

tblCallRep
CallID - primary key to foreign key to Call table
SupportRepID - foreign key to the Employee table, EmployeeID

Now say it with data...[tt]
EmployeeID EmployeeLN EmployeeFN
[COLOR=yellow blue]1[/color] FixIT John
[COLOR=red white]2[/color] AnswerMan Roy
[COLOR=green yellow]3[/color] Geru Ace
4 HelpMe Firsty
5 Snaffu MissTake

CallID CallDate CustomerID (aka EmployerID)
[COLOR=white purple]1[/color] 08/25/2004 4
[COLOR=white green]2[/color] 08/26/2004 5

CallID SupportRepID (aka EmployerID)
[COLOR=white purple]1[/color] [COLOR=yellow blue]1[/color]
[COLOR=white purple]1[/color] [COLOR=red white]2[/color]
[COLOR=white green]2[/color] [COLOR=yellow blue]1[/color]
[COLOR=white green]2[/color] [COLOR=green yellow]3[/color]
[/tt]

Mr FixIT and AnswerMan worked on the first call placed by Firsty HelpMe.
Mr FixIT and Geru worked on the second call placed by MissTake Snafu.

Now, you have the Job Subform working.

Your SupportRep / Employee subform is built using the joiner or intermediary table tblCallRep. The CallID in the subform links to the CallID in the main form. A combo box based on the SupportRepID (aka EmployeeID) to the Employee table.

Per AceMan, you can now use the Link Child and Link Master fields to ensure the linkage.

Richard