i have a list of data from database, when i click on an item from this list the value from list should go to the database through SQL criteria, but it doesn't happen, it throws "Data Type mismatch in criteria expression error" (error is hightighted with red in code part). Datatype of list is General (i changed also to a Number) and the column type in db is index (or number), what's wrong with the datatypes? Thanks in advance.

Public cnn As New ADODB.Connection
Public rst As New ADODB.Recordset
Public r As New ADODB.Recordset

Private Sub Form_Load()
    cnn.ConnectionString = _
     "Provider=Microsoft.Jet.OLEDB.4.0;" & _
     "Data Source=" & _
      "C:\8.mdb"
    cnn.Open
rst.Open "Select * from material", cnn, adOpenDynamic, adLockOptimistic
Do While Not rst.EOF
List1.AddItem rst(1)
rst.MoveNext
Loop
rst.Close
End Sub

Private Sub List1_Click()
rst.Open "SELECT price from material where id_material='" & List1 & "'", cnn, adOpenDynamic, adLockOptimistic
Text1 = IIf(IsNull(rst(0)), 0, rst(0))
rst.Close
End Sub

As a simple example...

Option Explicit

Dim SelectedListBoxItem As Integer

Private Sub Command1_Click()
MsgBox List1.List(SelectedListBoxItem)
End Sub

Private Sub Form_Load()
Dim ForLoopCounter As Integer
For ForLoopCounter = 1 To 10
  List1.AddItem CStr(ForLoopCounter)
Next ForLoopCounter
End Sub

Private Sub List1_Click()
SelectedListBoxItem = List1.ListIndex
End Sub

Now, the above example is for a string but you say your field is a number and I also see that your number is wrapped by single ticks ' . Well those denote that what you are passing is a string and not a number, hence the data type mismatch error.

Good Luck

This article has been dead for over six months. Start a new discussion instead.