Record changed value of field (previous value)

[Hugh self taught]

Hi knowledgeable Guru's

I need to keep a history of partnership changes. The fields that change are
a date (date the partnership is created) & a number field (references a
partner in a master table) which come from a table of partnerships. I use the
afterupdate event to write a new record in a history table when the
partnership has a partner change. There are other references to the
partnership table that I also record but those are from current data on the
form so that's easy. I can't seem to work out how to keep the date of the
previous change in this partnership as well as the index number of the
previous partner. The date picker is used to change the date & the partner is
a cbo box.

Your pointers on how to achieve this would be greatly appreciated.

Cheers
H

 

[Jeff Boyce]

You've defined a "how", not so much a "what".

I'll 'interpret' what you are describing to mean that you want a way to keep
a history of partners in partnerships.

If that's your situation, there may be other ways to get it done...

--

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Hugh self taught]

Hi Jeff,

Thanks for responding. Yes it is partnerships as in dance couples. The male
is the dominant partner so I want to keep track of every time he changes his
partner. I save his index data & her index data as well as the new partner's
index. That data is all stored in a table "History". That way I can later
generate a report on partnership activity.

When the partnership is created I record the date it starts. Then when the
female partner is changed I record the new date but I need to record the old
date as well.

Your suggestions please
Cheers
H

 

[Mike Painter]

Hi Jeff,

Thanks for responding. Yes it is partnerships as in dance couples.
The male is the dominant partner so I want to keep track of every
time he changes his partner. I save his index data & her index data
as well as the new partner's index. That data is all stored in a
table "History". That way I can later generate a report on
partnership activity.

When the partnership is created I record the date it starts. Then
when the female partner is changed I record the new date but I need
to record the old date as well.

Your suggestions please
Cheers

A table of Masters and a table of partners would be one way.
A form, subform would be used to display all the partners with the current
one at the top of the subform.
Adding a new partner would be done from a combo box with a form popped if
the new person was not in the list.

You might also want to consider a self referencing table in the event that
Frank and Ernest had it right...
http://www.reelclassics.com/Actresses/Ginger/ginger-article2.htm

 

[Hugh self taught]

Hi Mike,

Thanks for this. I have a table with the partnerships where each individual
is referenced by their PK in the master table.

What I did in the interim to try get around losing the date & female partner
data when I change the form data was to make a small hidden form with 2 text
boxes that loads with the partnership form & references the fields on it.
Then my RUNSQL statement refers to the data on each form to get the correct
data into the history table.

Does that all make sense to you? Do you foresee any problems going that route?

Many thanks
Hugh

 

[Mike Painter]

I'm going to assume that
1. you have no aversion to seeing all the previous partners at one time
with the current one at the top of the list and possibly conditionally
formatted to show a different color, etc.
2. You have quite a few partners and that they move around between the male
dancer.
3. That an old partner may rejoin a master.

Masters Table.
MasterID, Name, address, etc, etc.
Build a form with all the data you want to show and use a combo box to find
a given person.

PartnersInformation Table
Same as above with PartnerID, etc.

MasterPartner Table
ID, Date, MasterID, PartnerID.

MasterPartnerQuery
ID, Date, MasterID, PartnerID, PartnersInformation.PartnerName, etc

Build a form on the query, set it to datasheet view.
Drag it onto the Master form and make it pretty.
Set the master child relationship to MasterID in both forms.

At this point you will never loose old information.
Adding a new record amounts to entering the date (could default to current
date) and the PartnerID in the subform.

If that works for you then you can add a combobox in the subform to lookup
the partnerID by name and sort the query by reverse order to keep the
current one on top.