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

[lclayton1997]

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

 

[Andrzejek]

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:

rs2!Surcharge_Desc = rs1.Fields("Field[red]83[/red]")
rs2!Surcharge_Amt  = rs1.Fields("Field[red]84[/red]")
rs2.Update
rs2!Surcharge_Desc = rs1.Fields("Field[red]55[/red]")
rs2!Surcharge_Amt  = rs1.Fields("Field[red]86[/red]")
rs2.Update
...
rs2!Surcharge_Desc = rs1.Fields("Field[red]130[/red]")
rs2!Surcharge_Amt  = rs1.Fields("Field[red]131[/red]")
rs2.Update

I think you want to do this:

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.
[tt]
INSERT INTO surcharges (Surcharge_Desc, Surcharge_Amt)
SELECT Field83, Field130 FROM surcharge_upload
[/tt]

Have fun.

---- Andy

There is a great need for a sarcasm font.