Solved

Access VBA Adding multiple records to table

Posted on 2016-11-03
5
92 Views
Last Modified: 2016-11-03
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!
0
Comment
Question by:Not2ruthless
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 

Author Comment

by:Not2ruthless
ID: 41873097
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41873102
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
 

Author Comment

by:Not2ruthless
ID: 41873125
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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 41873129
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
 

Author Closing Comment

by:Not2ruthless
ID: 41873140
Thanks again, I am happy...
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question