Generate duplicate records in Access 2000

[TheOldBean]

Can anyone please suggest code to generate a sequentially numbered range of records (the quantity of records will vary) counting from a selected integer field (ideally the Primary Key) in a Table. All other Fields in the record set would keep the same values. Thanks

 

[GummowN]

Try writing the sqlstr you require, open that as a recordset, and then clone the recordset.

You were a bit vague with your question so that is the best I can do. Elaborate on what you are trying to do? Do you want to copy a section of a table to another table? Or manipulate a section of data from a table in code?

 

[TheOldBean]

Sorry I was so vague! What I want to do is generate, say, 20 new records numbered 2346 to 2365 but keeping the exact same data from record 2345 shown in its Fields 1,2,3 etc. Does this make more sense?

Unique Field1 Field2 Field3
1234 Fred Jim 03/02/2003
2345 Sam Ken 01/02/1999
4567 Bob Dave 06/12/2002

 

[GummowN]

Try this:
Function AddDupRecords(i as integer, j as integer)
Dim dbs as database
Dim rst as recordset
Dim x as integer
Dim rst1 as recordet, sqlstr as string

set dbs = currentdb
set rst = dbs.openrecordset("table1&quot
sqlstr = SELECT * FROM table1 WHERE Unique = " & i & ";"
set rst1 = dbs.openrecordset(sqlstr)
For x = i to i+j
with rst
.addnew
.fields("unique&quot = x
.fields("field1&quot = rst1.field1
'etc for all fields
.update
end with
x = x+1
Loop
rst1.close
rst.close
set dbs = nothing
End sub

From the debug window run ?AddDupRecords(<record to copy>, <number of copies>)

 

[TheOldBean]

Very many thanks for your help.
Doubt I'd have ever got there!
Will give this a try.