dcsimg
Results 1 to 5 of 5

Thread: Run-Time error -2147217913 Data type mismatch in criteria expression

  1. #1

    Thread Starter
    New Member
    Join Date
    Jan 2020
    Posts
    2

    Lightbulb Run-Time error -2147217913 Data type mismatch in criteria expression

    Hi to all,

    I'm relatively new to VBA, still learning and I need help

    Excel file that i'm making will have few userforms and there is a problem with one, which will search database that is located in sheet named BOM_Data.
    Here is preview of userform:

    Name:  Userform.jpg
Views: 83
Size:  21.9 KB

    There will be two ways for search:

    - search by scanning - upper textbox - no problem there
    - search by selecting - lower comboboxes - problem

    Selection for way of search will be done with checkbox. Off for first way, on for second.

    I've found code to make dependent comboboxes with adodb connection and there is a problem. When I make second choice (with first choice there is no problem) I get error message "Run-Time error -2147217913 Data type mismatch in criteria expression". I tried to found solution for it and all could figure is that problem lies in data in columns B and H (second and third choice) - they contain numbers. First choice - column A - contain text.

    Here is code that i'm using:

    Code:
    Dim con As Object
    Dim rs As Object
    Dim sql As String
    
    
    
    Private Sub ComboBox1_Change()
    
    If Not ComboBox1.text = "" Then
    Call Listbox
    Call Combo(sql)
    End If
    
    End Sub
    
    Private Sub ComboBox2_Change()
    
    If Not ComboBox2.text = "" Then
    Call Listbox
    Call Combo(sql)
    End If
    
    End Sub
    
    Private Sub ComboBox3_Change()
    
    If Not ComboBox3.text = "" Then
    Call Listbox
    Call Combo(sql)
    End If
    
    End Sub
    
    Private Sub CommandButton1_Click()
    
    Set con = Nothing
    
       ComboBox1 = Empty
       ComboBox2 = Empty
       ComboBox3 = Empty
    ListBox1.Clear
    Call UserForm_Initialize
    
    End Sub
    
    Private Sub UserForm_Initialize()
    
    Set con = CreateObject("adodb.connection")
    #If VBA7 And Win64 Then
    con.Open "provider=microsoft.ace.oledb.12.0;data source=" & ThisWorkbook.FullName & ";extended properties=""excel 12.0;hdr=no"""
    
    #Else
    con.Open "provider=Microsoft.jet.oledb.4.0;data source=" & ThisWorkbook.FullName & ";extended properties=""excel 8.0;hdr=no"""
    
    #End If
    
    Call CheckBox1_Click
    Call Combo("")
    
    End Sub
    
    Sub Listbox()
    
    Set ws1 = Sheets("BOM_Data")
    Set rng1 = ws1.Range("C:C")
    CountStanje = Application.CountA(rng1)
    
    sql = "select * from [BOM_Data$A2:T" & CountStanje & "] Where F1 is not null"
    If ComboBox1.text <> "" Then sql = sql & " and F1 = '" & ComboBox1.Value & "'"
    If ComboBox2.text <> "" Then sql = sql & " and F8 = '" & ComboBox2.Value & "'"
    If ComboBox3.text <> "" Then sql = sql & " and F2 = '" & ComboBox3.Value & "'"
    
    Set rs = con.Execute(sql)
    ListBox1.ColumnCount = rs.Fields.Count
    ListBox1.Column = rs.GetRows(rs.RecordCount)
    
    End Sub
    
    
    
    Sub Combo(ByVal Tabela As String)
    
    
    Set ws1 = Sheets("BOM_Data")
    Set rng1 = ws1.Range("C:C")
    
    CountStanje = Application.CountA(rng1)
    
    If Tabela = "" Then Tabela = "[BOM_Data$A2:T" & CountStanje & "]"
    ComboBox1.Column = con.Execute("select distinct F1 from (" & Tabela & ")").GetRows
    ComboBox2.Column = con.Execute("select distinct F8 from (" & Tabela & ")").GetRows
    ComboBox3.Column = con.Execute("select distinct F2 from (" & Tabela & ")").GetRows
    
    End Sub
    
    
    Private Sub CheckBox1_Click()
     
        If CheckBox1.Value = False Then
        Pretraga_zica.Caption = "Provera putem skeniranja KS kartice"
           TextBox1.Enabled = True
           TextBox1.BackColor = vbWhite
           ComboBox1.Enabled = False
           ComboBox1.BackColor = &H8000000F
           ComboBox2.Enabled = False
           ComboBox2.BackColor = &H8000000F
           ComboBox3.Enabled = False
           ComboBox3.BackColor = &H8000000F
        Else
        Pretraga_zica.Caption = "Provera putem rucnog odabira modula/zice"
           TextBox1.Enabled = False
           TextBox1.BackColor = &H8000000F
           ComboBox1.Enabled = True
           ComboBox1.BackColor = vbWhite
           ComboBox2.Enabled = True
           ComboBox2.BackColor = vbWhite
           ComboBox3.Enabled = True
           ComboBox3.BackColor = vbWhite
        End If
     
    End Sub
    Below comboboxes I have two listboxes. First will be used to give header for columns, second for data.
    I would like to have in them data from columns A, H and B, but it don't know is it even possible to define them like this. I know I can set Row source from A to H, but this isn't what I want.

    Any advice on both issue, please?

    Thanks for reading,

    Marinko

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,351

    Re: Run-Time error -2147217913 Data type mismatch in criteria expression

    Welcome to VBForums

    Thread moved from the 'VB.Net' forum to the 'Office Development/VBA' forum.

  3. #3
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,758

    Re: Run-Time error -2147217913 Data type mismatch in criteria expression

    you can not rely on recordsets from excel worksheet being the data type you expect

    if you just create a recordset "select * from worksheetname" for testing, you can look in the locals window to find the data types for each of the fields

    for me to help at all i would need a sample of the worksheet
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  4. #4
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,113

    Re: Run-Time error -2147217913 Data type mismatch in criteria expression

    a few things (since you're a beginner):
    1) Name your controls correctly. Right now i'm guessing, that commandbutton1 is the button right-hand-side of the 3 comboboxes. Name your controls properly, and there is a lot of less guesswork
    2) Never ever name your own functions and procedures the same as objects or controls. A "Sub ListBox()" can be very confusing reading the code.
    3) Get rid of the late binding (con and rs-objects). Use concrete classes. You would have to put a reference to it though, in "References". You might get a much better error-description out of it.
    4) As Pete said: Give us a sample-sheet, so we can test the SQL-statements
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  5. #5

    Thread Starter
    New Member
    Join Date
    Jan 2020
    Posts
    2

    Lightbulb Re: Run-Time error -2147217913 Data type mismatch in criteria expression

    First of all, thank you both for replaying to my post and on your suggestions.

    I didn't had enough time to make changes according your suggestions.

    In attachment you can found sample file as requested.

    Now I will try to explain what I would like to get with this:
    Comboboxes should have data from columns A, H and B in sheet BOM_Data
    When I select all 3, I need to find value for them from column X in sheet BOM_Data.
    Then in listboxes below to be shown all rows that contain same value in column X.
    Columns in listboxes should be A, B, H, P and Y.

    I made a printscreen for one example.
    My choices from comboboxes are marked in green and this is what I would like to get in listbox.

    I hope I was clear

    Command button "Pretraga1" will execute another code, that is irrelevant right now.

    Sorry for late response.

    Thanks in advance,

    Marinko
    Attached Images Attached Images  
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width