Adding a Specified Number of Records to a Table

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

      Me![txtNoLoads].SetFocus

      GoTo ErrorHandlerExit

   Else

      For i = 1 To intNoLoads

         rst.AddNew

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

         rst.Update

      Next i

      rst.Close

      MsgBox intNoLoads & ” records added to tblLoads”, vbInformation

   End If

  

ErrorHandlerExit:

   Exit Sub

 

ErrorHandler:

   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.