Transfer singel and duplicates into an other table

[Niborgst]

Hello all



I have a table with unique records which I have to transfer into a second table. Now in the source table is an attribute "count". I will transfer each record into the result table, but I need each record the amount of the value from "count".

for instance: record 1 has a count value: 1. so it is a single transfer. record 2 has the value 3, so I need record 2 times 3 into the result table.

my vba script looks like this:

Dim row As Integer
Dim rs As Recordset
Dim i As Integer
Dim Count As Integer
Set rs = CurrentDb.OpenRecordset("my_source")
duplicates= rs.Fields("Count").Value

For row = 1 To rs.RecordCount

For i = 1 To duplicates
DoCmd.RunSQL ("INSERT INTO result_table SELECT .... FROM my_source "WHERE rss.RowNo =" & row)
next

Next

unforthunately the second loop is not working since the value "duplicates" is always from the first row.

Do you have any good advice for my small problem?

Best regards Niborgst

[aytee111]

rs.MoveFirst
...
rs.MoveNext

[Niborgst]

Hello aytee111
Thank you for you input. I am sorry, I'm not used to that function.
how should I implement that?

....
rs.MoveFirst
For row = 1 To rs.RecordCount

For i = 1 To duplicates
DoCmd.RunSQL ("INSERT INTO result_table SELECT .... FROM my_source "WHERE rss.RowNo =" & row)
next

rs.MoveNext
Next
...

or without a for loop?

Best regards Niborgst

[aytee111]

Dim rs As Recordset, rsOut as Recordset
Dim i As Integer

Set rs = CurrentDb.OpenRecordset("my_source")
Set rsOut = CurrentDb.OpenRecordset("result_table")

rs.MoveFirst
Do Until rs.EOF
 duplicates= rs!Count
 For i = 1 To duplicates
  rsOut.AddNew
   rsOut!field1=rs!field1
   rsOut!number=i
   ...
  rsOut.Update
 next
 rs.MoveNext
Loop
Set rs=Nothing
Set rsOut=Nothing

[Niborgst]

This works. thank you very much