Solved

Access VBA Adding multiple records to table

Posted on 2016-11-03
5
33 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
  • 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 119

Expert Comment

by:Rey Obrero
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 119

Accepted Solution

by:
Rey Obrero 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Modern/Metro styled message box and input box that directly can replace MsgBox() and InputBox()in Microsoft Access 2013 and later. Also included is a preconfigured error box to be used in error handling.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

863 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now