UPDATE Command in VBA

[codeamin]

Hello everyone, I have been working with VBA code in MS Access 2003. I have a Command button which sends an email and then update the column of a table. Here is what I have... DoCmd.SendObject, , acForMatTXT, strWho, , , strSubject, strText, -1 strSQL = "UPDATE [Action Item Table] SET ysnActionAssigned = -1 " & _ "Where [Action Item Number] = " Me.Action_Item_Number & ";" On Error GoTo Err_Execute CurrentDB.Execute strSQL on Error GoTo 0 Err_Execute: ' Here I display error message in a message box I am getting an error of "Type mismatch". Can someone tell me what is going wrong. Thank you. Bashar Amin Sr. Software Engineer

[Mike Ellison]

Which line is triggering the Type Mismatch?

[codeamin]

CurrentDB.Execute strSQL When executing the SQL command. Bashar Amin Sr. Software Engineer

[Mike Ellison]

Are you explicitly using Dim to create the variable strSQL as a string?

[codeamin]

Yes. It is like a local variable in the same function. Bashar Amin Sr. Software Engineer

[Mike Ellison]

Try adding a Debug.Print line to output your constructed sql UPDATE statement to the immediate window. You can check to make sure the form values are being incorporated the way you think they are. You can also then copy and paste the actual statement from the immediate window and execute it through the SQL view of an access query. This is a good way to test whether or not there is a problem with the UPDATE statement itself.

[jonathan15]

I would guess it is this

codeamin wrote:

[Action Item Number] = " Me.Action_Item_Number & "

that is causing your problem. in particular the = " Me.Action_Item_Number & " You are missing the first &. As far as the query is concerned you are trying to enter the literal string value 'Me.Action_Item_Number &' into a numeric column. Jon

[codeamin]

Mike, Thank you for you valuable suggestion. Debug.print is a good way to troubleshoot software bug. I am still getting the "Type mismatch" error but it is updating the table. So I comment out the error check part for now. Bashar Amin Sr. Software Engineer

[codeamin]

Jon, Good thinking. Here is what I have my code now: Dim num as Integer num = Me.Item_Number strSQL = "UPDATE [Action Item Table] SET [Action Item Table].ysnActionAssigned = -1 " & _ "WHERE [Item Number] = " & num & ";" CurrentDB.Execute strSQL I was still getting a "Type Mismatch" error if I check for error. I comment out the error check as it was updating the [Action Item Table]. Thank's for your help. Bashar Amin Sr. Software Engineer

[Mike Ellison]

Hi Bashar. Wow - that's interesting. So the UPDATE command is successfully executing? AND you are getting a Type Mismatch error on the same line? Just to see what happens, try changing the line

CurrentDB.Execute strSQL

to

dim iResult as integer
iResult = CurrentDB.Execute(strSQL)