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.
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
therefore change your
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
<strong>Usually</strong> (and this applies to Type-mismatch error),
for dates you wrap the variable or value on both sides with
& "SET Date_Taken = (#" & strDate & "#) ...
for strings you use single quotes
WHERE Operator_Name = ('" & operName & "') ...
for numerical values you do not need to use anything but casting to provide the correct data type
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 & ")"