Repeat and Append Records by Quantity in Microsoft Access VBA
Question details
Create one destination record for each unit represented by a quantity in a Microsoft Access source table.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- A summary table stores items and quantities, but the user needs individual records for tracking each item, such as a software-media dongle.
- Observed behavior
- Each source row should produce as many destination rows as its quantity specifies.
Check that the source table has an item field and a nonnegative whole-number quantity field, and that the destination table can accept the copied item value. Adjust the table and field names in the example to match your database.
Use DAO recordsets to append one row per unit
Use a snapshot to read the summary rows and an editable dynaset to add individual destination rows.
This example assumes TotalFruit has FruitName and Quantity fields, and IndividualFruit has a FruitName field. Any other required destination fields must also be populated before Update.
In Access, open your database and press Alt+F11. Choose Insert > Module.
Paste this procedure into the module: Public Sub ExpandFruitRecords() Dim db As DAO.Database Dim sourceRows As DAO.Recordset Dim destinationRows As DAO.Recordset Dim i As Long Dim itemCount As Long Set db = CurrentDb Set sourceRows = db.OpenRecordset("TotalFruit", dbOpenSnapshot) Set destinationRows = db.OpenRecordset("IndividualFruit", dbOpenDynaset) Do While Not sourceRows.EOF itemCount = CLng(Nz(sourceRows.Fields("Quantity").Value, 0)) For i = 1 To itemCount destinationRows.AddNew destinationRows.Fields("FruitName").Value = sourceRows.Fields("FruitName").Value destinationRows.Update Next i sourceRows.MoveNext Loop destinationRows.Close sourceRows.Close Set destinationRows = Nothing Set sourceRows = Nothing Set db = Nothing End Sub
Place the cursor inside ExpandFruitRecords and press F5. Open IndividualFruit in Datasheet view and check that each source item produced the expected number of rows.

Track individual items in WPS Spreadsheets
WPS Office is a free, lightweight alternative with a familiar interface and Microsoft Office format compatibility for an easy move between office files. Use WPS Spreadsheets to maintain an individual-item list, although it does not run this Access recordset procedure or replace an Access database.

Frequently Asked Questions
Why use a snapshot for the source table?
A snapshot is read-only, which suits the source rows when the procedure only needs to read their item names and quantities. The destination uses a dynaset so AddNew and Update can append records.
What happens if the quantity is zero or null?
The example uses Nz to treat a null Quantity as zero. In either case, the For loop adds no destination records for that source row.
How can I copy more fields to each individual record?
Between AddNew and Update, assign each additional destination field from the corresponding source field. For example, set destinationRows.Fields("Category").Value to sourceRows.Fields("Category").Value.
Can I identify each dongle separately after expanding the rows?
Yes. Add an AutoNumber primary key to the destination table, and include fields such as serial number, installation status, or storage location as needed. Enter unique details for each individual record.




