?
Solved

Access VBA Adding multiple records to table

Posted on 2016-11-03
5
Medium Priority
?
122 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 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Use Windows Task Scheduler to print a Word document weekly so your printer ink won't dry out.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

762 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