Reference PK in mutliple tables

[tbrogdon]

I have a main table, tlProduction which has a 3 field composite PK.
The db brances in 2 different directions from this table and each
branch is linked to tblProduction on the same 3 fields. The problem is
that I need to be able to input data into each of the "branched"
tables (tblEmployeeProduction and tblProductionOperation) which I know
how to do for one at a time but once the intial record is created in
tblProduction and data is input into one of the two "branched" tables,
Access won't allow me to input data into the second "branched" table
using the same data from tblProduction again.

So I guess my question is how can I reference an existing composite
primary key in multiple records in a second table?

I am placing this in this forum because I am convinced it will take
some coding.

Thank you,

Tim

 

[John W. Vinson]

I have a main table, tlProduction which has a 3 field composite PK.
The db brances in 2 different directions from this table and each
branch is linked to tblProduction on the same 3 fields. The problem is
that I need to be able to input data into each of the "branched"
tables (tblEmployeeProduction and tblProductionOperation) which I know
how to do for one at a time but once the intial record is created in
tblProduction and data is input into one of the two "branched" tables,
Access won't allow me to input data into the second "branched" table
using the same data from tblProduction again.

So I guess my question is how can I reference an existing composite
primary key in multiple records in a second table?

I am placing this in this forum because I am convinced it will take
some coding.

No code is needed at all, actually, as the problem is stated. You could use a
Form based on tblProduction with two Subforms - one for each child table. The
Master Link Field and Child Link Field properties of the subform would consist
of the three fields separated by semicolons. This will let each child table
"inherit" the values in the main form.

What error are you getting? How are you doing the data input? What is the
Primary Key of each child table?

John W. Vinson [MVP]

 

[tbrogdon]

Hi John,

What error are you getting?

"The changes you requested were not successful because they would
create duplicate values..."

How are you doing the data input?

Via 3 forms: frmProduction (inputs data for ProductionDate, Dept, and
Shift into tblProduction); frmSetEmpHours (sets employees default
hours spent in a given JobFunction in tblEmployeeProduction); and
frmProdDetails (form/subform combination - the master is an autoform
of tblProduction and the subform is an autoform of
tblProductionOperation - it is in attempting to enter data into this
form that I receive the error message above).

What is the Primary Key of each child table?

tblEmployeeProduction: PK is a composite of ProductionDate, Dept,
Shift, and EmployeeID
tblProductionOperation: PK is a surrogate key (autonumber) - ProdOpID

Thanks,

Tim

 

[John W. Vinson]

Hi John,


"The changes you requested were not successful because they would
create duplicate values..."

So? Sounds like the changes you requested were not successful because they
would create duplicate values. Is there some way you can check what's there
(open a table datasheet window) and see if it's in fact already got the values
you're inserting?

Via 3 forms: frmProduction (inputs data for ProductionDate, Dept, and
Shift into tblProduction); frmSetEmpHours (sets employees default
hours spent in a given JobFunction in tblEmployeeProduction); and
frmProdDetails (form/subform combination - the master is an autoform
of tblProduction and the subform is an autoform of
tblProductionOperation - it is in attempting to enter data into this
form that I receive the error message above).

If you've already entered a record into frmProduction then you certainly
should not be trying to enter it again in frmProdDetails; if fact I don't see
what point there would be in having a separate frmProdDetails, as opposed to a
simple mainform based on tblProduction and two subforms (perhaps on tab pages
if they take a lot of screen space) for the other tables.

tblEmployeeProduction: PK is a composite of ProductionDate, Dept,
Shift, and EmployeeID

And are these four fields the Master/Child Link Field?

tblProductionOperation: PK is a surrogate key (autonumber) - ProdOpID

John W. Vinson [MVP]

 

[tbrogdon]

So? Sounds like the changes you requested were not successful because they
would create duplicate values. Is there some way you can check what's there
(open a table datasheet window) and see if it's in fact already got the values
you're inserting?

All of the tables are empty. he first data that is entered is
ProductionDate, Dept. and Shift into tblProduction. The user
(supervisor) needs to enter data into tblEmployeeProduction at the
beginning of a shift. This is currently done via frmSetEmpHours which
runs an Append query (EmployeeIDs and JobFunctionIDs) and also an
Update Query which calculates a default number of hours worked per
employee by JobFunctionID and Shift. This number of hours can then be
modified by the supervisor at the end of shift if a particular
employee worked more or less hours and also if they changed
JobFunction mid-shift.

If you've already entered a record into frmProduction then you certainly
should not be trying to enter it again in frmProdDetails; if fact I don't see
what point there would be in having a separate frmProdDetails, as opposed to a
simple mainform based on tblProduction and two subforms (perhaps on tab pages
if they take a lot of screen space) for the other tables.

Forgive my ignorance please, but a little more background may be
helpful:

Remember, that currently by the beginning of the shift an entry has
been made in tblProduction in order to populate tblEmployeeProduction.
Later, the supervisor must make an entry for every batch run of parts
that occurred over his shift (which is again defined in tblProduction
and linked to tblProductionOperation by ProductionDate, Dept., and
Shift). The fields that are populated in tblProductionOperation
include ProductionDate, Dept, Shift, Operator1 (EmployeedID),
Operator2 (EmployeeID), WorkstationID, PartID, QtyRun, etc.

I don't want the supervisor to have to input ProductionDate, Dept, and
Shift in frmProdDetails for every batch run of parts. I am hoping to
be able to retrieve that instance from tblProduction and populate the
appropriate fields in tblProductionOperation.

