Add new data from Field1 into Field2 (& keep old Field2 data histo

[AmyNYC]

Not sure if this is possible, but would like to automatically add new data
from Field1 into Field2, but keep data currently in Field2 as a history.

Field1 = StatusCurrent (Memo Field)
Field2 = StatusHistory (Memo Field)

Example:
1. User enters data in StatusCurrent and saves the record. Data gets date
stamped upon saving, so would for example have --> 10/22/04 - Development
complete.

2. Data entered in StatusCurrent (including date stamp) is automatically
saved to StatusHistory. However, would like whatever was previously entered
in StatusHistory to remain there, so I can have a log of status such as the
following:
10/22/04 - Development complete.
10/2/04 - Design complete.
10/1/04 - Requirements analysis complete.

If anyone knows an easier way to go about this let me know.

 

[Randy]

Not sure if this is possible, but would like to automatically add new data
from Field1 into Field2, but keep data currently in Field2 as a history.

Field1 = StatusCurrent (Memo Field)
Field2 = StatusHistory (Memo Field)

Example:
1. User enters data in StatusCurrent and saves the record. Data gets date
stamped upon saving, so would for example have --> 10/22/04 - Development
complete.

2. Data entered in StatusCurrent (including date stamp) is automatically
saved to StatusHistory. However, would like whatever was previously
entered
in StatusHistory to remain there, so I can have a log of status such as
the
following:
10/22/04 - Development complete.
10/2/04 - Design complete.
10/1/04 - Requirements analysis complete.

If anyone knows an easier way to go about this let me know.

AmyNYC, could you please explain the current scenario you are using to
accomplish this? Sorry, but you were not clear or specific, and a lot of
possibilities came into my mind. Without further details I'm not sure, but
every time you update a project status, it should be a separate unique
entry, not an update. This would be a layout for what I understand you need
to build:

[TABLE PROJECTS]
ID
ProjectName
StartDate
EndDate
Description
NumOfEmployees

[TABLE PROJECT-HISTORY]
xID <-------------------Project ID
EventStatus
DateTimeStamp
EmployeeName

Also you may want to take a look at Microsoft Projects
http://www.microsoft.com/project

-Randy

 

[AmyNYC]

Hi Randy, Guess a very general way to put this question is:
1. Is it possible to copy data from Field1 to Field2 whenever Field1 is
changed?
2. If the first point is possible, then can Field2 also retain a history/log
of Field1 changes? In other words, can Field2 simply add changes without
deleting history? The reason for this is because I want to have Field1 state
the current status and have Field2 have a history of status entered in Field1.

Not sure if this is possible, but would like to automatically add new data
from Field1 into Field2, but keep data currently in Field2 as a history.

Field1 = StatusCurrent (Memo Field)
Field2 = StatusHistory (Memo Field)

Example:
1. User enters data in StatusCurrent and saves the record. Data gets date
stamped upon saving, so would for example have --> 10/22/04 - Development
complete.

2. Data entered in StatusCurrent (including date stamp) is automatically
saved to StatusHistory. However, would like whatever was previously
entered
in StatusHistory to remain there, so I can have a log of status such as
the
following:
10/22/04 - Development complete.
10/2/04 - Design complete.
10/1/04 - Requirements analysis complete.

If anyone knows an easier way to go about this let me know.

AmyNYC, could you please explain the current scenario you are using to
accomplish this? Sorry, but you were not clear or specific, and a lot of
possibilities came into my mind. Without further details I'm not sure, but
every time you update a project status, it should be a separate unique
entry, not an update. This would be a layout for what I understand you need
to build:

[TABLE PROJECTS]
ID
ProjectName
StartDate
EndDate
Description
NumOfEmployees

[TABLE PROJECT-HISTORY]
xID <-------------------Project ID
EventStatus
DateTimeStamp
EmployeeName

Also you may want to take a look at Microsoft Projects
http://www.microsoft.com/project

-Randy

 

[Randy]

Hi Randy, Guess a very general way to put this question is:
1. Is it possible to copy data from Field1 to Field2 whenever Field1 is
changed?

In Access that can only be accomplished on a form using the AfterUpdate
Event of the field (Field1).

Ex. Me.[Field2] = Me.[Field1]

2. If the first point is possible, then can Field2 also retain a
history/log
of Field1 changes? In other words, can Field2 simply add changes without
deleting history? The reason for this is because I want to have Field1
state
the current status and have Field2 have a history of status entered in
Field1.

You should really re-consider my first assessment. I can see they way you
are trying to implement this, and it is bad design. I wouldn't build based
on a field that carries all the history. You should really have a separate
table for the changes made to Field1. Please review my original suggestion
at the very bottom of this post. However if you want to continue with your
current design use the following code in the AfterUpdate event of the field
(Field1) on a form:

Dim PrevID as string
Dim PrevHistoryData as string
Dim tmp as variant
Dim tmpField1 as string
Const HISTORYSEPARATOR = " + " ' string used to separate the history
changes.
Const HISTORYMAXSIZE = 255 ' maximum size of Field2
Const EMPTYFIELD1 = "[EMPTY]" 'used if Field1 is

' for this code I use tmpField1 instead of Field1
tmpField1 = nz(Me.[Field1], "")
If Trim(tmpField1) = "" then
Me.[Field1]=EMPTYFIELD1
tmpField1=EMPTYFIELD1
End If

' this locates the last record (ID) added to the table
' excluding the current record
tmp=DMax("[ID]", "[MyTable]", "[ID] < " & Me.[ID])
If isnull(tmp) then tmp = 0
PrevID = tmp

' now lets retrieve its history data for the previous record ID
tmp = DLookup("[Field2]", "[MyTable]", "[ID] = " & PrevID)
If isnull(tmp) then tmp = ""
PrevHistoryData = tmp

' then it combines the current change (Field1) and the previous changes
(PrevID)
tmp = tmpField1
If PrevHistoryData <> "" then tmp = tmp & HISTORYSEPARATOR & PrevHistoryData

' truncate history if it is more than the field can retain
If len(tmp) > HISTORYMAXSIZE then tmp=Left(tmp,HISTORYMAXSIZE)

Me.[Field2] = tmp

It is understood that ID is an Autonumber (no-duplicates) PrimaryKey on
MyTable. And that Field1 and Field2 are string data type.

-Randy

.....Without further details I'm not sure, but
every time you update a project status, it should be a separate unique
entry, not an update. This would be a layout for what I understand you
need
to build:

[TABLE PROJECTS]
ID
ProjectName
StartDate
EndDate
Description
NumOfEmployees

[TABLE PROJECT-HISTORY]
xID <-------------------Project ID
EventStatus
DateTimeStamp
EmployeeName

Also you may want to take a look at Microsoft Projects
http://www.microsoft.com/project

-Randy