Results 1 to 8 of 8
  1. #1
    virgilio is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    81

    Looping through DAO recordset

    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:

    Code:
    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?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    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"
    Last edited by June7; 02-27-2020 at 04:33 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    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.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    virgilio,
    Can you tell us why you need this in a new table? Just curious.

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by virgilio View Post
    How do I access the next record with each loop?
    This is how I would write the VBA
    Code:
    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)
    Code:
    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
    Code:
    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.

  6. #6
    virgilio is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    81
    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.
    Last edited by virgilio; 02-27-2020 at 06:29 PM. Reason: Clarity

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    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?

  8. #8
    virgilio is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    81
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 03-06-2019, 11:49 AM
  2. Replies: 3
    Last Post: 08-08-2018, 08:13 AM
  3. Replies: 7
    Last Post: 08-26-2016, 07:45 AM
  4. looping through recordset (columnwise)
    By pradeep.sands in forum Queries
    Replies: 1
    Last Post: 06-27-2013, 09:46 AM
  5. Replies: 2
    Last Post: 06-13-2012, 06:00 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums