VBA & Macro Problems

Repeat and Append Records by Quantity in Microsoft Access VBA

Muhammad TalhaMuhammad Talha Nov 13, 2026 869 views

Question details

Create one destination record for each unit represented by a quantity in a Microsoft Access source table.

Repeat and Append Records by Quantity in Microsoft Access VBA
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the VBA editor

In Access, open your database and press Alt+F11. Choose Insert > Module.

2
Add the procedure

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

3
Run and check the results

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.

Use DAO recordsets to append one row per unit
Running the procedure again: Each run appends another set of rows; it does not detect previously processed source records. Use a processing flag or another tracking method if rows must not be duplicated.
Free Microsoft Office alternative

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.

Maintain a spreadsheet with one row per item and columns for its status and location.Open and edit Microsoft Excel XLSX files.Work in a familiar spreadsheet interface.Use a free, lightweight office suite.
microsoft office alternative - wps office

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.