Runtime error 2147217900 (80040e14)

[Clod_13]

Hi guys, I do have a huge problem with my db,
I try to run a code, but I don't know why it comes up a runtime error 2147217900 (80040e14)

I've been looking for days for a solution, I tried to check on the internet, and someone (also in this forum) had this problem. Even if I try, I can't see any mistake in my code... could you please give it a check? I yellowed the part highlighted during the debug. thank guys
___________________________________________________________

Private Sub Assegnato_BeforeUpdate(Cancel As Integer)

Dim sqlCmd As String, nomecampo As String
If Assegnato.Value <> Assegnato.Tag Then


Select Case Assegnato.Tag
Case "Uff Val Cedenti": nomecampo = "Data Fine Val Cedenti"
Case "Resp Val Cedenti": nomecampo = "Data Fine Resp Val Cedenti"

End Select
sqlCmd = "UPDATE [DatePratiche] SET [DatePratiche].[" & _
nomecampo & "] = Date() " & _
"WHERE ([DatePratiche].NDG)= " & CStr(NDG)
CurrentProject.Connection.Execute sqlCmd

Select Case Assegnato.Value
Case "Uff Val Cedenti": nomecampo = "Data Inizio Val Cedenti"
Case "Resp Val Cedenti": nomecampo = "Data Inizio Resp Val Cedenti"


End Select
sqlCmd = "UPDATE [DatePratiche] SET [DatePratiche].[" & _
nomecampo & "] = Date() " & _
"WHERE ([DatePratiche].NDG)= " & CStr(NDG)
CurrentProject.Connection.Execute sqlCmd

End If
End Sub

 

[namliam]

First off when posting code please use the code tags, the # button in your post menu.

Try using
Currentdb.Execute
Instead of
CurrentProject.Connection

Also the CStr seems to suggest you are looking for a text field in your where clause...
If you are looking for text, then you need to (also) quote it like so:

    sqlCmd = "UPDATE [DatePratiche] SET [DatePratiche].[" & _
              nomecampo & "] = Date() " & _
              "WHERE ([DatePratiche].NDG)= """ & CStr(NDG) & """"

For a date you would use

              "WHERE ([DatePratiche].NDG)= #" & CStr(NDG) & "#"

Where NDG then needs to be in US date format, i.e. MM/DD/YYYY

Finaly using spaces in column names generaly is not best practice. and your table name leaves a little to be desired. Strongly suggest you take a look here:
http://www.access-programmers.co.uk/forums/showthread.php?t=225837&highlight=naming+convention

 

[Clod_13]

Still doesn't work... on the runtime error it says that 'datepratiche is not a valid name, but I don't really understand what to do, I also put it in square brackets... where am I wrong?

 

[namliam]

do you have a query/form or other object with the same name?

DatePatriche is supposed to be the tablename right?

 

[Clod_13]

yes is the table name.... no i don't have anything similar nor equal to this

 

[namliam]

The SQL seems proper to me, without the DB I am kindoff stuck sorry.

 

[gemma-the-husky]

take your latest version of this

sqlCmd = "UPDATE [DatePratiche] SET [DatePratiche].[" & _
nomecampo & "] = Date() " & _
"WHERE ([DatePratiche].NDG)= " & CStr(NDG)

now add this

msgbox(sqlcmd)

now try and record exactly what sqlcmd looks like, so we can identify the issue