Can't INSERT entire record set into Access database table using SQL in VBA subroutine

Question

Hi Everyone, I have a VBA subroutine that receives a RecordSet (rs) as a record set parameter. I check to see if any records are in the record set. If records are present it is suppose to write the entire record set into the Access table tblLstItems. I don’t think I am writing the Insert SQL correctly because I get; “Run-time error 28/Out of stack space.” In my INSERT SQL statement I am trying to use the variable rs that I passed to the subroutine as a table, but obviously this isn’t allowed. Can someone point out what I am doing wrong? Thank you, Mark McCumber Subroutine code:

Public Sub List_Excel_WKS(ByVal rs As ADODB.Recordset)
    'Purpose:       Populates Access Table LstItems with
    '               items to use inside a Combobox
    'Parameters:    rs As ADODB.RecordSet - RecordSet containing the Items
    'Returns:       Nothing
    On Error GoTo List_Excel_WKS_Err
    Dim rsItems As ADODB.Recordset, _
        lngID As Long, _
        strItem As String, _
        sSQL As String
    DoCmd.SetWarnings False
    Set dbs = CurrentDb
    With rs
        If .RecordCount <> 0 Then
            'Did rs get passsed correctly
             strSQL = "INSERT INTO tblLstItems " & _
                      "( ItemID, Item ) SELECT rs.ItemID, rs.Item FROM rs;"
             DoCmd.RunSQL strSQL
        End If
    End With
   
List_Excel_WKS_Exit:
    On Error Resume Next
    DoCmd.SetWarnings True
    rsItems.Close
    Set rsItems = Nothing
    Exit Sub
    
List_Excel_WKS_Err:
    'Log error
    Call LogError(Err.Number, _
                  Err.Description, _
                  "modFunctions_v2 Sub List_Excel_WKS", _
                  Date)
    Resume List_Excel_WKS_Exit
End Sub

Answer 1

SQL doesn't know about your rs. So, open tblLstItems as another recordset; then loop rs and add the records from this one by one to the tblLstItems recordset. That said, it might be simpler and faster to use rs to create a value list to be used by the combobox. Or, learn about using Callback for combo- and listboxes. See my article at GitHub: https://github.com/GustavBrock/VBA.Callback

Answer 2

Hi Everyone: I found what was causing the Runtime Error 28/Out of stack space. I have a database table called tblErrors that is designed to log the errors that occur during the execution of the project. For some reason the field "Description" in the table somehow got deleted and every time there was an error the Runtime error popped up. I had to place a Debug.Print line into the Error routine just to see what occurring in order for me to fix it. Thanks for your indulgence, MRM256