how to record changes in a form for comparison purposes...

[Susy]

Hi there!



I have this one form connected to this main table ("SalesActivities").

Some Fields in the "SalesActivities" table:
SalesActivityID
SalesActivityResponsible
SalesActitivyStatus ----->{Planned, Confirmed, Realised}


One of the fields in "SalesActivity" contains three possible status: {Planned, Confirmed, Realised}.

At one point, when entering the data the status can be "Planned". One or two days later, the status can change to "Confirmed" or "Realised" and so on...

At the end of the month, I would like to compare if a sales activity status moved from one status to another, or I would like to make a report of all salesactivities with the status "Planned", or "Confirmed", or "Realised"

Right now I just have to tables:

Table One:
"SalesActivities"
{SalesActivityID, SalesActivityResponsible, SalesActivityStatus}

Table Two:
"Status"
{StatusID, SalesActivityStatus}


Some tips on how I should approach this challenge (at least it is a challenge for me!)

I will have an EDIT_Click() button where I will allow Me.AllowEdits = True... but after that I have no idea how to record the changes... :-( not only I want to rewrite the Status, but record the time of the change at the "SalesActivityID" level.


Thanks in advance!

[pbaldy]

Search on audit trail. A report on a chosen status could be done with a parameter query or:

http://www.baldyweb.com/wherecondition.htm

[Micron]

I think you need a status history table (tblStatusHist)
{HistoryID, SalesActivity_FK, Status_FK, StatusDate}
where FK means foreign key from the related table. Something tells me you might be using a lookup field in your table:
One of the fields in "SalesActivity" contains three possible status: {Planned, Confirmed, Realised}.

[Susy]

Thanks for both suggestions. I still need to figure out how this works.