Can I limit record update?

[Joe]

Hi,
I’ve had great help from this group in the past and hope that someone might
be able to help me once again.

I have a subform Asset Assignments based on tblAssetAssignments (datasheet)
that records the history of assignment of assets to various employees. The
subform has textboxes with the fields AssignID, AssignmentDate,
AssignDescription, cboEmployee with linked textbox Department (record source
=cboEmployee.Column(2)) such that selection of the employee’s name
automatically populates the department name as allocated in the Employee
Form.

My problem is that whenever an employee is transferred to another department
all historical records of that employee update to reflect the latest
department. Is it possible to allow update of the department name in the
last recorded date of asset assignment and still retain the historical
record? If so, how would I go about it?

Many thanks in anticipation,

Joe

 

[Pictou]

Sounds like a database design issue. If department is identified in the
employee record and not in the historical records then you have no way to
tell what department to which the historical records belong.

 

[Bob Quintal]

Hi,
I’ve had great help from this group in the past and hope that
someone might be able to help me once again.

I have a subform Asset Assignments based on tblAssetAssignments
(datasheet) that records the history of assignment of assets to
various employees. The subform has textboxes with the fields
AssignID, AssignmentDate, AssignDescription, cboEmployee with
linked textbox Department (record source =cboEmployee.Column(2))
such that selection of the employee’s name automatically
populates the department name as allocated in the Employee Form.

My problem is that whenever an employee is transferred to another
department all historical records of that employee update to
reflect the latest department. Is it possible to allow update of
the department name in the last recorded date of asset assignment
and still retain the historical record? If so, how would I go
about it?

Many thanks in anticipation,

Joe

First of all, it seems to me that the depaqrtment is not relevant,
because 1) the asset movews to the new department with the employee,
or the asset gets transferred to another employee in the old
department. Having said that, you are the boss, so you know what you
are trying to accomplish.

First modify the table to store the dapartment ID. Run an update
query to set the department for all assets.
Then modify your subform combobox's afterupdate event to write the
deptID to the record.

 

[John W. Vinson]

My problem is that whenever an employee is transferred to another department
all historical records of that employee update to reflect the latest
department. Is it possible to allow update of the department name in the
last recorded date of asset assignment and still retain the historical
record? If so, how would I go about it?

Only by adding a DepartmentName field to the table, bound to a textbox on the
form. Rather than setting that control's Control Source to the =comboboxname
expression, you'll need to set it to the DepartmentName field, and "push" the
value into the control using the combo's AfterUpdate event:

Private Sub cboEmployee_AfterUpdate()
Me!txtDepartment = Me!cboEmployee.Column(2)
End Sub

Note that I'm suggesting storing the department name rather than the
departmentID - not sure if you have both, but departments do change names over
time and I think you'll want to store the *actual name* of the department as
of the time of the record.

There's probably no reliable way to populate this field for historical
existing records; you'll just need to run an update query updating it to the
employee's current department and then go back and manually fix up the errors.


John W. Vinson [MVP]

 

[Joe]

Sorry, I only just got back to follow up this discussion due to other
pressing tasks.

John, I read with interest your suggestion to track the record with the
'department' name. Whilst I understand the ideal is to try to establish a
normalised and efficient database I guess there are times when convention
needs to be stretched slightly in order to achieve the desired goal. Thanks
again for your input - I'm really grateful for your assistance.

Joe