What is the best way to handle a status history field 1

[Pack10]

I have a text field (Notes) that is used to show the history of the record as it changes from status to status along the transaction route. The user selects the status from a combo box it displays in the value in the Notes text box with the date and value of the status, for example.

Notes:
1/26/2012 - With Analyst
1/30/2012 - With Manager
1/30/2012 - Revisions Accepted
1/30/2012 - With Manager
1/30/2012 - Released

The issue I have is if the user accidently selects a status that he does not want, that value still displays in the text box.
I have a line of code which reads ....
txtNotes = txtNotes & date & " " & me.txtStatus
So, the text box grows with each status, but all the accidental ones are there also.
What is the best design for something like this.
Should I write the note info to a separate table and make a subform out of it.

 

[dhookom]

I would create a related table where each status update is an individual record.

Duane
Hook'D on Access
MS Access MVP

 

[MazeWorX]

also use a cmd button to do the update where you verify the intended result with prompt confirmation before writing it to the related table

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[Pack10]

Thanks all I will go with your idea

 

[Pack10]

Used the related table and a listbox...worked fine.

thanks all