Duplicate record and insert into table 1

[ptuck]

I have a record in one table that I need to duplicate "x" (x will be determined by a field in the table) number of times and insert into another table. Any suggestions on the best way to do this.

 

[SgtJarrow]

My suggestion is not to do this at all. This violates referential integrity rules and unless you have a good reason and document it will, this will come back to bite you in the sitting place later. Can you give some details as to your setup and what you are trying to accomplish. This will allow us to help you find the APPROPRIATE solution for your situation.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[ptuck]

The table will only be used to randomly select a winner of a drawing. The data is worthless and will be deleted after each drawing. The number of times a user can be entered into the drawing depends on how many times they participated in the activity. The user will go to a website and enter name, email, etc. and how many times they participated. I then want to take the number of times participated and insert them into the drawing table this many times.

Does this make sense??

 

[SgtJarrow]

This is one of the cases I was discussing...duplication here would be ok...

I am going to assume your "entries" are done and you will be the one to initiate the transfer from one table to the other. Use something like the following:

open a recordset to each table
move to the first record of the entries table
loop through the recordset and add new records to the drawing table
close both sets

like

Dim rsEntries As ADODB.Recordset
Dim rsDrawing As ADODB.Recordset
Dim i As Integer

Set rsEntries = New ADODB.Recordset
Set rsDrawing = New ADODB.Recordset

rsEntries.Open "tablename", CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdTable
rsDrawing.Open "tablename", CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdTable

With rsEntries
    .MoveFirst
    Do Until .EOF
        For i = 1 To .Fields("fieldwithnumberoftimes")
            rsDrawing.AddNew
            rsDrawing.Fields("fieldname") = .Fields(fFieldname")
            '... (as many fields as needed)
            rsDrawing.Update
        Next i
        .MoveNext
    Loop
End With

rsDrawing.Close
rsEntries.Close

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[ptuck]

If rsEntries is a query what would this line be?

rsEntries.Open "tablename", CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdTable

 

[ptuck]

I got it working..I left off my quotes. I did change the adCmdTable to adCmdQuery. Thanks for the help it works great....