CODEWITHSUNDEEP
sqlvbams-accesseofrecordset
skevthedev
skevthedev

Reputation: 465

access 2007 vba recordset.EOF loop returning back first record of set

So I am trying to select some data from a table and then loop through the results and insert new records with modified/additional data into the table. the problem I am having is that when i loop through my recordset It seems to go through the whole set and then back to the first set before exiting.

An example is that I have two records in the table and when I make the selection it should insert only two records but for some reason it inserts 3, the first one repeating twice

Here is my code.

Dim rs1 As DAO.Recordset
Dim query1 As String
query1 = "select [Test Script] from TEST_SCRIPTS where TEST_ID= " & Me.TEST_ID & " "

Set rs1 = CurrentDb.OpenRecordset(query1)

If Not (rs1.EOF And rs1.BOF) Then

    rs1.MoveFirst
    Do Until rs1.EOF = True

         INSERT_INTO_TEST_SCRIPTS = "insert into TEST_SCRIPTS ([Test Script], TEST_ID) values (""" & rs1![Test Script] & """, " & Me.TEST_ID & ") "

            DoCmd.SetWarnings False
                DoCmd.RunSQL INSERT_INTO_TEST_SCRIPTS
            DoCmd.SetWarnings True

        rs1.MoveNext

    Loop
End If

rs1.Close
Set rs1 = Nothing

I am new to working with recordsets and this one has been bothering me, I appreciate any help.

thanks

Upvotes: 0

Views: 1685

Answers (1)

Elias
Elias

Reputation: 2632

I think your code should work too, the way it is.

Try setting the recordset to forward only, maybe that will help

Set rs1 = CurrentDb.OpenRecordset(query1, dbOpenForwardOnly)

That being besides the point, it would be more efficient to do an insert select.

Insert into YourTable (col1, col2) (Select col1, col2 From otherTable)

Upvotes: 1

Related Questions