Run-Time Error data type missmatch

[Misterpokey]

Good Day,

You guys gave me some pointers on my Asset Database a couple of days ago and I thank you for the help. After making the changes I am running into an error that I am at a loss with. I keep getting a "Run-Time Error '3464': Data type mismatch in criteria expression" for the 'Update" and "Delete" buttons on my forum. The "Add" button works just fine in adding records to the table. I am assuming I am missing something in the code.

I have attached print screens of the Table and the Form I put together and also the code will be below.




Code:


Private Sub cmdAdd_Click()


'When we click on button Add there are two options
'1. for insert
'2. for update
If Me.txtSerialNumber.Tag & "" = "" Then
'this is for insert new
'add data to table
CurrentDb.Execute "INSERT INTO ToBeProcessed(SerialNumber, ITGNumber, FixedAssetTagNumber, AssetName, AssetType, Department, AccountString, DateDeployed) " & _
" VALUES(" & Me.txtSerialNumber & ",'" & Me.txtITGNumber & "','" & _
Me.txtFixedAssetNumber & "','" & Me.txtAssetName & "','" & Me.cbcAssetType & "','" & Me.cbcDepartment & "','" & Me.txtAccountString & "','" & txtDateDeployed & "')"

Else
'otherwise (Tag of txt store the Asset number of item to be modified
CurrentDb.Execute "UPDATE ToBeProcessed " & _
" SET SerialNumber=" & Me.txtSerialNumber & _
", ITGNumber='" & Me.txtITGNumber & "'" & _
", FixedAssetTagNumber='" & Me.txtFixedAssetNumber & "'" & _
", AssetName='" & Me.txtAssetName & "'" & _
", AssetType='" & Me.cbcAssetType & "'" & _
", Department='" & Me.cbcDepartment & "'" & _
", AccountString='" & Me.txtAccountString & "'" & _


", DateDeployed='" & Me.txtDateDeployed & "'" & _
" Where SerialNumber=" & Me.txtSerialNumber.Tag

End If




'clear form
cmdClear_Click


'refresh data in list on form
frmToBeProcessedSub.Form.Requery


End Sub


Private Sub cmdClear_Click()
Me.txtSerialNumber = ""
Me.txtITGNumber = ""
Me.txtFixedAssetNumber = ""
Me.txtAssetName = ""
Me.cbcAssetType = ""
Me.cbcDepartment = ""
Me.txtAccountString = ""
Me.txtDateDeployed = ""

'focus on Serial Number Text Box
Me.txtSerialNumber.SetFocus
'set button edit to endable
Me.cmdEdit.Enabled = True
'change caption of button add to Add
Me.cmdAdd.Caption = "Add"
'clear tag on txtSerialNumber for reset new
Me.txtSerialNumber.Tag = ""


End Sub


Private Sub cmdClose_Click()


DoCmd.Close


End Sub


Private Sub cmdDelete_Click()
'delete record
'check existing selected record
If Not (Me.frmToBeProcessedSub.Form.Recordset.EOF And Me.frmToBeProcessedSub.Form.Recordset.BOF) Then
'confirm delete
If MsgBox("Are you sure you want to delete this Asset?", vbYesNo) = vbYes Then
'delete now
CurrentDb.Execute "DELETE FROM ToBeProcessed " & _
" WHERE SerialNumber=" & Me.frmToBeProcessedSub.Form.Recordset.Fields("Seri alNumber")
'refresh data
Me.frmToBeProcessedSub.Form.Requery
End If
End If
End Sub


Private Sub cmdEdit_Click()
'check wether there exists data in list
If Not (Me.frmToBeProcessedSub.Form.Recordset.EOF And Me.frmToBeProcessedSub.Form.Recordset.BOF) Then
'get data to text box control
With Me.frmToBeProcessedSub.Form.Recordset
Me.txtSerialNumber = .Fields("SerialNumber")
Me.txtITGNumber = .Fields("ITGNumber")
Me.txtFixedAssetNumber = .Fields("FixedAssetTagNumber")
Me.txtAssetName = .Fields("AssetName")
Me.cbcAssetType = .Fields("AssetType")
Me.cbcDepartment = .Fields("Department")
Me.txtAccountString = .Fields("AccountString")
Me.txtDateDeployed = .Fields("DateDeployed")
'store Item Number of asset in Tag of txtItemNumb in case Number is modified
Me.txtSerialNumber.Tag = .Fields("SerialNumber")
'change caption of button add to Update
Me.cmdAdd.Caption = "Update"
'disable button edit
Me.cmdEdit.Enabled = False
End With
End If
End Sub



Thanks all! I am trying to learn all this on the fly.

[CJ_London]

the error message is clear - you are mixing datatypes.
For example you have serialnumber as short text datatype, but are trying to provide a numeric datatype here

" VALUES(" & Me.txtSerialNumber & ",

and here

" WHERE SerialNumber=" & Me.frmToBeProcessedSub.Form.Recordset.Fields("SerialNumber")

with a field name like 'SerialNumber' I would expect the value to be numeric, so I suspect your field datatype is incorrect. If it is not numeric then the name is misleading - 'ABC' is not a number (which from the above code you think it should be) so I would rename as SerialCode or similar

[Misterpokey]

I can certainly change the name, the reason why I have it as short text and not numeric is because the Serial Numbers of the assets are alphanumeric. It seems based on what you said there is a alphanumeric datatype syntax i should be using? I had that suspicion but I don't have much experience with coding.

Thanks!

[CJ_London]

abc123 is text, 123 is numeric so you need to include the quotes as per text. There is not an alphanumeric datatype

[Misterpokey]

abc123 is text, 123 is numeric so you need to include the quotes as per text. There is not an alphanumeric datatype

Sorry Ajax, could you give me an example of where to put the quotes in the code? These basic code stuff is pretty new to me.

[CJ_London]

as you have here

" VALUES(" & Me.txtSerialNumber & ",'" & Me.txtITGNumber & "','" & _

[Misterpokey]

OK got it! All working now.

Thanks for the help.