UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
> Syntax, Access 2016    
 
   
mike60smart
post Feb 14 2020, 04:16 PM
Post#1


UtterAccess VIP
Posts: 14,018
Joined: 6-June 05
From: Dunbar,Scotland


Hi Everyone

I am getting the following error when I try to run the following On Click Event.

Attached File  error.PNG ( 4.97K )Number of downloads: 2


CODE
Private Sub cmdUpdateCourse_Click()

10        On Error GoTo cmdUpdateCourse_Click_Error

          Dim dtCourseDate As Date
20        dtCourseDate = Me.dtCourseDate
      Dim strSQL As String
30    strSQL = "UPDATE jtblPositionTraining SET CourseDate = " & Me.dtCourseDate & " WHERE EmployeeNr = " & Me.EmpNr & " AND TrainingDetails = " & Me.txtCourse & ";"
40    Debug.Print strSQL
50    CurrentDb.Execute strSQL, dbFailOnError

55      MsgBox "AllDates Updated", vbInformation
60        On Error GoTo 0
70        Exit Sub

cmdUpdateCourse_Click_Error:

80        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdUpdateCourse_Click, line " & Erl & "."

End Sub


The Immediate windows displays the following correct values:-

UPDATE jtblPositionTraining SET CourseDate = 14/02/2020 WHERE EmployeeNr = 81768 AND TrainingDetails = Knife Safety;

Can anyone point out where I am going wrong?

Any help appreciated.


--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
 
Start new topic
Replies
cheekybuddha
post Feb 14 2020, 04:26 PM
Post#2


UtterAccess Moderator
Posts: 13,006
Joined: 6-December 03
From: Telegraph Hill


Hi Mike,

Single quotes required around the string value 'knife safety'

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
mike60smart
post Feb 14 2020, 04:45 PM
Post#3


UtterAccess VIP
Posts: 14,018
Joined: 6-June 05
From: Dunbar,Scotland


Hi David

Changed code to this:-

CODE
Private Sub cmdUpdateCourse_Click()

10        On Error GoTo cmdUpdateCourse_Click_Error

          Dim dtCourseDate As Date
20        dtCourseDate = Me.dtCourseDate
      Dim strSQL As String
30    strSQL = "UPDATE jtblPositionTraining SET CourseDate = " & Me.dtCourseDate & " WHERE EmployeeNr = " & Me.EmpNr & " AND TrainingDetails = '" & Me.txtCourse & "';"
40    Debug.Print strSQL
50    CurrentDb.Execute strSQL, dbFailOnError

55      MsgBox "AllDates Updated", vbInformation
60        On Error GoTo 0
70        Exit Sub

cmdUpdateCourse_Click_Error:

80        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdUpdateCourse_Click, line " & Erl & "."

End Sub


Now get this error:-
Attached File  error.PNG ( 3.2K )Number of downloads: 0


Immediate Window shows this:-
UPDATE jtblPositionTraining SET CourseDate = 14/02/2020 WHERE EmployeeNr = 81768 AND TrainingDetails = 'Knife Safety';


--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
fkegley
post Feb 17 2020, 07:57 PM
Post#4


UtterAccess VIP
Posts: 23,787
Joined: 13-January 05
From: Mississippi


I cannot write syntax for you, but I would suggest using the EVAL function around each of the suspect entries.

EVAL('expressongoeshere')---Note the 's around the expression. This forces the particular expression to be evaluated first, then the value is placed in the SQL statement.

--------------------
Frank Kegley, Microsoft Access 2010 MVP
Go to the top of the page
 

Posts in this topic



Custom Search


RSSSearch   Top   Lo-Fi    6th July 2020 - 03:43 AM