[RESOLVED] Function giving Type Mismatch Error

Hello,

I am having a problem returning a collection from a function that is in a class.

Basically, the function gets a set of comments from a database and sticks them in a collection, then I loop thru the collection and fill a listbox. However, when I run the program, the sub that calls the function that returns the collection throws an error that says, "Compile Error Type Mismatch". I am not sure how there can be a type mismatch. The variable that holds the returned collection is dimed as a collection. My code is below. The part in red is highlighted when the error is thrown.

Code:

---

Private Sub cmdAddComment_Click()
On Error GoTo ErrorHandler

If Settings.REPORT_OPEN = True And Settings.ReportControl.REPORT_SAVED = True Then
   
    Dim CommentCol As New Collection
   
    Select Case Settings.OPEN_REPORT_NAME
   
    Case Is = 0
       
        Set CommentCol = Settings.ReportControl.DataControl.GetCommentsBySubFunction(Settings.ReportControl.DataControl.GetSubFunctionIDFromCollection(Settings.ReportControl.DataControl.SUBFUNINDEX_INDEX), False)
       
    Case Is = 1.........

---

Here is the code that gets called.

Code:

---

Public Function GetCommentsBySubFunction(ByVal ParentID As Long, ByVal MultiTransactions As Boolean) As Collection
On Error GoTo ErrorHandler

If MultiTransactions <> True Then

    Me.OpenDatabase
   
End If
   
    Dim A As New Collection
   
    DataTable.TableName = "Comment"
   
    DataTable.Open
   
    DataTable.IndexName = "IndexPosition"
   
    DataTable.IndexAscending
   
    DataTable.Query "PID = " & "'" & ParentID & "'"
       
    Me.ReleaseComments
   
    If DataTable.QueryRowCount <> 0 Then
       
        Settings.ReportControl.DataControl.COMMENT_INDEX = 1
       
        While Not DataTable.EOF = True
           
            A.Add Trim$(Replace(DataTable.GetString("Comment"), Chr$(0), vbNullString))
       
            DataTable.Next
       
        Wend
   
    Else
       
        A.Add "No pre-defined comments available."
       
    End If
   
    DataTable.Close
   
    Set GetCommentsBySubFunction = A
   
    Set A = Nothing
   
If MultiTransactions <> True Then

    Me.ReleaseDatabase
   
End If

Exit Function
ErrorHandler:
ErrorAlert.ErrorAlert Err.Number, Err.Description, "clsTableControl.GetCommentsBySubFunction"
End Function

---

Thank you for your help.

I think the problem is with this function

Settings.ReportControl.DataControl.GetSubFunctionIDFromCollection(Settings.ReportControl.DataControl .SUBFUNINDEX_INDEX)

What does it return?

The GetSubFunctionIDFromCollection returns a Long. It uses the SUBFUNINDEX_INDEX to retrieve a subfunction ID from a collection and uses the subfunctionID in the GetSubFunctionIDFromCollection to find the related comments.

Originally, I had the GetCommentsBySubFunction function return a string, and everything works well, and has for over a year. Tonight, I got the idea of sticking all of the comments into a listbox, and having the user select them from a list, instead of clicking next and add on a separate form. I thought the easiest way would be to put all of the comments in a collection, and pass the collection on to be looped thru.

Can you post the GetSubFunctionIDFromCollection? Better yet post the entire DataControl class.

I can't post the entire DataControl class, it is over 3000 lines of code. Here is the function you requested.

Code:

---

Public Function GetSubFunctionIDFromCollection(ByVal CollectionIndex As Integer) As Long
On Error GoTo ErrorHandler
   
    If CollectionIndex <= SubFunIndex.Count And CollectionIndex > 0 Then

        If SubFunIndex.Count <> 0 Then
   
            Set SubFunctionList = SubFunIndex(CollectionIndex)

            GetSubFunctionIDFromCollection = SubFunctionList.ID
       
        Else
       
            GetSubFunctionIDFromCollection = 0
       
        End If
   
        Set SubFunctionList = Nothing
       
    End If

Exit Function
ErrorHandler:
ErrorAlert.ErrorAlert Err.Number, Err.Description, "clsTableControl.GetSubFunctionIDFromCollection"
End Function

---

SubFunIndex is a private collection in the DataControl class. The DataControl class acts as an interface with the collection. SubFunctionList is a class that holds the data just about that specific subfunction, that class is stored in the collection.

It would be nice if I could step thru the code and see what exactly is throwing the error, but it errors out before the cmdAddComment_Click even runs.

OK, well, first, I am sorry that I wasted your time. I found what the problem was, and it was a stupid mistake. I haven't worked on this code in a year, or more. I created a class that is between the database access class and the form management class that allows me to switch data sources, and it is there that they call the function to use the database. That location was still trying to return a string, when it was receiving a collection, and that is why the type mismatch error.

Thank you very much for your help.