Using 2 recordsets in VBA

[Derek]

Hi All

I am writing the following code in the listbox double click event so that when the index is 1 then insert data in a table from another table. So I am using two recordsets but Its not saving data in a table.

 Private Sub lstTabPages_DblClick(Cancel As Integer)
Dim i As Integer
Dim rs As Recordset
Dim rs1 As Recordset
Set rs = CurrentDb.OpenRecordset("tblAuditStandard")
Set rs1 = CurrentDb.OpenRecordset("select * from tblAuditStandardLibrary where StandardDocs='Mandatory'")
    
With Me.lstTabPages
        For i = 0 To .ListCount - 1
            If .Selected(i) Then
            MsgBox i
            If i = 1 Then
            Do While Not rs1.EOF
            rs.AddNew
            rs.Fields("AuditID") = Me.txtAuditID
            rs.Fields("StandardID") = rs1.Fields("StandardID")
            rs.Update
            rs1.MoveNext
            Loop
            End If
            End If
        Next i
End With
     
End Sub

ANy help will be much appreciated.

Thanks

 

[Minty]

Add a debug statement and possibly the other line in red and see what is being returned ?;

    Dim i               As Integer
    Dim rs              As Recordset
    Dim rs1             As Recordset
    Set rs = CurrentDb.OpenRecordset("tblAuditStandard")
    Set rs1 = CurrentDb.OpenRecordset("select * from tblAuditStandardLibrary where StandardDocs='Mandatory'")

    With Me.lstTabPages
        For i = 0 To .ListCount - 1
            If .Selected(i) Then
                MsgBox i
                If i = 1 Then
                    [COLOR="Red"]rs1.MoveFirst[/COLOR]
                    Do While Not rs1.EOF
                        rs.AddNew
                       [COLOR="red"] Debug.print me.txtAuditID, rs1.Fields("StandardID")[/COLOR]
                        rs.Fields("AuditID") = Me.txtAuditID
                        rs.Fields("StandardID") = rs1.Fields("StandardID")
                        rs.Update
                        rs1.MoveNext
                    Loop
                End If
            End If
        Next i
    End With