Copy records from certain fields to another field IF new record created

[spawnss via AccessMonster.com]

Hello everyone,

I'm preparing redline (drawings that implemented on site with changes)
database for our department. We are receiving redlines different dates. Here
is my question: if a user wants to enter new date (Redline date) for a
redline that we received before, how can I copy old date to another field
(old redline date (in subform, in same table))? I need this so I can track
old records/received dates.

Thank you,
Regards

 

[John W. Vinson]

Hello everyone,

I'm preparing redline (drawings that implemented on site with changes)
database for our department. We are receiving redlines different dates. Here
is my question: if a user wants to enter new date (Redline date) for a
redline that we received before, how can I copy old date to another field
(old redline date (in subform, in same table))? I need this so I can track
old records/received dates.

Thank you,
Regards

DON'T use separate fields for the historical dates. That way lies madness! If
you're tracking the current record's date and the previous one, someday you'll
need the one before that, then the one before THAT, ad infinitum.

Instead use a table of revisions; each time you get a new redline date, *add a
new record* to this table.

 

[spawnss via AccessMonster.com]

I cannot create new records for each date. We usually receive one drawing
only 3-4 times with different dates. If can save previous one that's works
for me too.

I found below code but it is not saving old date. When I close form old date
is disappearing.

Private Sub From_Site_Click()
Dim fromsite, subform, stLinkCriteria As String

subform = "REDLINES SUBFORM"
stLinkCriteria = "[ID]=" & Me![ID]

DoCmd.OpenForm subform, acNormal, , , acFormEdit, acHidden, stLinkCriteria

If [Forms]![REDLINES]![From Site] > 0 Then
[Forms]![REDLINES SUBFORM]![Old From Site Date].Value = [Forms]![REDLINES]!
[From Site].Value
[Forms]![REDLINES SUBFORM]![Old From Site Date].Requery
Else
DoCmd.GoToControl [Forms]![REDLINES]![From Site]
End If

End Sub

Hello everyone,

[quoted text clipped - 7 lines]

Thank you,
Regards

DON'T use separate fields for the historical dates. That way lies madness! If
you're tracking the current record's date and the previous one, someday you'll
need the one before that, then the one before THAT, ad infinitum.

Instead use a table of revisions; each time you get a new redline date, *add a
new record* to this table.

 

[Mike Painter]

You most certainly can create a new record for each date, but you don't have
to.
If you use a relational model then you will have a simple to maintain set of
tables that will allow easy report and form designs and be easy to maintain
with little or no code.
E.G. A form with a subform dropped on it requires you to enter, at most, a
master/child relationship and eliminates the need for the code I've left in.

Private Sub From_Site_Click()
Dim fromsite, subform, stLinkCriteria As String

subform = "REDLINES SUBFORM"
stLinkCriteria = "[ID]=" & Me![ID]

DoCmd.OpenForm subform, acNormal, , , acFormEdit, acHidden,
stLinkCriteria

If you don't you will be writing code to solve your problems and sooner than
later run into a problem that will either be beyond your ability to code and
or create other problems because of the code.

 

[spawnss via AccessMonster.com]

You mean entering old date manually? That's OK for me but how about other
users that has permission data input? They can forget this point. That's why
I'm trying to copy that record to another field automatically. Actually, when
a user clicks on file date database will show msgbox in order to check
whether he/she wants to write new date or not, if answer yes old date will be
copied to old date field and file date field will be available to new data
input.

I can create one more table for "history/superseded records". If I create
relationship what is the best way to prevent potential/people based data loss?
Stand with them with a mace

You most certainly can create a new record for each date, but you don't have
to.
If you use a relational model then you will have a simple to maintain set of
tables that will allow easy report and form designs and be easy to maintain
with little or no code.
E.G. A form with a subform dropped on it requires you to enter, at most, a
master/child relationship and eliminates the need for the code I've left in.

Private Sub From_Site_Click()
Dim fromsite, subform, stLinkCriteria As String

[quoted text clipped - 4 lines]

DoCmd.OpenForm subform, acNormal, , , acFormEdit, acHidden,
stLinkCriteria

If you don't you will be writing code to solve your problems and sooner than
later run into a problem that will either be beyond your ability to code and
or create other problems because of the code.

 

[Mike Painter]

You have information that will remain the same for a particular job and
information that changes.
The master record should contain information that does not change.

A related table would contain information that is new. In general there
should be no need to copy old information to a new record.
There should be no need to enter an old date, just the new date for a
change.
If an autonumber key is used or you use a "DateAdded" field you can trace
the changes chronologically no matter what *new* date the user enters.


Don't allow your users to delete records is the first step.
If an audit trail is impoertant don't let them make changes after they have
saved a record with something like.

If Not Me.NewRecord Then
Me.Payment.Locked = True
Else
Me.Payment.Locked = False
End If

You mean entering old date manually? That's OK for me but how about
other users that has permission data input? They can forget this
point. That's why I'm trying to copy that record to another field
automatically. Actually, when a user clicks on file date database
will show msgbox in order to check whether he/she wants to write new
date or not, if answer yes old date will be copied to old date field
and file date field will be available to new data input.

I can create one more table for "history/superseded records". If I
create relationship what is the best way to prevent potential/people
based data loss? Stand with them with a mace

You most certainly can create a new record for each date, but you
don't have to.
If you use a relational model then you will have a simple to
maintain set of tables that will allow easy report and form designs
and be easy to maintain with little or no code.
E.G. A form with a subform dropped on it requires you to enter, at
most, a master/child relationship and eliminates the need for the
code I've left in.

Private Sub From_Site_Click()
Dim fromsite, subform, stLinkCriteria As String

[quoted text clipped - 4 lines]

DoCmd.OpenForm subform, acNormal, , , acFormEdit, acHidden,
stLinkCriteria

If you don't you will be writing code to solve your problems and
sooner than later run into a problem that will either be beyond your
ability to code and or create other problems because of the code.

 

[spawnss via AccessMonster.com]

Thank you for your replies.

I decided to pop up a msgbox and ask to user whether they want to add new
date for old records or not? If they want to add new a msgbox will pop up and
if they click yes subform will open and they can write or copy old date to
here. I know that it looks like circuitous. Maybe I can improve it later on.

Again thank you.

You have information that will remain the same for a particular job and
information that changes.
The master record should contain information that does not change.

A related table would contain information that is new. In general there
should be no need to copy old information to a new record.
There should be no need to enter an old date, just the new date for a
change.
If an autonumber key is used or you use a "DateAdded" field you can trace
the changes chronologically no matter what *new* date the user enters.

Don't allow your users to delete records is the first step.
If an audit trail is impoertant don't let them make changes after they have
saved a record with something like.

If Not Me.NewRecord Then
Me.Payment.Locked = True
Else
Me.Payment.Locked = False
End If

You mean entering old date manually? That's OK for me but how about
other users that has permission data input? They can forget this

[quoted text clipped - 27 lines]