Looping through DAO recordset

[virgilio]

Hi, I'm trying to loop through a DAO recordset and populate a new table with a key from that recordset.

My code looks like this:

Private Sub My_Function_Click()
'On Error GoTo Error_Handler


   Dim LOT_NUMBER As Long
   Dim Location As String
   Dim strSQL As String
   
   LOT_NUMBER = 12345
   Location = "here"
   
   Dim db As DAO.Database
   Dim rs As DAO.Recordset
   Dim i As Integer
   
   Set db = CurrentDb()
   Set rs = db.OpenRecordset("LOT_DATES")
   
   For i = 0 To rs.RecordCount - 1
      LOT_NUMBER = rs![LOT_NUMBER] '<- This is my problem.  It writes the first LOT_NUMBER from rs, and doesn't go to the next record with i
      
      strSQL = " INSERT INTO Lot_Location (Lot_Num, Location) VALUES " & "('" & LOT_NUMBER & "', '" & Location & "' );"
      DoCmd.RunSQL strSQL
   Next i


   rs.Close
   
End Sub

I'm filling my new table with the first value in the recordset, it isn't iterating through the table with the loop.

How do I access the next record with each loop?

[June7]

Code is populating an existing table, not a 'new' table.

Missing rs.MoveNext line.

Should not have to iterate through recordset. Consider:

CurrentDb.Execute "INSERT INTO Lot_Location(Lot_Num, Location) SELECT Lot_Number, " & Location & " FROM LOT_DATES"

[ranman256]

you don't have to loop thru a recordset to make an append query. (the slowest way) null are the enemy.

just run an append query on the table will get the same results much faster.

[orange]

virgilio,
Can you tell us why you need this in a new table? Just curious.

[ssanfu]

How do I access the next record with each loop?

This is how I would write the VBA

Private Sub My_Function_Click()
    'On Error GoTo Error_Handler

    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    Dim i As Integer
    Dim LOT_NUMBER As Long   'does not need delimiters because it is a Number
    Dim Location As String   'requires delimiters because it is a String
    Dim strSQL As String


    Set db = CurrentDb()

    LOT_NUMBER = 12345
    Location = "here"

    Set rs = db.OpenRecordset("LOT_DATES")
    If Not rs.BOF And Not rs.EOF Then   '<<--- always check to see if there are records in the recordset
        rs.MoveLast
        rs.MoveFirst
        Do Until rs.EOF
            '   For i = 0 To rs.RecordCount - 1
            LOT_NUMBER = rs![LOT_NUMBER]   

            strSQL = "INSERT INTO Lot_Location (Lot_Num, Location) VALUES (" & LOT_NUMBER & ", '" & Location & "' );"  '<<-- notice the delimiters
            '     DoCmd.RunSQL strSQL
            db.Execute strSQL, dbFailOnError
            rs.MoveNext
            '    Next i
        Loop
    Else
        MsgBox "No records found"
    End If

    'clean up
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

This is what June7 suggested (Faster than looping)

Private Sub My_Function_Click()
    Dim Location As String        'requires delimiters because it is a String
    Dim strSQL As String

    Location = "here"

    strSQL = "INSERT INTO Lot_Location(Lot_Num, Location) SELECT Lot_Number, '" & Location & "' FROM LOT_DATES"
    
    'or, since location is static text, you could use
    '        strSQL = "INSERT INTO Lot_Location(Lot_Num, Location) SELECT Lot_Number, 'here' FROM LOT_DATES"

    CurrentDb.Execute strSQL, dbFailOnError

End Sub

Since "Location" is static text, you could use

strSQL = "INSERT INTO Lot_Location(Lot_Num, Location) SELECT Lot_Number, 'here' FROM LOT_DATES"

Then you wouldn't need the variable "Location" in the code.

[virgilio]

I guess there is no reason to build a new table (existing but empty table) other than that’s what I had in mind. It’s moot now because my users decided that they don’t want that feature.

‘Location‘ was going to be calculated from a bunch of IF statements. Thank you all for suggestions, they will be put to good use eventually.

[orange]

I was really wondering what you had in mind for that table. My thought was, that since the value was in the existing recordset, could you make a query to get that value when needed?

[virgilio]

I just wanted it in the table to use as a key. The important part would have been when I fleshed out the ‘Location’ field.