Getting the error data type conversion error running this code. the data is from text boxes set to data type rich text.
CurrentDb.Execute "UPDATE tblISPServices", "SET Location=& Me.cboLocation,ServiceType = & Me.cboSType, BBProvider = & Me.txtBBProvider" & "WHERE ID= & Me.txtID.Tag"
please help
Remember to get variable or control values from the VBA code and rest should be string inside double quotes.
When generating such SQL statement to execute remember 3 rules
1) String data type fields should have single quote for string value so I had put single quote before & after location for e.g. '" & Me.cboLocation & "' 2) Number data type do not need single quote, so remove single quote for ID field for e.g. ID= " & Me.txtID.Tag 3) Date data type will have # instead of single quote for e.g. #" & Now() & "#
My assumption is , in the table tblISPServices field Location is Text Data type, ServiceType is Text data type, DBProvider is Text data type and ID is Numeric.
Observe how I made changes to get variable(control property) from VBA to generate String variable.
CurrentDb.Execute "UPDATE tblISPServices SET Location= '" & Me.cboLocation & "', ServiceType = '" & Me.cboSType & "', BBProvider = '" & Me.txtBBProvider & "' WHERE ID= " & Me.txtID.Tag
Note : remember while generating such string make sure you do not join any keyword to string or number for e.g. following is wrong SQL since it does not separate where
BBProvider = '" & Me.txtBBProvider & "'WHERE ID= " & Me.txtID.Tag
Correct SQL would be,
BBProvider = '" & Me.txtBBProvider & "' WHERE ID= " & Me.txtID.Tag
