Copy a record into the same table

[keers]

Hi All,

i am trying to copy a record into the same table prior to updating the
original with a form. To achive this i am using the following code;

Dim fld As Field
With Me.RecordsetClone
.Bookmark = Me.Bookmark
Me.Recordset.AddNew
For Each fld In .Fields
'skip the PK field
If fld.Name <> "ResourceProjectID" Then
Me.Recordset.Fields(fld.Name) = fld.Value
'amend the value in one of the unique index fields
If fld.Name = "Date" Then
Me.Recordset.Fields(fld.Name) = Date
End If
End If
Next fld
Me.Recordset.Update
End With
Set fld = Nothing

As the code runs i get the following error;

Err Number =3164

Field cannot be updated

The field generating the error is called 'ActiveRecord' which is a
text field within the table. It can only be set to 'Yes or No' (google
searches suggest that using the yes/no datatype is not a good idea so
i create the same manually). The properties of the field are;

General

Required = No
Allow Zero Length = Yes
Indexed = No
Unicode Compression = Yes
IME Mode = No Control
IME Sentence Mode

Lookup

Display Control = List Box
Row Source Type = Value List
Row Source = Yes;No
Bound Column = 1
Column Count =1
Column Heads = No

any pointers as to why i am getting this error would be vey much
appreciated - and if there is a better way of copying a record that
would be a bonus.

Thanks for your time

Keers

 

[Jeff Boyce]

You've described what and how, but not why.

What will having a copy of a record added to the same table allow you to do?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[keers]

You've described what and how, but not why.

What will having a copy of a record added to the same table allow you to do?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP















- Show quoted text -

Hi Jeff,

I have been asked to keep track of each record as it changed - so
keeping a copy of the 'current' record prior to update seemed the
right thing to do. The particular field causing the problem is to be
set during the copy operation to say that the record has been
superseeded (to be used later to identify the 'current' record).

The database is to keep track of people costs while working on
projects; 3 main tables;

1. tlbResources (names of indivuduals, rate of pay per hour, hours
worked per week, call out rate etc)
2. tblProjects (names and details of the projects, sponsors etc)
3. tblCTC (each records contains the resource and the project details
being worked on)

So there current thinking is; if for example, the rate of pay for an
inidivdual changes, i need to close of all the records in the CTC that
they are currently working on and create new ones with the new rate of
pay. At this point i will be able to write query that will show the
change in cost to the project based on the pay up lift.
I did consider creating a seperate, historical table, but that seemed
more complicated......

hope that makes sense.


Keers

 

[Jeff Boyce]

I probably don't have a complete-enough understanding of your situation yet,
but I'll still offer the following...

If one person can have many 'rates', that's a one-to-many relationship. It
sounds like you are trying to track costs over time, so you'd need to know
the person-rate at the beginning, and after each change. One way to do this
is to not "close all the CTC records" and write new ones, but to keep track
of FromDate and ToDate person-rates assigned. No "closing" required, just
add a new record with the new person-rate (and the StartDate).

Or maybe I'm not seeing the full picture ...

Regards

Jeff Boyce
Microsoft Office/Access MVP

You've described what and how, but not why.

What will having a copy of a record added to the same table allow you to
do?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP















- Show quoted text -

Hi Jeff,

I have been asked to keep track of each record as it changed - so
keeping a copy of the 'current' record prior to update seemed the
right thing to do. The particular field causing the problem is to be
set during the copy operation to say that the record has been
superseeded (to be used later to identify the 'current' record).

The database is to keep track of people costs while working on
projects; 3 main tables;

1. tlbResources (names of indivuduals, rate of pay per hour, hours
worked per week, call out rate etc)
2. tblProjects (names and details of the projects, sponsors etc)
3. tblCTC (each records contains the resource and the project details
being worked on)

So there current thinking is; if for example, the rate of pay for an
inidivdual changes, i need to close of all the records in the CTC that
they are currently working on and create new ones with the new rate of
pay. At this point i will be able to write query that will show the
change in cost to the project based on the pay up lift.
I did consider creating a seperate, historical table, but that seemed
more complicated......

hope that makes sense.


Keers

 

[keers]

I probably don't have a complete-enough understanding of your situation yet,
but I'll still offer the following...

If one person can have many 'rates', that's a one-to-many relationship.  It
sounds like you are trying to track costs over time, so you'd need to know
the person-rate at the beginning, and after each change.  One way to dothis
is to not "close all the CTC records" and write new ones, but to keep track
of FromDate and ToDate person-rates assigned.  No "closing" required, just
add a new record with the new person-rate (and the StartDate).

Or maybe I'm not seeing the full picture ...

Regards

Jeff Boyce
Microsoft Office/Access MVP








Hi Jeff,

I have been asked to keep track of each record as it changed - so
keeping a copy of the 'current' record prior to update seemed the
right thing to do. The particular field causing the problem is to be
set during the copy operation to say that the record has been
superseeded (to be used later to identify the 'current' record).

The database is to keep track of people costs while working on
projects; 3 main tables;

1. tlbResources (names of indivuduals, rate of pay per hour, hours
worked per week, call out rate etc)
2. tblProjects (names and details of the projects, sponsors etc)
3. tblCTC (each records contains the resource and the project details
being worked on)

So there current thinking is; if for example, the rate of pay for an
inidivdual changes, i need to close of all the records in the CTC that
they are currently working on and create new ones with the new rate of
pay. At this point i will be able to write query that will show the
change in cost to the project based on the pay up lift.
I did consider creating a seperate, historical table, but that seemed
more complicated......

hope that makes sense.

Keers- Hide quoted text -

- Show quoted text -

Hi Jeff,

to complete the thread; I found the following solution which works
perfectly,creating an audit of changes to tables that i can later
query; http://www.allenbrowne.com/AppAudit.html

Rgds

Keers