Create multiple records from a single record-CodePudding

I have this code that creates a record, is it possible to edit this to create multple records? I have a textbox that says Aantal (amount) let's say I enter 20, could it create 20 records with each record having a different value?

The value that is being put into the table is based on the box: [SerialNmbr], which is a combination of three different boxes.

=([OrderNr] & ("" [Aantal]) & "" & [SapArtNr])

All three together will look like: 0000020TEST (OrderNr: 00000 Aantal: 20 SapArtNr: TEST)

Record one would be: 0000001TEST, record two would be: 0000002TEST and so on until the amount entered into the Aantal box, in this case 20. And all 20 would have the same values inputted into the record as shown below, just how it works now but times 20.

Code:

Private Sub OpenForm_Click()

CurrentDb.Execute "INSERT INTO Geleidelijst " & _
"(SerialNmbr, InvoerOrderNr, InvoerAantal, InvoerSapArtNr, InvoerVermogen, InvoerHSLSSpn, InvoerSAPItemName) " & _
"VALUES('" & Me.SerialNumberLong & "','" & Me.OrderNr & "'," & Me.Aantal & ",'" & Me.SapArtNr & "','" & Me.Vermogen & "','" & Me.HSLSSpn & "','" & Me.SAPItemName & "')"
DoCmd.OpenForm "GeleidelijstForm"

DoCmd.GoToRecord acDataForm, "GeleidelijstForm", acLast
DoCmd.GoToRecord acDataForm, "GeleidelijstForm", acLast

DoCmd.Close acForm, "OrderinvoerForm"

End Sub

I have tried several methods but it always ends up with an error or creating multiple records but all having the same value. If anyone has any ideas, thank you.

CodePudding user response：

Use DAO and a loop:

    Dim Records     As DAO.Recordset
    Dim Control     As Control
    
    Dim Sql         As String
    
    Dim ProductCode As Long
    Dim Counter     As Long
 
    ' Create records.
    Sql = "Select * From Geleidelijst"
    Set Records = CurrentDb.OpenRecordset(Sql, dbOpenDynaset, dbAppendOnly)

    For Counter = 1 To Nz(Me!Aantal.Value, 0)
        ProductCode = Format(Counter, String(7, "0")) & Me!SapArtNr.Value
        Records.AddNew
        Select Case Control.Name
            Case "SerialNumberLong"
                Records!SerialNmbr.Value = ProductCode
            Case "InvoerOrderNr"
                Records!OrderNr.Value = Control.Value
            Case "InvoerAantal"
                Records!Aantal.Value = Control.Value
            Case "InvoerSapArtNr"
                Records!SapArtNr.Value = Control.Value
            Case "InvoerVermogen"
                Records!Vermogen.Value = Control.Value
            Case "InvoerHSLSSpn"
                Records!HSLSSpn.Value = Control.Value
            Case "InvoerSAPItemName"
                Records!SAPItemName.Value = Control.Value
        End Select
        Records.Update
    Next
    Records.Close