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.
