CODEWITHSUNDEEP
ms-accessvba
Shahid Khan
Shahid Khan

Reputation: 1

Create a number of records equal to the value of a table field

I have a table with ID, product number, product description, qty fields.

Now I want to create a new table containing a quantity of records given by the qty field of the parent table.

For example, if the parent table has a record with qty = 6 then in the child table it should create 6 records with serial numbers from 1 to 6.

Upvotes: 0

Views: 38

Answers (2)

Elwick
Elwick

Reputation: 126

You can use the code below.

Dim rsTable1 as Recordset
Dim rsTable2 as Recordset
Dim Table1qty as integer
Dim i as integer

set rsTable1 = CurrentDb.openrecordset("SELECT qty FROM Table1")
set rsTable2 = CurrentDB.openRecordSet("SELECT * From Table2")

if rsTable1.RecordCount > 0 then
    while not rsTable1.EOF

        Table1qty = rsTable("qty")

        for i = 1 to Table1qty
           rsTable2.Addnew
           rsTable2!SerialNumber.Value = i
           rsTable2!SomeField.Value = SomeValue
           rsTable2.Update
         next 

     rsTable1.movenext
     wend
end

Upvotes: 1

Gustav
Gustav

Reputation: 55841

You can use a simple loop like this:

Dim rs As DAO.Recordset
Dim Record As Integer
Dim Records As Integer

Set rs = CurrentDb.OpenRecordset("Select * From YourTable")
' Count of records - use value from your quantity field:
Records = 5 

For Record = 1 To Records
    rs.AddNew
        rs!SomeID.Value = Record
        rs!Field1.Value = SomeValue
        rs!Field2.Value = SomeOtherValue
        rs!Field3.Value = YetAnotherValue
   rs.Update
Next
rs.Close

Set rs = Nothing

Upvotes: 1

Related Questions