12224

Access VBA: SQL query causes UPDATE syntax error

I have a database with linked tables- Staff, Courses and Training_Record. Each staff member has a numeric primary key, as does each course and each entry in the Training_Record table. The Staff_ID and Course_ID in the Training_Record reference records in Staff and Courses.

When a staff member or course is added, the Training_Record (fields: Staff_ID, Course_ID, Date_Taken, Notes) has staff,course records inserted- so adding staff member 1 would insert records (1,1,,,), (1,2,,,) etc, adding course 8 would insert records (1,8,,,), (2,8,,,) and so on. This works.

I then have a form to record training. The user selects the course, enters the date and selects staff members from a listbox. I have a save button which triggers VBA code. The date and course are pulled from the boxes and I loop round the listbox, concatenating selected staff members into a string. This all works and a message box displays, verifying that. Then, an update SQL query should be run, updating the Training_Record.

The problem I have is with the SQL update. I have an update query that will work in the SQL query editor, though it uses written in variables:

UPDATE Training_Record SET Date_Taken = '12/12/12' WHERE Staff_ID IN (1,2,3,4,5) AND Course_ID = 4

This updates the Training_Record to show that staff 1,2,3,4 and 5 took course 4 on 12/12/12. However, in VBA this will not work. This is my SQL query in VBA:

strSQL = "UPDATE Training_Record" _ & "SET Date_Taken = (" & strDate & ")" _ & "WHERE Staff_ID IN (" & strCriteria & ") AND Course_ID = (" & strCourse & ")" DoCmd.RunSQL strSQL

The error that the code generates is "Run-time error '3144': Syntax error in UPDATE statement." and the debugger highlights the DoCmd.RunSQL statement following the query.The entire VBA code:

Private Sub SaveTraining_Click() Dim db As DAO.Database Dim VarItem As Variant Dim strCriteria As String Dim strDate As Variant Dim strCourse As Variant Dim strSQL As String Set db = CurrentDb() 'Extract the course ID and the training date from the form strCourse = Me!CourseID.Value strDate = Me!TrainingDate.Value 'Dealing with empty boxes- zero length If IsNull(strCourse) Then MsgBox "Please select a course." _ , vbOKOnly, "No course selected" End If If IsNull(strDate) Then MsgBox "Please enter a date." _ , vbOKOnly, "No date given" End If If StaffMembers.ItemsSelected.Count = 0 Then MsgBox "Please select staff members." _ , vbOKOnly, "No staff members" End If If (Not IsNull(strCourse)) And (Not IsNull(strDate)) And (StaffMembers.ItemsSelected.Count > 0) Then 'Extract each selected member and concatenate into a string for sql query For Each VarItem In Me!StaffMembers.ItemsSelected strCriteria = strCriteria & "," & Me!StaffMembers.ItemData(VarItem) Next VarItem 'Gets rid of extra comma on query string strCriteria = Right(strCriteria, Len(strCriteria) - 1) 'Message box MsgBox ("Staff: " & strCriteria & vbNewLine & "Date: " & strDate & vbNewLine & "Course: " & strCourse & vbNewLine & "No. Selected staff: " & StaffMembers.ItemsSelected.Count) strSQL = "UPDATE Training_Record" _ & "SET Date_Taken = (" & strDate & ")" _ & "WHERE Staff_ID IN (" & strCriteria & ") AND Course_ID = (" & strCourse & ")" DoCmd.RunSQL strSQL End If Set db = Nothing End Sub

TL;DR I can't make a SQL UPDATE query run in VBA</b>

I've got a feeling that it's an error in syntax somewhere, but I can't find where. Any ideas/advice would be much appreciated, thanks.

Answer1:

I think you are simply missing spaces at the end of the lines

You old query print out

UPDATE Training_RecordSET Date_Taken = ()WHERE Staff_ID IN () AND Course_ID = ()

as you can see there will be a name collision before keywords SET and WHERE

therefore change your strSQL to

strSQL = "UPDATE Training_Record " _ & "SET Date_Taken = (" & strDate & ") " _ & "WHERE Staff_ID IN (" & strCriteria & ") AND Course_ID = (" & strCourse & ")"

which prints out as (with no values provided)

UPDATE Training_Record SET Date_Taken = () WHERE Staff_ID IN () AND Course_ID = ()

which in terms of SQL syntax is correct

If I were you I would also check the data types of columns in your Training_Record table

<strong>Usually</strong> (and this applies to Type-mismatch error),

for dates you wrap the variable or value on both sides with #

example & "SET Date_Taken = (#" & strDate & "#) ...

for strings you use single quotes '

example WHERE Operator_Name = ('" & operName & "') ...

for numerical values you do not need to use anything but casting to provide the correct data type

Answer2:

My guess:

strSQL = "UPDATE Training_Record" _ & "SET Date_Taken = (#" & Format(strDate, "mm\/dd\/yyyy") & "#)" _ & "WHERE Staff_ID IN (" & strCriteria & ") AND Course_ID = (" & strCourse & ")"

If staff_ID is a string:

strSQL = "UPDATE Training_Record" _ & "SET Date_Taken = (#" & Format(strDate, "mm\/dd\/yyyy") & "#)" _ & "WHERE Staff_ID IN ('" & strCriteria & "') AND Course_ID = (" & strCourse & ")"

Recommend

  • ADODB Recordset.RecordCount giving incorrect answer
  • C# SQLDataAdapter - Not inserting data into DB
  • Unable to create a second dataframe python pandas
  • MS Access using forms in a pass-through query
  • How do I embed a custom user-control in MS Word?
  • gulp.watch running same task multiple times when saving many files
  • Install different versions of nuget packages inside one solution file with two projects
  • goJS dropdown remove items
  • CKeditor stripping font tags instead of converting to span
  • How can I add div content to a new page in jspdf?
  • Manually Timing out a C# Thread
  • Jenkins Grails plugin does not list lastest versions of Grails
  • JSR-330 support in Picocontainer : @Inject … @Named(\"xxx)
  • Insert new calendar with SyncAdapter- Calendar API Android
  • Ensure fsync did its job
  • Problems to linebreak with an int in JLabel
  • Sony Xperia Z Tablet not found by adb
  • Check if a string to interpolate provides expected placeholders
  • How to extract text from Word files using C#?
  • javascript inside java/jsp code
  • Sending data from AppleScript to FileMaker records
  • Rearranging Cells in UITableView Bug & Saving Changes
  • Circular dependency while pushing http interceptor
  • retrieve vertices with no linked edge in arangodb
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • Linker errors when using intrinsic function via function pointer
  • KeystoneJS: Relationships in Admin UI not updating
  • AngularJs get employee from factory
  • Load html files in TinyMce
  • How to set the response of a form post action to a iframe source?
  • Understanding cpu registers
  • FormattedException instead of throw new Exception(string.Format(…)) in .NET
  • Change div Background jquery
  • Qt: Run a script BEFORE make
  • Are Kotlin's Float, Int etc optimised to built-in types in the JVM? [duplicate]
  • Add sale price programmatically to product variations
  • Does armcc optimizes non-volatile variables with -O0?
  • reshape alternating columns in less time and using less memory
  • How can i traverse a binary tree from right to left in java?
  • Conditional In-Line CSS for IE and Others?