Saving Multiple Records at once

[EvanRosenlieb]

Hello,
I am managing a database for an academic project that requires a large amount of data-entry. In particular, there are several times when there are hundreds of identical records that must be entered. I would like to design a form that has a quantity box, so that if the person entering the data had to enter 320 identical records, they could just enter 320 in the quantity box, and then enter the rest of the data, and press the save button and then 320 identical (except for the Primary Key) records will be saved at the same time.
Does anyone know if this is possible? I'm assuming VBA is necessary?


-Evan

[ajetrumpet]

yes vb is required. what comes to mind is something like this:

DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO table ( field1, field2, etc.. ) VALUES ('value1', 'value2', etc... )"
DoCmd.SetWarnings True
Me.Requery

you'd have to run it once per iteration though, of course. I can't really think of any other way to do it that's easier. This method too, would be easiest if you were entering data into an unbound form, otherwise you'll have to deal with the record that the user is already entering as it will already be bound.

The idea is to concatenate the form values into the 'value' strings.

[boblarson]

Will post the solution I started in a minute. My browser decided to post before I was done.

[boblarson]

You could use a Recordset to do this as well. You could have a button to do the copying of the records and a text box to say how many to do. The click event of the button would be something like this:

    Dim rst    As DAO.Recordset
    Dim i      As Integer
 
    ' Find out if the user entered a number of the record to copy.
    If Len(Me.TextBoxWithCount & vbNullString) > 0 Then
        ' open the table
        Set rst = CurrentDb.OpenRecordset("TableNameHere")
 
        ' repeat up to the number you selected
        For i = 1 To Me.TextBoxWithCount
            With rst
                .AddNew
                ' the first part is the name of the field in the table.  The second here (Me.FieldControlNameHere) is the name of the control
                ' that is bound to the field in the table.
                !FieldNameHere = Me.FieldControlNameHere
                !Field2NameHere = Me.FieldName2ControlNameHere
                !Field3NameHere = Me.FieldName3ControlNameHere
                !Field4NameHere = Me.FieldName4ControlNameHere
                !Field5NameHere = Me.FieldName5ControlNameHere
                .Update
            End With
        Next

        rst.Close

        Set rst = Nothing

        Me.Requery

[EvanRosenlieb]

Edit: I have successfully debugged the code and it works great. Thanks Bob Larsen. In case it ends up helping anyone else, the code I ended up with is included below. Instead of using an If statement I just set the default value of the test box with the number to one, and had it reset to one after the action.
-Evan

Private Sub SaveBox_Click()
Dim i      As Integer
Dim WorkingDB As Database
Set WorkingDB = OpenDatabase("C:\Angert_Lab\Mimulus\Mimulus Demography Shared\Mimulus Demography Database\WorkingDB.accdb")

 Dim rsBoxEntry As Recordset

Set rsBoxEntry = WorkingDB.OpenRecordset("NewBoxEntry", dbOpenDynaset)
    For i = 1 To Me.HowMany
        rsBoxEntry.AddNew
               
                rsBoxEntry!Tag = Me.TagNumber
                rsBoxEntry!PlotID = Me.PlotID
                rsBoxEntry!Y1 = Me.Y1
                rsBoxEntry!Y2 = Me.Y2
                rsBoxEntry!X1 = Me.X1
                rsBoxEntry!X2 = Me.X2
                rsBoxEntry!Class = Me.Class
                rsBoxEntry!HealthNotes = Me.HealthNotes
                rsBoxEntry!TagNotes = Me.TagNotes
                rsBoxEntry!PositionNotes = Me.PositionNotes
                rsBoxEntry!FlStLn1 = Me.FlStLn1
                rsBoxEntry!FlStLn2 = Me.FlStLn2
                rsBoxEntry!FlStLn3 = Me.FlStLn3
                rsBoxEntry!FlStLn4 = Me.FlStLn4
                rsBoxEntry!FlStLn5 = Me.FlStLn5
                rsBoxEntry!FlStCount = Me.FlStCount
                rsBoxEntry!NFStLn1 = Me.NFStLn1
                rsBoxEntry!NFStLn2 = Me.NFStLn2
                rsBoxEntry!NFStLn3 = Me.NFStLn3
                rsBoxEntry!NFStLn4 = Me.NFStLn4
                rsBoxEntry!NFStLn5 = Me.NFStLn5
                rsBoxEntry!NFSTCount = Me.NFSTCount
                rsBoxEntry!Fr = Me.Fr
                rsBoxEntry!Fl = Me.Fl
                rsBoxEntry!Per = Me.Per
                rsBoxEntry!Plus = Me.Plus
            
                rsBoxEntry.Update
           Next
           
        Me.TagNumber = ""
                Me.PlotID = ""
                Me.Y1 = ""
                Me.Y2 = ""
                Me.X1 = ""
                Me.X2 = ""
                Me.Class = ""
                Me.HealthNotes = ""
                Me.TagNotes = ""
                Me.PositionNotes = ""
                Me.FlStLn1 = ""
                Me.FlStLn2 = ""
                Me.FlStLn3 = ""
                Me.FlStLn4 = ""
                Me.FlStLn5 = ""
                Me.FlStCount = ""
                Me.NFStLn1 = ""
                Me.NFStLn2 = ""
                Me.NFStLn3 = ""
                Me.NFStLn4 = ""
                Me.NFStLn5 = ""
                Me.NFSTCount = ""
                Me.Fr = ""
                Me.Fl = ""
                Me.Per = ""
                Me.Plus = ""
        HowMany = "1"

        rsBoxEntry.Close

        Set rsBoxEntry = Nothing

        Me.Requery
End Sub

[EvanRosenlieb]

Also, I would mark this as solved, but I'm not quite sure how... If someone would be kind enough to tell me I will make sure to do so in the future
-Evan