Member Avatar for shazib07

Hello guys, i am facing problem while updating database. I get "data type mismatch in criteria expression" error when click update button.
Necessary info:
*The database i am using is creadted ms office acess appliaction.

*I have created a module where i have putted connection data in a sub function named sub connect(), which works well while inserting records.

Here is the coding i use:

Private Sub Form_Load()
Call connect
End Sub

Private Sub update_Click()
Dim i As Integer
i = InputBox("enter employee id to edit")
rcd.Filter = "employeeid=" & Val(i)
If rcd.RecordCount > 0 Then
With cmd
.CommandText = "update employee set employeeid =" & Val(Text1.Text) & "," & "name=" & "'" & Text2.Text & "'" & " " & " where employeeid=" & val(i)
.CommandType = adCmdText
.ActiveConnection = con
.Execute
End With
Else
MsgBox "record not found"
End If
rcd.Filter = adFilterNone
rcd.Requery
con.Close
End Sub
Edited by shazib07
  • 2 Contributors
  • 1 Reply
  • 162 Views
  • 2 Hours Discussion Span
  • Latest Post Latest Post by AndreRet
Member Avatar for AndreRet

Your CommandText is incorrect -

.CommandText = "update employee set employeeid ='" & Val(Text1.Text) & "', name='" & Text2.Text & "' WHERE employeeid='" & val(i) & "'"

''If empid is ONLY a number, you do not have to use the ' to enclose the id...
''Also, you can not set the employeeid if that is what you have selected in your WHERE clause, especialy if it was set to an autonumber field...
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.