[RESOLVED] Function giving Type Mismatch Error

[Krenshau]

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.

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.

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.

[brucevde]

I think the problem is with this function

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

What does it return?

[Krenshau]

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.

[brucevde]

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

[Krenshau]

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

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.

[Krenshau]

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.