我有一个链接表 - 工作人员,课程和Training_Record数据库。每个工作人员都有一个数字主键一样,每门课程,并在Training_Record表中的每个条目。该Staff_ID和COURSE_ID在工作人员和培训班Training_Record引用记录。
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.
当工作人员或过程中添加,则Training_Record(字段:Staff_ID,COURSE_ID,Date_Taken,债券)有工作人员,病程记录inserted-因此增加工作人员1将插入记录(1,1 ,,,),( 1,2 ,,,)等,当然,加入8将插入(1,8 ,,,),(2,8 ,,,)等记录。这工作。
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.
然后我有一个表格来记录训练。用户选择的过程中,输入的日期和选择的工作人员从一个列表框。我有一个保存按钮,触发VBA code。日期,当然是从箱子和我循环圆拉列表框,选择串联工作人员为字符串。这一切工作,并显示一个消息框,验证。然后,更新SQL查询应运行,更新Training_Record。
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.
我的问题是与SQL更新。我有一个更新查询,将工作在SQL查询编辑器,虽然它采用写在变量:
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
这将更新Training_Record表明,工作人员1,2,3,4和5把课程4 12年12月12日。然而,在VBA这是不行的。这是在VBA我的SQL查询:
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
这在code产生的错误是运行时错误3144:在UPDATE语句的语法错误。和调试器突出了DoCmd.RunSQL语句后面的query.The整个VBA code:
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我不能让一个SQL UPDATE查询运行在VBA
我有一种感觉,它在语法错误的地方,但我找不到在哪里。任何想法/意见将是多少AP preciated,谢谢。
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
您旧的查询打印出来
UPDATE Training_RecordSET Date_Taken = ()WHERE Staff_ID IN () AND Course_ID = ()
你可以看到会有关键字之前的名称冲突设置
和,其中
因此改变你的 STRSQL
到
strSQL = "UPDATE Training_Record " _
& "SET Date_Taken = (" & strDate & ") " _
& "WHERE Staff_ID IN (" & strCriteria & ") AND Course_ID = (" & strCourse & ")"
打印出的(没有值提供的)
UPDATE Training_Record SET Date_Taken = () WHERE Staff_ID IN () AND Course_ID = ()
这在SQL语法而言是正确的。
which in terms of SQL syntax is correct
如果我是你,我也将检查你的 Training_Record
表列的数据类型
If I were you I would also check the data types of columns in your Training_Record
table
一般 (并且适用于所有类型不匹配错误的),
有关的约会,你与#
例如&放大器; SET Date_Taken =(#&放大器; strDate&放大器;#)...
对于字符串使用单引号
for strings you use single quotes '
例如其中Operator_Name =('&放大器; operName和放大器;)...
有关的数值,你不需要使用任何东西,但铸件提供正确的数据类型
for numerical values you do not need to use anything but casting to provide the correct data type
相关推荐
最新文章