Noob having trouble getting value from query

[kaylachris]

I am attemping to create a table in powerpoint from a command button in access using data from an Access DB. The following code runs just fine when using an entire table content as the sql statement. However, when I attempt to use an actual SQL statement it throws the following error:

Run-time error '3265': Item not found in this collection.

Furthermore, if I comment out the for loop this code will execute but obvioulsy only fills in the first column of data. I'm hoping that theres a relativly easy to understand solution because my background in coding only goes as fars as google

Calling like this doesn't produce the error:

Call insertTableSlide("tblTestDummy", "Test Me")

But this does...

    mySQL = "SELECT tblAPFT.APFT_Test1_Date, tblAPFT.APFT_Test1_Score " & _
            "FROM tblAPFT;"
    Call insertTableSlide(mySQL, "APFT")

The Code that throws the error

For i = 1 To ColumnCount 'If I comment out this for loop the code will execute fine
'However, it obviously only fills one column of data
                    insertme = rs.Fields(i).Value
                    .Cell(iRow, iCol).Shape.TextFrame.TextRange.Text = rs.Fields(i).Value
                    iCol = iCol + 1
                    fieldNum = fieldNum + 1
                Next i

This is the function that the error throwing code resides in:

Public Function insertTableSlide(rsSQL As String, Optional slideTitle As String)
    Dim db As Database: Set db = CurrentDb
    Dim rs As Recordset: Set rs = db.openRecordSet(rsSQL, dbOpenDynaset)
    Dim fld As DAO.field
 
    Dim iRow As Integer: iRow = 1
    Dim iCol As Integer: iCol = 1
    Dim fieldNum As Integer
    Dim i As Integer
 
    Dim colWidth As Integer
    Dim colHeadWidth As Integer
    Dim RowsPerPage As Integer
    Dim ColumnCount As Integer
    'Open presentation to be edited
    Call openEditPP
 
    RowsPerPage = 10
    ColumnCount = rs.Fields.Count
 
    With Pres.Slides.Add(Pres.Slides.Count + 1, ppLayoutTable)
        .Shapes.Title.TextFrame.TextRange.Text = slideTitle
        With .Shapes.addTable(RowsPerPage, ColumnCount).Table
            'Set the widths of the columns
            'width of column: fld.Size
            'Fill Column Headers
            i = 1
            For Each fld In rs.Fields
                colWidth = Len(fld.Name) * 11
                .Columns(i).Width = colWidth
                .Cell(iRow, iCol).Shape.TextFrame.TextRange.Text = fld.Name
                iCol = iCol + 1
                'i = i + 1
            Next
 
            Set fld = Nothing
            i = 0
            rs.MoveFirst 'Make sure to start at begining
            While Not rs.EOF
                iRow = iRow + 1 'iRow + 1 ' move to next row
                iCol = 1 'Make sure to start at the first column
                Dim insertme As String
 
                For i = 1 To ColumnCount
                    insertme = rs.Fields(i).Value
                    .Cell(iRow, iCol).Shape.TextFrame.TextRange.Text = rs.Fields(i).Value
                    iCol = iCol + 1
                    fieldNum = fieldNum + 1
                Next i
                rs.MoveNext
            Wend
        End With
    End With
 
    'Close PowerPoint App
    Call closeopenEditPP
End Function

These next two sets of code are simply for informational purposes since they are called in the above code:

Private Sub openEditPP()
    'creates an instance of PowerPoint and prepares it for editing
    Dim PresMsg, PPRunning As Boolean
    Dim filePath As String: filePath = getNewFilePath()
 
     'Note: error handling is directed to different points, "AppErr" in case an error occurs while creating or accessing the
     'PowerPoint application or on opening of the presentation, "PresErr" in case an error occurs while editing the presentation.
     'Depending on where an error occurs, an error specific message will be shown after the cleanup process.
    On Error Resume Next
    Set PP = GetObject(Class:="PowerPoint.Application")
    On Error GoTo AppErr
 
     'If PowerPoint is already running, make a note of it, because we shouldn't quit the application when done.
     'If PowerPoint is not running yet, create a new instance of it.
    If Not PP Is Nothing Then
        PPRunning = True
    Else
        Set PP = New PowerPoint.Application
    End If
 
     'Open a presentation.
    Set Pres = PP.Presentations.Open(filePath, WithWindow:=False)
 
    On Error GoTo PresErr
 
     'Do stuff, add a blank slide at the end of the presentation for instance.
 
     'Save the changes made.
    'Pres.Save
 
PresErr:
     '(the "cleanup" process)
     'Close the presentation.
    Pres.Close
 
    If Err.Number <> 0 Then PresMsg = True
 
AppErr:
     'If a new instance of PowerPoint was created, quit it.
    If (Not PP Is Nothing) And (PPRunning = False) Then PP.Quit
 
 
     'If no error occurred, exit the procedure, else, show the proper error message.
    If Err.Number = 0 Then Exit Sub
 
    If PresMsg Then
        MsgBox "A problem occurred while editing the presentation. " & vbCrLf & _
        "Due to this problem, the presentation is closed. ", vbExclamation, "May I have your attention please..."
    Else
        MsgBox "PowerPoint is possibly not installed on your system, " & vbCrLf & _
        "or the presentation you wish to open could not be found. ", vbExclamation, "May I have your attention please..."
    End If
 
End Sub

Sub closeEditPP()
    Pres.Save
    'Clear object variables.
    Set Pres = Nothing
    Set PP = Nothing
End Sub

 

[DJkarl]

What line in particular is throwing the 3265 error?

 

[JHB]

Could you post your database with some sample data, (zip it if you don't have post 10 post yet)?