3464 (data type mismatch in criteria expression) in Select Statement

[furnitureheaven]

Hi,
I am writing a code in VBA, but receive a error No 3464 (data type mismatch in criteria expression)

I have a table with 2 columns (Ftype and FRefNo) and I also have a form with a Combo Box (Combo8) and a Text box (frmRefNo).

I want, when user select value in Combo box (combo8) it should bring a matching RefNo from table and show into the text box (frmRefNo) on the form, and update with plus 1 in the FRefNo.

I have write a code in Combo Box Value Change Event.

My code is here

Private Sub Combo8_Change()
frmRefNo.SetFocus
Dim rs As DAO.Recordset
 
Set rs = CurrentDb.OpenRecordset("SELECT FRefNo FROM LegLook Where (((Ftype)= '" & [Forms]![LegFile].[Combo8] & "'));")
Dim SQLtext As String
SQLtext = rs("FRefNO").Value
frmRefNo = SQLtext
rs.Close
Set rs = Nothing
DoCmd.RunSQL "UPDATE LegLook SET LegLook.FRefNo = LegLook.FRefNo+1 WHERE (((LegLook.Ftype)=&Forms!LegFile!Me.Combo8));"
 
End Sub

 

[WayneRyan]

fh,

Remove these apostrophes:

Set rs = CurrentDb.OpenRecordset("SELECT FRefNo FROM LegLook Where (((Ftype)= '" & [Forms]![LegFile].[Combo8] & "'));")

They're not needed as Ftype is a number.

Also, use the BeforeUpdate event of your combo. The OnChange event
is really for only when you're typing in values ... and will fire for EVERY
keystroke the user makes.

hth,
Wayne

 

[furnitureheaven]

fh,

Remove these apostrophes:

Set rs = CurrentDb.OpenRecordset("SELECT FRefNo FROM LegLook Where (((Ftype)= '" & [Forms]![LegFile].[Combo8] & "'));")

They're not needed as Ftype is a number.

Also, use the BeforeUpdate event of your combo. The OnChange event
is really for only when you're typing in values ... and will fire for EVERY
keystroke the user makes.

hth,
Wayne

Great, that’s solved, could you also help me in the update query. When its update the record, its show message “Your are about to update 1 row(s).” I don’t want to show this message on update record.

Could you tell me how I could stop this message? I have an update code as this

DoCmd.RunSQL "UPDATE LegLook SET LegLook.FRefNo = LegLook.FRefNo+1 WHERE (((LegLook.Ftype)= " & [Forms]![LegFile].[Combo8] & "));"