Insert Multiple Records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to insert several records into a table at one time with the same values. I will collect the default values that I need to have for the fields in a form and then insert the default values the number of times that the user puts in a dropdown box. An example of this would be: I collect StartDate, MealDate, FinishDate, FinishTime, and JobCode and the user enters a 3 in the dropdown. I want to insert the values that are in those fields 3 times into the same table. I am sure I have to loop through a recordset somehow but I am under the gun on this and could use some help. The values that the user has entered on the form are not stored in any table.
 
A bound form will store one record when you move off it. You can store the
rest of the copies by creating an Append Query or simply opening a Recordset
and using the .AddNew method.

Larry Linson
Microsoft Access MVP

Bob said:
I am trying to insert several records into a table at one time with the
Click to expand...
same values. I will collect the default values that I need to have for the
fields in a form and then insert the default values the number of times that
the user puts in a dropdown box. An example of this would be: I collect
StartDate, MealDate, FinishDate, FinishTime, and JobCode and the user enters
a 3 in the dropdown. I want to insert the values that are in those fields 3
times into the same table. I am sure I have to loop through a recordset
somehow but I am under the gun on this and could use some help. The values
that the user has entered on the form are not stored in any table.
 
The form is not bound to any table. The form is just used to gather the data for the record that I want to put in the table. Once I get the data in the fields I want to append that the number of times that is entered in the dropdown.
 
The form is not bound to any table. The form is just used to gather the data for the record that I want to put in the table. Once I get the data in the fields I want to append that the number of times that is entered in the dropdown.
 
To add new records with a loop:

Function MakeData(HowMany As Long)
Dim lngI As Long
Dim rs As DAO.Recordset

Set rs = DBEngine(0)(0).OpenRecordset("MyTable")
With rs
For lngI = 1 To HowMany
.AddNew
!SomeField = SomeValue
!AnotherField = AnotherValue
'etc.
.Update
Next
End With
rs.Close
Set rs = Nothing
End Function


It may be easier to execute one Append query statement, and use a Cartesian
product to generate the desired number of records. That's just a matter of
having a table that counts from 1 to the maximum number of records you would
ever need to add. Include this table in your append query, with no join
between tables. Use criteria under the counter field to get the desired
number of records.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Bob said:
I am trying to insert several records into a table at one time with the
Click to expand...
same values. I will collect the default values that I need to have for the
fields in a form and then insert the default values the number of times that
the user puts in a dropdown box. An example of this would be: I collect
StartDate, MealDate, FinishDate, FinishTime, and JobCode and the user enters
a 3 in the dropdown. I want to insert the values that are in those fields 3
times into the same table. I am sure I have to loop through a recordset
somehow but I am under the gun on this and could use some help. The values
that the user has entered on the form are not stored in any table.
 
I think that I am somewhat following you. I take the data that I have collected and send it to a table (temporarily), which would include the number of records that I want to add, and then have another table that has one field that counts from 1-30 (maximum records). How does the maximum records table create my multiple instances of the 1 record? Say the number is 5.
 
What I supplied was 2 differnent approaches.

For the first, you would code:
Call MakeData(5)
to create 5 records.

The alternative would create all 5 records using a table of counters. There
is no temporary table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Bob said:
I think that I am somewhat following you. I take the data that I have
Click to expand...
collected and send it to a table (temporarily), which would include the
number of records that I want to add, and then have another table that has
one field that counts from 1-30 (maximum records). How does the maximum
records table create my multiple instances of the 1 record? Say the number
is 5.
 
Back
Top