473,289 Members | 2,040 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

Run Time Error (Type Mismatched)

44 32bit
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

✓ answered by NeoPa

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.

3 13133
NeoPa
32,554 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
Aftab Ahmad
44 32bit
Thanks NeoPa. It works...
Oct 25 '22 #3
NeoPa
32,554 Expert Mod 16PB
Hi Aftab.

Very pleased to hear it :-)
Oct 26 '22 #4

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

Similar topics

1
by: ArcadeJr | last post by:
Good morning all! I have been getting a Run-time Error message #3464 - Data Type mismatch in criteria expression. While trying to run a query. I have a database where the field Asset_Number...
4
by: Christaaay | last post by:
I have been using the code below successfully for almost a year. yesterday, I began getting a run time error 6 (overflow). I am using the code in an Access 2000 database. Can anyone help me...
5
by: fimarn | last post by:
I am trying to get rid of compile time error that I am getting only in RHEL5 (not in RHEL4) apparently due to the changes in the stl_list.h file. The error that I am getting is coming from the...
1
by: mudasserrafiq | last post by:
I am using following asp file default.asp <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <META http-equiv=Content-Type content="text/html; charset=windows-1252"> <META content="0...
4
by: cpptutor2000 | last post by:
Could someone please help me? I have the following C struct. typedef struct _FIREWALL_RULE_INFO { ULONG Precedence; BOOL bEnabled; BOOL ...
0
by: hussainiyad | last post by:
Dear All i,m totlally new for vb 6.0 and i,m doing a invoice project and i,m facing a issue , can you plz tell me solution i,m trying to retrive some datas from sql (Product table) it...
1
by: zedd | last post by:
Hi, I orignaly posted this with the error above, however I am still trying to solve it on my own, and found that some of my syntax in the query was not correct and I corrected it. But i am still...
2
by: sknaina | last post by:
My project is based on a2003adp with sql2000 srv. Over there i want to control input validity check via Sp and VBA. That's why I write the following Stoted Proc as well as VBA Function and Procedure...
5
by: IbrahimL | last post by:
Hello Everyone, I have successfully created a login page for my Order Processing System. When I enter my Username and Password correctly it logs in successfully and moved onto my next form called...
2
by: SourceOfAccess | last post by:
So...I'm trying to create a field that automatically calculates a value by gathering data from multiple tables. My code seems to be fine, but MS Access returns with an error: Run-time error...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.