VBA UPDATE SQL-error data mismatch

[infoscion]

Hi All:
I am getting a data mismatch error when I execute a Update statement through the Command Execute functionality in VBA. Is there a link between the field data type in the table and the string that is being inserted into the table. Advice is kindly solicited.
Regards,
Info

 

[PHV]

Is there a link between the field data type in the table and the string that is being inserted into the table
Obviously yes.
You may post your code explaining us the table structure.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[infoscion]

Hi PHV:
Here is the code. The error comes up as Type Mismatch and the Execute statement of the code is highlighted.



If (IsNull(Me.Text18)) Or ((Me.Text18) = "") Then
strSQL = "UPDATE Special SET [ID]='" & Str(Me!ID) & "',[Date_of_Procedure]='" & Str(Me.Text12) & "',[Type_Of_Procedure]='" & (Me.Text14) & "',[RoomNo]='" & Str(Me!Text16) & "',[Name]='" & Str(svalue) & "',[Timesent_for]='" & Str(Me!Text20) & "',[Time_a]='" & Str(Me!Text22) & "',[Time_c]='" & Str(Me!Text24) & "',[Time_r]='" & Str(Me!Text26) & "',[TimeX__notified]='" & Str(Me!Text28) & "',[TimeX_arrives]='" & Str(Me!Text30) & "',[Time_Procedure_begins]='" & Str(Me!Text32) & "',[Time_Procedure_ends]='" & Str(Me!Text42)
strSQL = strSQL & "',[TimeX_leaves_Room]='" & Str(Me!Text44) & "',[Time_Second_XNotified]='" & Str(Me!Text46) & "',[Time_Second_X_Arrives]='" & Str(Me!Text48) & "',[Special_notes]='" & (Me!Text50)
strSQL = strSQL & "' WHERE ID='" & Str(Me!_ID) & "';"
MsgBox "1"

MsgBox (strSQL)


Else
strSQL = "UPDATE Special SET [ID]='" & Str(Me!ID) & "',[Date]='" & Str(Me!Text12) & "',[Type]='" & (Me!Text14) & "',[RoomNo]='" & Str(Me!Text16) & "',[Name]='" & Str(Me!Text18) & "',[Time_sent_for]='" & Str(Me!Text20) & "',[Time_a]='" & Str(Me!Text22) & "',[Time_c]='" & Str(Me!Text24) & "',[Time_r]='" & Str(Me!Text26) & "',[TimeX_notified]='" & Str(Me!Text28) & "',[TimeX_arrives]='" & Str(Me!Text30) & "',[Time_Procedure_b]='" & Me!Text32 & "',[Time_Procedure_e]='" & Str(Me!Text42)
strSQL = strSQL & "',[TimeX_leaves_Room]='" & Str(Me!Text44) & "',[Time_SecondX_Notified]='" & Str(Me!Text46) & "',[Time_SecondX_Arrives]='" & Str(Me!Text48) & "',[Special_notes]='" & Me!Text50
strSQL = strSQL & "' WHERE ID='" & Str(Me!Patent_ID) & "';"



MsgBox "2"

MsgBox (strSQL)
End If

Msg = " You are about to update the record"
ans = MsgBox(Msg, vbYesNo)
If ans = vbNo Then
Me.ID.SetFocus
Else

CmdCommand.ActiveConnection = cn
'set the update sql statement to the command text
CmdCommand.CommandText = strSQL
'execute the command
CmdCommand.CommandType = adCmdText
CmdCommand.Execute

'End If

Set CmdCommand = Nothing
cn.Close
Set cn = Nothing


Also the underlying table sturcture is as follws:

ID==Number
RoomNo==Number
Type==Text
Name==Text
Special Notes=Text

rest of them are date/Time

Your inputs are greatly apprecaited.
Regards, Info

 

[PHV]

[ID]='" & Str(Me!ID) & "'
If ID is defined as numeric in Special you have to get rid of the single quotes, idem for RoomNo.
If the dates and times fields are defined as DateTime then replace the single quotes by hash (#)


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[infoscion]

Hi PHV:
It worked. Thank you my friend for your inputs.
Wondering as to how I can have a list box and/or a combo box retreive values from a table? Do I do it the same way as the text box or is there some thing else?
Regards,
Info

Regards,
Info

 

[PHV]

Take a look at the RowSourceType and RowSource properties of the LisBox/ComboBox objects.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[infoscion]

Hi PHV:
Another quick question. When I populate the text boxes of the form through the recordset and later make some changes to the text field then type mismatch comes up on the SQL update query. Any ideas.
Regards,
Info