Access VBA Adding multiple records to table

Hello,
I would like to be able to add multiple records to a given table, based on a value on a user form (MyCopies).  The below code works, but just for a adding a single record.   Perhaps I am approaching it the wrong way?

Private Sub AddInstalls_Click()
Dim MyCopies
Dim MyID
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tblInstallsAndAuths")

Let MyID = Forms!frmAddNewSoftware!SoftwareID
Let MyCopies = Forms!frmAddNewSoftware!NumCopies

With rs
  .AddNew
    .Fields("SoftwareID") = MyID

  .Update
End With

rs.Close
Set rs = Nothing
End Sub

Thanks!
Not2ruthlessAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
Private Sub AddInstalls_Click()
 Dim MyCopies, j as integer
 Dim MyID
 Dim rs As DAO.Recordset
 Set rs = CurrentDb.OpenRecordset("tblInstallsAndAuths")

 Let MyID = Forms!frmAddNewSoftware!SoftwareID
 Let MyCopies = Forms!frmAddNewSoftware!NumCopies

'Do until rs.eof

for j=1 to  MyCopies
 With rs
   .AddNew
     .Fields("SoftwareID") = MyID

   .Update
 End With

next

'rs.movenext
'loop

 rs.Close
 Set rs = Nothing
 End Sub
0
 
Not2ruthlessAuthor Commented:
Let me try to clarify:  If the form field [NumCopies] says, 5, for example,  I would like the above exact steps to repeat 5 times.  Thanks!
0
 
Rey Obrero (Capricorn1)Commented:
you have to iterate thru the recordset

Private Sub AddInstalls_Click()
 Dim MyCopies
 Dim MyID
 Dim rs As DAO.Recordset
 Set rs = CurrentDb.OpenRecordset("tblInstallsAndAuths")

 Let MyID = Forms!frmAddNewSoftware!SoftwareID
 Let MyCopies = Forms!frmAddNewSoftware!NumCopies

Do until rs.eof
 With rs
   .AddNew
     .Fields("SoftwareID") = MyID

   .Update
 End With
rs.movenext
loop

 rs.Close
 Set rs = Nothing
 End Sub

Open in new window

0
 
Not2ruthlessAuthor Commented:
That's cool!  Is there anyway, of just "iterating", for instance what the variable "MyCopies" states.  For instance, the form states 5 numcopies.  (The above added 4,000+ times) ?
0
 
Not2ruthlessAuthor Commented:
Thanks again, I am happy...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.