Adding a Specified Number of Records to a Table

Office for Mere Mortals helps people around the world get more from Word, Excel, PowerPoint and Outlook. Delivered once a week. free.

How to add a specified number of records to a table.

Q:  Kacie Anderson writes:  “I have two tables, one for Orders and one for Loads and both of which contain an OrderID field to link them together (the OrderID is autogenerated). For each order, there can be multiple loads. . . . But my client has requested that I have a button that they can press to add “x” number of Loads to that Order in order to have “x” number of autogenerated Load numbers set aside right away. . . . I added a field in the Orders table called “Load #’s to Add” and I was somehow going to use an append query along with a “repeat” macro to take the Load #’s to Add, add a record, and subtract 1 from the Load #’s to Add field until it reaches zero. Am I on the right track? Can you help me go further on this?”


A:  What I would do is set up a loop to create the specified number of new records, using a DAO recordset, picking up the number of records to create from a control on the form (say txtNoLoads) — no need to have a field in the table for this purpose — and create the required number of new records in the loop.  Here is some sample code, running from the AfterUpdate event of the text box txtNoLoads.

Private Sub txtNoLoads_AfterUpdate()


On Error GoTo ErrorHandler


   Dim dbs As DAO.Database

   Dim rst As DAO.Recordset

   Dim intNoLoads As Integer

   Dim i As Integer


   Set dbs = CurrentDb

   Set rst = dbs.OpenRecordset(“tblLoads”)

   intNoLoads = Nz(Me![txtNoLoads].Value)

   If intNoLoads = 0 Then

      MsgBox “Please enter the number of loads”, vbCritical


      GoTo ErrorHandlerExit


      For i = 1 To intNoLoads


         ‘Here you could write standard data to other fields, if desired


      Next i


      MsgBox intNoLoads & ” records added to tblLoads”, vbInformation

   End If



   Exit Sub



   MsgBox “Error No: ” & Err.Number & “; Description: ” & Err.Description

   Resume ErrorHandlerExit


End Sub


If there is a standard number of loads to be entered, that value could be written to txtNoLoads from the Open or Load event of the form.

Want More?

Office Watch has the latest news and tips about Microsoft Office.  Delivered once a week.