CurrentDb.Execute Update 2

[jsummerville]

Hello, I am using a button in an unbound form that will use an update sql to place currency fields into a table. I am receiving a error for a datatype mismatch. I am very new to VBA and self taught, Can someone help me? The code is posted below.

Option Compare Database
Option Explicit

Private Sub btnSubmit_Click()
If IsNull(Me.txtFullname) Then
MsgBox "Bro, select a student to adjust!", vbOKOnly
Else
Dim db As Database
Set db = CurrentDb
Dim strUpdateSQL As String

strUpdateSQL = "Update Test_tblFY17_ConvEstimates " _
& "SET Summer_Stipend = '" & Me.txtSumStipend & "' , Summer_Housing = '" & Me.txtSumHousing & "' , Summer_Tuition = '" & Me.txtSumTuition & "' " _
& " WHERE EMPLID = '" & Me.txtStudentID & "';"
Debug.Print strUpdateSQL '<--- PRINT TO IMMEDIATE WINDOW FOR CHECKING
db.Execute strUpdateSQL

End If
End Sub

[jonh]

If the datatype of the field in the table is text you wrap values in quotes.
If it's numeric you don't use quotes.

update my_table set field1='field1 is text so requires quotes', field2=1234, field3=5678

[jsummerville]

Can I remove the single and double quotes in the set side of the syntax? In the unbound form I have the text boxes set to currency and in the table the field type is currency. IS that a good practice?

[jonh]

You can remove the single quotes but double quotes are generally used to build the sql string so I would leave them in.

[jsummerville]

No success. The information on the form is passed from a search form. I am using it to take numbers from one field and update it to another field. Is there a way I can convert the the syntax to currency?

[jonh]

What line do you get the error?

Currency is numeric.

Re SQL: There are 3 basic data types - text, number and date.

text is wrapped in quotes - 'hello'
date/times are either text like '14-april-2017' or explicit US format #4/14/2017#
and numbers are just numbers.

Can't help more than that unless you can post up a copy of your file.