Combo box - changes to underlying table

Scenario:

On MyForm, I have a combo box called cboApprovedBy. The
source is a table (tblApprovalAuth) with the fields:
ApprovedByID (autonumber) and ApprovedByName (text field -
for this posting only, I am combining what would normally
be the "Salutation" and "Last Name" fields). The idea is
to allow a user to select a name from the combo box
identifying an approval authority.

Let's assume ApprovedByID "99" is ApprovedByName "Ms.
Jones", and Ms. Jones has been an approval authority on
300 of 1000 records in the past 3 years. Now Ms. Jones has
gotten married and is now "Mrs Cole".

If I go into tblApprovalAuth and change "Ms. Jones"
to "Mrs Cole", all 300 database records previously
approved now become "Mrs Cole", resulting in
discrepancies between the ApprovedByName (in the database)
and the 'hard copy' approval signatures.

The only way I can think of getting around this is to
create a new ApprovedByName (Mrs Cole) in tblApprovalAuth.
Is there a better way around this?

Thank you for your time.

Denis

Denis

What is important to you? Do you want to show that ApprovedByID #99 has
approved 300 in the past three years, no matter if that person has changed
his/her name?

Or do you want to show that a particular name is the Approval Authority,
regardless of whether or not it's been the same person? (i.e., if you have
more than one John Smith working for you at some point in time, is it the
name, or the absolute ID?)

If you want to keep your db sync'd with your hard copy, you'll need to keep
track of the date ranges for names, or, you'll need to add names to your
record (not just ID).

--
Good luck

Jeff Boyce
Access MVP

Jeff,
Thank you for your reply. In fact, the important piece of
information we need is the name of the Approval Authority -
to match the hard copies. Thanks again.
Denis
-----Original Message-----
Denis

What is important to you? Do you want to show that
ApprovedByID #99 has
approved 300 in the past three years, no matter if that
person has changed
his/her name?

Or do you want to show that a particular name is the
Approval Authority,
regardless of whether or not it's been the same person?
(i.e., if you have
more than one John Smith working for you at some point in
time, is it the
name, or the absolute ID?)

If you want to keep your db sync'd with your hard copy,
you'll need to keep
track of the date ranges for names, or, you'll need to
add names to your
record (not just ID).

--
Good luck

Jeff Boyce
Access MVP

.