Solved

Insert number of rows depending on value in quantity

Posted on 2012-03-22
2
334 Views
Last Modified: 2012-08-14
Hi,

I need to insert records from one table to another depending on what the value is the Quantity field.

At the moment, it's only inserting one record for each line.  I would like to, if there is a value of more than one in the Quantity field, insert that number of rows.

Below is the code so far.

Many thanks :)

strSQL = "SELECT * FROM [Job Quotes Details] WHERE [SubID] = '" & nParentID & "';"
            objRS.Open strSQL, objConn
                  If Not objRS.EOF Then
                        Do Until objRS.EOF
                              strSQL = "INSERT INTO [Job Cost Sheets Details] ([Item],[Quantity],[Days],[Unit Price], Total, SubID) SELECT '"
                              strSQL = strSQL & Replace(objRS("Item"), "'", "''") & "', " & objRS("Quantity") & ", " & objRS("Days") & ", " & objRS("Unit Price") & ", "
                              strSQL = strSQL & objRS("Total") & ", " & nID & ";"
                              objRS2.Open strSQL, objConn
                              objRS.MoveNext
                        Loop
                  End If
            objRS.Close
0
Comment
Question by:Tra71
2 Comments
 
LVL 15

Accepted Solution

by:
pateljitu earned 500 total points
ID: 37752514
Please try this code:

strSQL = "SELECT * FROM [Job Quotes Details] WHERE [SubID] = '" & nParentID & "';"
objRS.Open strSQL, objConn
      If Not objRS.EOF Then
            Do Until objRS.EOF
                
                  objRSQuantity = objRS("Quantity")
                  
                  for i=1 to objRSQuantity
                      strSQL = "INSERT INTO [Job Cost Sheets Details] ([Item],[Quantity],[Days],[Unit Price], Total, SubID) SELECT '"
                      strSQL = strSQL & Replace(objRS("Item"), "'", "''") & "', " & objRS("Quantity") & ", " & objRS("Days") & ", " & objRS("Unit Price") & ", "
                      strSQL = strSQL & objRS("Total") & ", " & nID & ";"
                      objRS2.Open strSQL, objConn
                      objRS2.Close
                  next
                  
            objRS.MoveNext      
            Loop
      End If
objRS.Close

Open in new window

0
 

Author Closing Comment

by:Tra71
ID: 37752681
Great, thanks....
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

777 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