I may describe this techincally incorrect but it occurred to me that
perhaps what I need to do is, on frmProduction, when the user clicks
the command button, to do 2 things: 1) populate tblProduction and 2)
run a Make table query that would use all of the fields from
tblProductionOperation with ProductionDate, Dept, and Shift
prepopulated and use this query as the record source for
frmProdDetails which would also have command button. THAT command
button would run another Append query which would append the "made"
table to tblProductionOperation and then run a Delete query to delete
the temporary table. Even if this is not a good way to do it, does it
help you see what I'm trying to do?

And are these four fields the Master/Child Link Field?

No. Only ProductionDate, Dept., and Shift. EmployeeID is added to the
other three in tblEmployeeProduction to create a unique index WITHIN
tblEmployeeProduction.

 

[John W. Vinson]

All of the tables are empty. he first data that is entered is
ProductionDate, Dept. and Shift into tblProduction. The user
(supervisor) needs to enter data into tblEmployeeProduction at the
beginning of a shift. This is currently done via frmSetEmpHours which
runs an Append query (EmployeeIDs and JobFunctionIDs) and also an
Update Query which calculates a default number of hours worked per
employee by JobFunctionID and Shift. This number of hours can then be
modified by the supervisor at the end of shift if a particular
employee worked more or less hours and also if they changed
JobFunction mid-shift.
ok....


Forgive my ignorance please, but a little more background may be
helpful:

Remember, that currently by the beginning of the shift an entry has
been made in tblProduction in order to populate tblEmployeeProduction.
Later, the supervisor must make an entry for every batch run of parts
that occurred over his shift (which is again defined in tblProduction
and linked to tblProductionOperation by ProductionDate, Dept., and
Shift). The fields that are populated in tblProductionOperation
include ProductionDate, Dept, Shift, Operator1 (EmployeedID),
Operator2 (EmployeeID), WorkstationID, PartID, QtyRun, etc.

I don't want the supervisor to have to input ProductionDate, Dept, and
Shift in frmProdDetails for every batch run of parts. I am hoping to
be able to retrieve that instance from tblProduction and populate the
appropriate fields in tblProductionOperation.

And cannot this be done using a subform, inheriting the ProductionDate, Dept
and Shift from the values on the mainform? There is certainly no need to enter
them again.

I may describe this techincally incorrect but it occurred to me that
perhaps what I need to do is, on frmProduction, when the user clicks
the command button, to do 2 things: 1) populate tblProduction and 2)
run a Make table query that would use all of the fields from
tblProductionOperation with ProductionDate, Dept, and Shift
prepopulated and use this query as the record source for
frmProdDetails which would also have command button.

OW. No. You should *never* need to run any MakeTable queries here! What new
table are you proposing to create??? I don't see why you would even need an
Append query.

THAT command
button would run another Append query which would append the "made"
table to tblProductionOperation and then run a Delete query to delete
the temporary table. Even if this is not a good way to do it, does it
help you see what I'm trying to do?

No. It looks like going all the way around Robin's barn to do something that a
Subform Control does for you automatically with no code and no new tables.

No. Only ProductionDate, Dept., and Shift. EmployeeID is added to the
other three in tblEmployeeProduction to create a unique index WITHIN
tblEmployeeProduction.

That's why you're getting the error. You're trying to add multiple records per
shift to tblEmployeeProduction; if you have a unique index on those three
fields you can add one and only one record.


John W. Vinson [MVP]

 

[tbrogdon]

Hi John,

OK. I definitely see your point and am pretty sure that I have made
things much more complicated for myself than they need to be
obviously. (I am trying to buld this thing at home with a 1 year old
and a 3 year old at my feet. I know excuses, excuses.)

So all I need to do is set up the form/subform. Would you suggest
setting the OnOpen property of the main form to GoToRecord,,acNewRec?

My concern is that a supervisor will input partial data in
tblProductionOperation - which is OK - BUT what approach do I use for
them to recall that recordset for later editing?

Is it as simple as a form that takes Production.ProductionDate,
Production.Dept, and Production.Shift as parameters joined on
tblProductionOperation as a query? WIll this allow the user to not
only edit existing records in tblProductionOperation but also add new
ones?

Thank you for your patience. It is much appreciated!

Tim

 

[John W. Vinson]

Hi John,

OK. I definitely see your point and am pretty sure that I have made
things much more complicated for myself than they need to be
obviously. (I am trying to buld this thing at home with a 1 year old
and a 3 year old at my feet. I know excuses, excuses.)

So all I need to do is set up the form/subform. Would you suggest
setting the OnOpen property of the main form to GoToRecord,,acNewRec?

Use the Load event instead.

My concern is that a supervisor will input partial data in
tblProductionOperation - which is OK - BUT what approach do I use for
them to recall that recordset for later editing?

Put combo boxes on the form (unbound) to select the record. Default the date
combo to Date(), and put a command button with code to jump to the new record.

Is it as simple as a form that takes Production.ProductionDate,
Production.Dept, and Production.Shift as parameters joined on
tblProductionOperation as a query? WIll this allow the user to not
only edit existing records in tblProductionOperation but also add new
ones?

Again... YOU DON'T NEED THIS!

It is simply *not that difficult*.

You do not need any append queries.
You do not need any make table queries.
You do not need to add any records to any table.

The subform *will do that for you*.

Try using the built in features of Access. Unlike some other languages (Visual
FoxPro, dBase) a whole lot of functionality comes built in and does not need
any programming AT ALL.

Thank you for your patience. It is much appreciated!

<g> Keeping it under control... I think... ooops, shouted a bit up there,
sorry!

John W. Vinson [MVP]

 

[tbrogdon]

Hi John,

I am taking ALL of your advice and yes, I have been rightly accused of
over-thinking things from time to time!

You kept it well under control considering my persistence to be
difficult!

Thank you again. I am also going to take your advice about utilizing
the tab control and house the main formand 2 subforms in one window.

Believe it or not you have taught me a lot on this - and several other
occasions.

As always - thank you,

Tim