Multiple Record Iterations - SQL RST to Access RST

[jaycast]

I am trying to add data to an Access table using ADO in VB from a SQL recordset. I am able to get the records to add to specified table, populating the same number of records as is the recordcount of the SQL recordset, however, it simply lists the first record over and over. I'm assuming this is a very simple connection or recordset configuration, but I haven't been able to isolate it.

Here is what I have so far:

Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs1 As New ADODB.Recordset, rs2 As New ADODB.Recordset
Dim sql as string
Dim count as integer, x as integer

sql = [Desired SQL String]

cn.Open "FreightInvoices", "sa", ""

With rs1
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open sql, cn

Set .ActiveConnection = Nothing
End With

Set cn = Nothing

cn.Open "EDIDOCS", "sa", ""

With rs2
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open "tblFedExInvoiceBreakdown", cn
End With

rs1.MoveFirst
count = rs1.RecordCount
x = 1

For x = 1 To count
rs2.AddNew
rs2("InvoiceNumber&quot = rs1("InvoiceNumber&quot
rs2("TransDate&quot = rs1("TransDate&quot
rs2("InvoiceDate&quot = rs1("InvoiceDate&quot
rs2("NetAmtDue&quot = rs1("NetAmtDue&quot
rs2("ChargeAmount&quot = rs1("ChargeSum&quot
rs2("ShipDate&quot = rs1("ShipDate&quot
rs2("TradingPartnerName&quot = rs1("TradingPartnerName&quot
rs2.Update
rs2.MoveNext
Next x

Set cn = Nothing
Set rs2 = Nothing
Set rs2 = Nothing


Can someone point out my (hopefully) simple mistake?

Thanks in advance!!!

 

[johnwm]

In your For..Next loop you are only doing a MoveNext on rs2 - do it on rs1 at the same time. You also need to set rs1 to Nothing at the end, rather than rs2 twice!

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[jaycast]

I knew it was going to be something as simple as that. I have been staring at this for about 1 1/2 hours trying to see what I missed...I guess all I needed was a second pair of eyes to point out the obvious.

Thanks John!