Access 2007 - Copying Records Within The Same Table

I have 2,433 records in a table (with many many fields).

I need to make an exact copy of each record, with one exception. I need a change made in each newly created record which will give it a unqiue name. These records present various funding scenarios for various companys. Essentially this table holds the results of "what if" games played by accountants.

Each of the existing 2,433 records now have a "scenario" name of "Original Scenario" (the field is even called Scenario) - all of the remaining fields have number representing a variety of things.

I need to make a copy of all 2,433 records but with a scenario name of "New Scenario". Everything would be indentical except that one field. The end result would be that I now have 4,866 records with 2,433 having "Original Scenario" in the Scenario field and 2, 433 records with "New Scenario" in the Scenario field. This is what I've tried, but all it does is duplicate everything, even the scenario name.

Code:

---

Public Sub m_AddNewScenarioRecords()
'the purpose of this sub is to insert a fresh set of records
'for a newly created scenario.  this scenario is created and
'saved on the PCMH_Data form
Dim db As DAO.Database
Set db = Access.Application.CurrentDb
Dim rsOriginal As DAO.Recordset, rsNew As DAO.Recordset
Dim i As Long

    Set rsOriginal = CurrentDb.OpenRecordset("SELECT * FROM source_scenarios_results WHERE scenario = 'Original Scenario' ")
    Set rsNew = CurrentDb.OpenRecordset("SELECT * FROM source_scenarios_results WHERE scenario = 'Original Scenario' ")
    Do While Not rsOriginal.EOF
        With rsNew
            .AddNew
            For i = 0 To .Fields.count - 1
              If .Fields(i).Value = "Scenario" Then 'insert new scenario name in scenario field
                  .Fields("Scenerio") = "New Scenario"
              Else 'otherwise insert existing value into new field
                .Fields(i).Value = rsOriginal.Fields(i).Value
              End If
            Next
            .Update
        End With
    rsOriginal.MoveNext
    Loop
End Sub

---

What am I doing wrong?

You misspelled "Scenerio" within your program. The correct spelling is "Scenario" To help you find it: ScenArio. Should be the A, not the E that is in your program.

A much better way to do this would be to write an append query where every field name is the output value of it's own field, except for Scenario. You would give the "New Scenario" as the field value for that field. It will even run in about 1/10 of the time or less than your program.

An "append query"? Ya lost me....

I googled it and it looks like the same thing as an INSERT query. I know how to write an INSERT INTO blah SELECT blah FROM otherbla, and I would assume that it would work the same way even though I'm not dealing with different tables, but I don't get how to change the one field after the inseration of each record.

Lets make is simple. Lets say I have three fields: field1, field2, field3

I want to copy all records in the table to the same table but change the value of field3 from "white" to "black" during the copy. I will end up with exactly double the number of records I started with, with half having "white" in field3 and the other half having "black" in field3.

Using the append query, how would I do that?

I'm thinking maybe I should just copy the whole darn table to a temp table, and update the temp table to change that one field, then copy all the records from the temp table over to my real table, and drop the temp table. It sounds like a lot of steps, but I'm pretty sure it would work. I guess I'm just wondering if there is an easier way.

(BTW: I am constantly misspelling Scenario for some reason....don't know why :D)

here is the SQL for the query you described:

Code:

---

INSERT INTO Table1 ( field1, field2, field3 )
SELECT Table1.field1, Table1.field2, "BLACK" AS Expr1
FROM Table1
WHERE (((Table1.field3)="white"));

---

I tested it on three records in a dummy (Table1) table. It worked.
By the way, Access calls this an Append query, even though the SQL says to INSERT INTO. While in Query Design view, Append is one of the kinds of query to ask for. The others would be "Select", "Make Table", "Update", "CrossTab", and "Delete".

Once again my VBA Knight in shining armour comes through.

Thank you sir.....as the Brits are fond of saying...."Works a treat!" :)