INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Insert records in a new table using nested loop on another table

Insert records in a new table using nested loop on another table

(OP)
Need help writing a nested loop please.
Trying to append an existing table Surcharges with two fields Surcharge_Desc and Surcharge_Amt (it has 464,464 records some of which can be null in the fields)
with data from another table Surcharge_Upload.
This Surcharge_upload table has fields starting at field83 going to field130.
Field83 contains the descriptions
Field84 contains the amount
This repeats every two fields up to field130.

I have this code but it is only returning 2 records and then 22 blank lines.
the two lines are only from field83 and field84.

Option Compare Database

Dim db As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset

Set db = CurrentDb.OpenRecordset(strSQL)
Set rs1 = db.OpenRecordset("surcharge_upload", dbOpenSnapshot)
Set rs2 = db.OpenRecordset("surcharges", dbOpenDynaset)

For x = 83 To 130 Step 2
rs2.AddNew
rs2!Surcharge_Desc = rs1.Fields("Field" & x)
rs2!Surcharge_Amt = rs1.Fields("Field" & x + 1)
rs2.Update
Next x

End Sub

RE: Insert records in a new table using nested loop on another table

With your loop, it looks to me that you point to the very first record in surcharge_upload table and go across the fields in this table never moving to any other record, but I think you want to go down the records in your table.

So your loop looks like this:

CODE

rs2!Surcharge_Desc = rs1.Fields("Field83")
rs2!Surcharge_Amt  = rs1.Fields("Field84")
rs2.Update
rs2!Surcharge_Desc = rs1.Fields("Field55")
rs2!Surcharge_Amt  = rs1.Fields("Field86")
rs2.Update
...
rs2!Surcharge_Desc = rs1.Fields("Field130")
rs2!Surcharge_Amt  = rs1.Fields("Field131")
rs2.Update 

I think you want to do this:

CODE

Do While Not rs1.EOF
 rs2.AddNew
 rs2!Surcharge_Desc = rs1.Fields("Field83").Value
 rs2!Surcharge_Amt  = rs1.Fields("Field130").Value
 rs2.Update
 rs1.MoveNext
Loop 

If that is what you want, you can do the same with one simple INSERT statement.

INSERT INTO surcharges (Surcharge_Desc, Surcharge_Amt)
SELECT Field83, Field130 FROM surcharge_upload


Have fun.

---- Andy

There is a great need for a sarcasm font.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close