I have 3 tables which form the basis for my db:
tblProduction w/ a composite key of ProductionDate, Dept, and Shift.
Every bit of information derived from every query is based on this
composite key.
I also have tblEmployeeProduction w/ ProductionDate, Dept, Shift,
EmployeeID, JobFunction, HoursMachine, and HoursAssembly. This table
is linked to tblProduction on ProductionDate, Dept, and Shift.
Likewise it is indexed and unique on ProductionDate, Dept, Shift, and
EmployeeID. The purpose of this table is to capture the hours worked
in a particular JobFunction per an instance of ProductionDate, Dept,
and Shift so the value of ProductionDate, Dept, and Shift will be
repeated for each employee of that Dept and Shift on any particular
day. Currently I have frmSetEmpHours which is a combination of an
Append query and an Update query to populate the table. This is the
code that runs from the Click event on that form:
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryEmpShiftHours"
DoCmd.OpenQuery "qrySetJFHours"
MsgBox "Employee shift information is updated."
DoCmd.SetWarnings True
This works great so far. However, I have to make an entry in
tblProduction before I utilize frmSetEmpHours or I get a warning
concerning key violations which I understand because of the composite
PK.
On top of that, I have tblProductionOperation which captures the
actual production information related to each employee for every
instance of ProductionDate, Dept, and Shift (i.e., PartID, QtyRun,
etc.).
It seems to me that I should have th user make an intial entry in
tblProduction for ProductionDate, Dept, and Shift from a command
button on a form and ALSO run the code above to prepopulate
tblEmployeeProduction with the default data. Later in the shift, from
a different form (frmInputProdData - bound to tblProductionOperation),
the user inputs production data for the shift. I have a form/subform
to do this already which works great IF the user inputs composite key
data in IT before entering data in tblEmployeeProduction.
In other words, I need to be able to input data into both
tblEmployeeProduction and tblProductionOperation which are both linked
to tblProduction on 3 fields and the data entry will take place at
different times so how do I code the CLick event for two different
forms to allow me to link and return the values from the composite key
of tblProduction to use as input into both tblEmployeeProduction and
tblProductionOperation?
|
Similar Threads
