Run Time Error (Type Mismatched)

[Aftab Ahmad]

Hi Experts,
I have a DataSheet form where I want to search my record using an Unbound Textbox. I have write AfterUpdate code for Textbox which is as under:-

Private Sub txtSearch_AfterUpdate()

Me.RecordSource = _
        "SELECT * " & _
        "FROM Students " & _
        "WHERE (fncSearch([ID]," & Chr(34) & Me.txtSearch & Chr(34) & ",'Students')=True);"
        
End Sub

While I have write a code in module to search record by keywords.

Public Function fncSearch( _
    ByVal ID As Variant, _
    ByVal textSearch As Variant, _
    ByVal TableName As String) _
    As Boolean

    Dim varText As Variant
    Dim colText As Collection
    Dim colResult As Collection
    Dim intCount As Integer
    Dim rs As DAO.Recordset
    Dim varTempFound() As Variant
    Dim fld As DAO.Field
    ' exit if there is nothing to search
    If Trim(textSearch & "") = "" Then
        fncSearch = True
        Exit Function
    End If
    ' break the txtSearch into an array
    varText = Split(textSearch, " ")
    Set colText = New Collection
    ' add array element to collection
    For intCount = 0 To UBound(varText)
        colText.Add varText(intCount)
    Next
    Set rs = CurrentDb.OpenRecordset( _
            "SELECT * FROM " & TableName & " WHERE " & _
            "[ID] = " & ID, dbOpenSnapshot)
    
    ReDim varTempFound(0)
    
    With rs
        If Not (.BOF And .EOF) Then
            .MoveFirst
            For Each fld In .Fields
                If UBound(varTempFound) = UBound(varText) + 1 Then
                    fncSearch = True
                    Exit For
                End If
                For intCount = 0 To UBound(varText)
                    If InStr(fld.Value, varText(intCount)) <> 0 Then
                        If Not InArray(varTempFound, varText(intCount)) Then
                            ReDim Preserve varTempFound(UBound(varTempFound) + 1)
                            varTempFound(UBound(varTempFound)) = varText(intCount)
                        End If
                    End If
                Next
            Next fld
        End If
        .Close
    End With
    Set rs = Nothing
End Function

Private Function InArray(vArr As Variant, Value As Variant) As Boolean
    Dim v As Variant
    For Each v In vArr
        If v = Value Then
            InArray = True
            Exit For
        End If
    Next
End Function

But on Search i.e. through Textbox I am facing issue 'Run Time Error' (Type Mismatched) on line No.36. What's wrong with the code?

[NeoPa]

Hi Aftab.

It looks like you're over-complicating things. What's wrong with :

With Me
    .RecordSource = Replace("SELECT * FROM [Students] WHERE ([ID]='%V')" _
                          , "%V", .txtSearch)
End With

Of course, this would only make sense for a Field that's a string - which is unusual for [ID]s. If your [ID] field is numeric (as most are) then it would use the string "SELECT * FROM [Students] WHERE ([ID]=%V)" instead.

NB. I removed the link to your database. Expecting that level of help at this stage is not appropriate. Experts may ask you if you can provide an example but very rarely before trying a simpler approach. You should only ever provide one when asked or it gives the impression you're looking for others to do your work for you. Bytes.com doesn't offer that type of service.

[Aftab Ahmad]

Thanks NeoPa. It works...

[NeoPa]

Hi Aftab.

Very pleased to hear it :-)