468,557 Members | 2,149 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,557 developers. It's quick & easy.

Run Time Error (Type Mismatched)

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:-
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtSearch_AfterUpdate()
  2.  
  3. Me.RecordSource = _
  4.         "SELECT * " & _
  5.         "FROM Students " & _
  6.         "WHERE (fncSearch([ID]," & Chr(34) & Me.txtSearch & Chr(34) & ",'Students')=True);"
  7.  
  8. End Sub
While I have write a code in module to search record by keywords.

Expand|Select|Wrap|Line Numbers
  1. Public Function fncSearch( _
  2.     ByVal ID As Variant, _
  3.     ByVal textSearch As Variant, _
  4.     ByVal TableName As String) _
  5.     As Boolean
  6.  
  7.     Dim varText As Variant
  8.     Dim colText As Collection
  9.     Dim colResult As Collection
  10.     Dim intCount As Integer
  11.     Dim rs As DAO.Recordset
  12.     Dim varTempFound() As Variant
  13.     Dim fld As DAO.Field
  14.     ' exit if there is nothing to search
  15.     If Trim(textSearch & "") = "" Then
  16.         fncSearch = True
  17.         Exit Function
  18.     End If
  19.     ' break the txtSearch into an array
  20.     varText = Split(textSearch, " ")
  21.     Set colText = New Collection
  22.     ' add array element to collection
  23.     For intCount = 0 To UBound(varText)
  24.         colText.Add varText(intCount)
  25.     Next
  26.     Set rs = CurrentDb.OpenRecordset( _
  27.             "SELECT * FROM " & TableName & " WHERE " & _
  28.             "[ID] = " & ID, dbOpenSnapshot)
  29.  
  30.     ReDim varTempFound(0)
  31.  
  32.     With rs
  33.         If Not (.BOF And .EOF) Then
  34.             .MoveFirst
  35.             For Each fld In .Fields
  36.                 If UBound(varTempFound) = UBound(varText) + 1 Then
  37.                     fncSearch = True
  38.                     Exit For
  39.                 End If
  40.                 For intCount = 0 To UBound(varText)
  41.                     If InStr(fld.Value, varText(intCount)) <> 0 Then
  42.                         If Not InArray(varTempFound, varText(intCount)) Then
  43.                             ReDim Preserve varTempFound(UBound(varTempFound) + 1)
  44.                             varTempFound(UBound(varTempFound)) = varText(intCount)
  45.                         End If
  46.                     End If
  47.                 Next
  48.             Next fld
  49.         End If
  50.         .Close
  51.     End With
  52.     Set rs = Nothing
  53. End Function
  54.  
  55. Private Function InArray(vArr As Variant, Value As Variant) As Boolean
  56.     Dim v As Variant
  57.     For Each v In vArr
  58.         If v = Value Then
  59.             InArray = True
  60.             Exit For
  61.         End If
  62.     Next
  63. 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?
May 12 '21 #1
1 1759
NeoPa
32,102 Expert Mod 16PB
Hi Aftab.

It looks like you're over-complicating things. What's wrong with :
Expand|Select|Wrap|Line Numbers
  1. With Me
  2.     .RecordSource = Replace("SELECT * FROM [Students] WHERE ([ID]='%V')" _
  3.                           , "%V", .txtSearch)
  4. 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.
May 12 '21 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Christaaay | last post: by
4 posts views Thread by cpptutor2000 | last post: by
reply views Thread by NPC403 | last post: by
1 post views Thread by UniDue | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.