Access 2016 VBA 无法设置按钮单击表单以将多值字段写入第二个表 [英] Access 2016 VBA unable to set button click on form to write multi-value fields to second table
问题描述
我正在构建一个应用程序来跟踪项目、员工等之间的时间.我工作的公司有重要的安全繁文缛节，所以我必须使用 MS Access 2016，我被禁止在 youtube 上搜索解决方案，我不能从互联网下载任何文件.幸运的是，我仍然可以访问 StackOverflow.
我有一个带有按钮的输入表单(Task_List_Entry_Form").单击时，按钮将值发送到表(tbl_Task_List").在表单 VBA 中，我已经包含了表(tbl_Task_List")刷新，并且所有值都转到另一个充当更正日志(tbl_Task_List_Corrections")的表中.但是多值字段(我知道，它们是最糟糕的，如果它不是看似必要的东西，我不会包括它们)不能去.所以我在 VBA 中包含了第二个查询来获取多值字段的值并将其输入到字段中，但到目前为止这一直没有成功.
第一个查询选项如下所示:strSQL2 = "INSERT INTO tbl_Task_List_Corrections " &标签.价值 &从 tbl_Task_List" &哪里 tbl_Task_List.ID IN(从 tbl_Task_List 中选择 MAX(tbl_Task_List.ID))；"'DoCmd.RunSQL strSQL2
结果:抛出错误Run-Time error '3134': Syntax error in INSERT INTO statement"，错误在DoCmd.RunSQL strSQL2"行，非多值字段传输正确，但未拉取值，tbl_Task_List_Corrections.Tags 字段保持为 NULL.
然后我尝试了更像这样的东西:CurrentDb.Execute "INSERT INTO tbl_Task_List_Corrections (Tags) VALUES ('" & Me.[Tags].Value & "');"
结果:没有抛出错误，但是没有拉取值，还有一个额外的行被插入到tbl_Task_List_Corrections"表中
我可以尝试第三个解决方案，我在这个来源找到，但是它基于一个示例访问数据库，由于安全限制，我无法下载和查看，我想不通仅从代码中出来.这是该来源:https://www.utteraccess.com/forum/index.php?s=66e048c0880b46f8bd25a0541c30df63&showtopic=2018212&st=0&p=2463687&#entry2463687这不是全部代码，而是与保存表单后移动值有关的代码.
Me.Requery '刷新使表完整Dim strSQL1 As String '声明非多值字段Dim strSQL2 As String '再次尝试多值字段strSQL1 = "插入 tbl_Task_List_Corrections (Task_ID, Task_Title,Task_Start_Time、Task_End_Time、Break_Length_Minutes、优先级、Blockage_Reason、需求、注意事项、Task_Complete) SELECTtbl_Task_List.ID、tbl_Task_List.Task_Title、tbl_Task_List.Task_Start_Time、tbl_Task_List.Task_End_Time, tbl_Task_List.Break_Length_Minutes,tbl_Task_List.Priority, tbl_Task_List.Blockage_Reason,tbl_Task_List.Requirements, tbl_Task_List.Notes,tbl_Task_List.Task_Complete FROM tbl_Task_List WHERE tbl_Task_List.ID IN(SELECT MAX(tbl_Task_List.ID) FROM tbl_Task_List);"DoCmd.RunSQL strSQL1'strSQL2 = "插入 tbl_Task_List_Corrections " &标签.价值 &"从tbl_Task_List " & "WHERE tbl_Task_List.ID IN (SELECT MAX(tbl_Task_List.ID)从 tbl_Task_List);"'DoCmd.RunSQL strSQL2 '这是尝试 1'CurrentDb.Execute "INSERT INTO tbl_Task_List_Corrections (Tags) VALUES ('"&我.[标签].价值 &"');"'这是尝试 2Dim db As Database 'Attempt4Dim rs As Recordset 'Attempt4Dim childRS 作为 Recordset 'Attempt4'尝试4Set db = CurrentDb() 'Attempt4'尝试4' 打开任务表的记录集.'尝试4Set rs = db.OpenRecordset("tbl_Task_List") 'Attempt4rs.MoveFirst '尝试 4'尝试4直到 rs.EOF 'Attempt4' 将任务名称打印到立即窗口.'尝试4Debug.Print rs!Task_Title.Value 'Attempt4'尝试4' 打开多值字段的记录集.'尝试4设置 childRS = rs!Tags.Value 'Attempt4'尝试4' 如果多值字段不包含记录，则退出循环 'Attempt4做直到 childRS.EOF 'Attempt4childRS.MoveFirst 'Attempt4'尝试4' 循环遍历子记录集中的记录.'尝试4做直到 childRS.EOF 'Attempt4' 将任务的所有者打印到 Immediate 'Attempt4' 窗户.'尝试4Debug.Print Chr(0), childRS!Value.Value 'Attempt4childRS.MoveNext 'Attempt4循环'尝试4循环'尝试4rs.MoveNext '尝试 4循环'尝试4MsgBox "您已成功添加此任务"DoCmd.Close结束子下面是尝试5Dim db As Database 'Attempt5Dim rs As Recordset 'Attempt5Dim rs2 As Recordset '定义 tbl_Task_List_CorrectionsDim childRS 作为 Recordset 'Attempt5'尝试5Set db = CurrentDb() 'Attempt5'尝试5' 打开任务表的记录集.'尝试5Set rs = db.OpenRecordset("tbl_Task_List") 'Attempt5Set rs2 = db.OpenRecordset("tbl_Task_List_Corrections") '设置tbl_Task_List_Corrections的值rs.MoveLast 'Attempt5'尝试5'直到 rs.EOF 'Attempt5
' 将任务名称打印到立即窗口.'尝试5Debug.Print rs!ID.Value 'Attempt5Debug.Print rs!Task_Title.Value 'Attempt5Debug.Print rs!Priority.Value 'Attempt5Debug.Print rs!Blockage_Reason.Value 'Attempt5Debug.Print rs!Requirements.Value 'Attempt5Debug.Print rs!Notes.Value 'Attempt5' 打开多值字段的记录集.'尝试5设置 childRS1 = rs!Tags.Value 'Attempt5设置 childRS2 = rs!Assigned_To.Value'尝试5' 如果多值字段不包含记录，则退出循环.'尝试5做直到 childRS1.EOF 'Attempt5childRS1.MoveFirst 'Attempt5'尝试5' 循环遍历子记录集中的记录.'尝试5做直到 childRS1.EOF 'Attempt5' 将任务的所有者打印到 Immediate 'Attempt5' 窗户.'尝试5Debug.Print Chr(0), childRS1!Value.Value 'Attempt5childRS1.MoveNext 'Attempt5循环'尝试5Loop '检查标签多值字段是否为空的循环结束' 如果多值字段不包含记录，则退出循环.'尝试5做直到 childRS2.EOF 'Attempt5childRS2.MoveFirst 'Attempt5' 循环遍历子记录集中的记录.'尝试5做直到 childRS2.EOF 'Attempt5' 将任务的所有者打印到 Immediate 'Attempt5' 窗户.'尝试5Debug.Print Chr(0), childRS2!Value.Value 'Attempt5childRS2.MoveNext 'Attempt5循环'尝试5Loop '检查 Assigned_To 多值字段是否为 NULL 的循环结束rs2.AddNew '尝试 5rs2!Task_ID = rs!ID.Value 'Attempt5rs2!Task_Title = rs!Task_Title 'Attempt5rs2!Tags = childRS1!Value.Value 'Attempt5rs2!Priority = rs!Priority 'Attempt5rs2!Assigned_To = childRS2!Value.Value 'Attempt5rs2!Blockage_Reason = rs!Blockage_Reason 'Attempt5rs2!Requirements = rs!Requirements 'Attempt5rs2!Notes = rs!Notes 'Attempt5rs2.Update '尝试 5以下是尝试 7，参考 4/13 的答案(4/23 再次编辑)
Me.Requery '在运行查询之前刷新表Dim strSQL1 As String '声明非多值字段移动到更正日志strSQL1 = "插入 tbl_Task_List_Corrections (Task_ID, Task_Title, " & _"Task_Start_Time, Task_End_Time, Break_Length_Minutes, Priority, " &_阻塞_原因、要求、注意事项、任务_完成)" &_"选择 ID，任务_标题，任务_开始_时间，任务_结束_时间，" &_Break_Length_Minutes, Priority, Blockage_Reason, 要求，" &_注释，Task_Complete FROM tbl_Task_List WHERE ID IN" &_(从 tbl_Task_List 中选择 MAX(ID))；"DoCmd.RunSQL strSQL1Dim rs As DAO.RecordsetSet rs = CurrentDb.OpenRecordset("SELECT ID FROM tbl_Task_List") '错误 3061Set rs = CurrentDb.OpenRecordset("SELECT MAX(ID) FROM tbl_Task_List") '3265直到 rs.EOFCurrentDb. 执行插入"&_" tbl_Task_List_Corrections(Tags.Value) SELECT Tags.Value FROM " &_" (SELECT Tags.Value FROM tbl_Task_List WHERE ID = " & rs!ID & ") " &_" AS T1 WHERE tbl_Task_List_Corrections.Task_ID = " &rs!IDrs.MoveNext环形MsgBox "您已成功添加此任务"'DoCmd.SetWarnings False '重新打开您将要更新 1 行"警告DoCmd.Close结束子结果比我想象的要简单得多.看起来最初的尝试是在正确的轨道上.
创建具有非 MVF 数据的新记录后，运行另一个 INSERT SELECT 操作以填充 MVF.示例显示在复制记录的循环中填充第二个表 MVF.测试和工作.对表单上的记录标识符指定的单个记录执行 SQL 操作应该很简单，而不是循环记录集.
Dim rs As DAO.RecordsetSet rs = CurrentDb.OpenRecordset("SELECT RateID FROM CopyOfRates")直到 rs.EOFCurrentDb.Execute "INSERT INTO CopyOfRates(MVTest.Value) SELECT MVTest.Value FROM " &_"(SELECT MVTest.Value FROM Rates WHERE RateID = " & rs!RateID & ") AS Q1 " &_"WHERE CopyOfRates.RateID = " &rs!RateIDrs.MoveNext环形I am building an application to track time amongst project, employees, etc. The company I work for has significant security red tape, so I have to use MS Access 2016, I am barred from youtube to search solutions, and I cannot download any files from the internet. Luckily, I can still access StackOverflow.
I have an entry form ("Task_List_Entry_Form") that has a button. On click, the button sends values to a table ("tbl_Task_List"). In the form VBA I've included that the table ("tbl_Task_List") refreshes, and all the values go to another table that acts as a corrections log ("tbl_Task_List_Corrections"). But the multi-value fields (I know, they are the worst, if it wasn't something that was seemingly necessary, I wouldn't include them) can't go. So I included a 2nd query in the VBA to take the value of the multi-valued fields and input into the fields, but this has been unsuccessful so far.
The first query option looked like this:
strSQL2 = "INSERT INTO tbl_Task_List_Corrections " & Tags.Value & " FROM tbl_Task_List " & "WHERE tbl_Task_List.ID IN (SELECT MAX(tbl_Task_List.ID) FROM tbl_Task_List);"
'DoCmd.RunSQL strSQL2
Result: Error thrown "Run-Time error '3134': Syntax error in INSERT INTO statement", with the error on line "DoCmd.RunSQL strSQL2", The non-multi-value fields transferred correctly, but the value was not pulled, the tbl_Task_List_Corrections.Tags field remains NULL.
Then I tried something more like this:
CurrentDb.Execute "INSERT INTO tbl_Task_List_Corrections (Tags) VALUES ('" & Me.[Tags].Value & "');"
Result: No error was thrown, but the value was not pulled, and there is an extra row that was inserted into the "tbl_Task_List_Corrections" table
There is potentially a 3rd solution I could try, that I found at this source, however it is based on an example access database that due to security restrictions, I am unable to download and look at, and I could not figure it out from the code alone. Here is that source: https://www.utteraccess.com/forum/index.php?s=66e048c0880b46f8bd25a0541c30df63&showtopic=2018212&st=0&p=2463687&#entry2463687
This is not all the code, but the code that has to do with moving values after the form is saved.
Me.Requery 'Refresh so the table is complete
Dim strSQL1 As String 'Declaring the Non-Multivalued fields
Dim strSQL2 As String 'attempting the multi-value fields again
strSQL1 = "INSERT INTO tbl_Task_List_Corrections (Task_ID, Task_Title,
Task_Start_Time, Task_End_Time, Break_Length_Minutes, Priority,
Blockage_Reason, Requirements, Notes, Task_Complete) SELECT
tbl_Task_List.ID, tbl_Task_List.Task_Title, tbl_Task_List.Task_Start_Time,
tbl_Task_List.Task_End_Time, tbl_Task_List.Break_Length_Minutes,
tbl_Task_List.Priority, tbl_Task_List.Blockage_Reason,
tbl_Task_List.Requirements, tbl_Task_List.Notes,
tbl_Task_List.Task_Complete FROM tbl_Task_List WHERE tbl_Task_List.ID IN
(SELECT MAX(tbl_Task_List.ID) FROM tbl_Task_List);"
DoCmd.RunSQL strSQL1
'strSQL2 = "INSERT INTO tbl_Task_List_Corrections " & Tags.Value & " FROM
tbl_Task_List " & "WHERE tbl_Task_List.ID IN (SELECT MAX(tbl_Task_List.ID)
FROM tbl_Task_List);"
'DoCmd.RunSQL strSQL2 'THIS IS ATTEMPT 1
'CurrentDb.Execute "INSERT INTO tbl_Task_List_Corrections (Tags) VALUES ('"
& Me.[Tags].Value & "');" 'THIS IS ATTEMPT 2
Dim db As Database 'Attempt4
Dim rs As Recordset 'Attempt4
Dim childRS As Recordset 'Attempt4
'Attempt4
Set db = CurrentDb() 'Attempt4
'Attempt4
' Open a Recordset for the Tasks table. 'Attempt4
Set rs = db.OpenRecordset("tbl_Task_List") 'Attempt4
rs.MoveFirst 'Attempt4
'Attempt4
Do Until rs.EOF 'Attempt4
' Print the name of the task to the Immediate window. 'Attempt4
Debug.Print rs!Task_Title.Value 'Attempt4
'Attempt4
' Open a Recordset for the multivalued field. 'Attempt4
Set childRS = rs!Tags.Value 'Attempt4
'Attempt4
' Exit the loop if the multivalued field contains no records 'Attempt4
Do Until childRS.EOF 'Attempt4
childRS.MoveFirst 'Attempt4
'Attempt4
' Loop through the records in the child recordset. 'Attempt4
Do Until childRS.EOF 'Attempt4
' Print the owner(s) of the task to the Immediate 'Attempt4
' window. 'Attempt4
Debug.Print Chr(0), childRS!Value.Value 'Attempt4
childRS.MoveNext 'Attempt4
Loop 'Attempt4
Loop 'Attempt4
rs.MoveNext 'Attempt4
Loop 'Attempt4
MsgBox "You have successfully added this Task"
DoCmd.Close
End Sub
Below is attempt 5
Dim db As Database 'Attempt5
Dim rs As Recordset 'Attempt5
Dim rs2 As Recordset 'Defining the tbl_Task_List_Corrections
Dim childRS As Recordset 'Attempt5
'Attempt5
Set db = CurrentDb() 'Attempt5
'Attempt5
' Open a Recordset for the Tasks table. 'Attempt5
Set rs = db.OpenRecordset("tbl_Task_List") 'Attempt5
Set rs2 = db.OpenRecordset("tbl_Task_List_Corrections") 'Setting the value of tbl_Task_List_Corrections
rs.MoveLast 'Attempt5
'Attempt5
'Do Until rs.EOF 'Attempt5
' Print the name of the task to the Immediate window. 'Attempt5
Debug.Print rs!ID.Value 'Attempt5
Debug.Print rs!Task_Title.Value 'Attempt5
Debug.Print rs!Priority.Value 'Attempt5
Debug.Print rs!Blockage_Reason.Value 'Attempt5
Debug.Print rs!Requirements.Value 'Attempt5
Debug.Print rs!Notes.Value 'Attempt5
' Open a Recordset for the multivalued field. 'Attempt5
Set childRS1 = rs!Tags.Value 'Attempt5
Set childRS2 = rs!Assigned_To.Value
'Attempt5
' Exit the loop if the multivalued field contains no records. 'Attempt5
Do Until childRS1.EOF 'Attempt5
childRS1.MoveFirst 'Attempt5
'Attempt5
' Loop through the records in the child recordset. 'Attempt5
Do Until childRS1.EOF 'Attempt5
' Print the owner(s) of the task to the Immediate 'Attempt5
' window. 'Attempt5
Debug.Print Chr(0), childRS1!Value.Value 'Attempt5
childRS1.MoveNext 'Attempt5
Loop 'Attempt5
Loop 'End of loop that checks if Tags multi-value field is NULL
' Exit the loop if the multivalued field contains no records. 'Attempt5
Do Until childRS2.EOF 'Attempt5
childRS2.MoveFirst 'Attempt5
' Loop through the records in the child recordset. 'Attempt5
Do Until childRS2.EOF 'Attempt5
' Print the owner(s) of the task to the Immediate 'Attempt5
' window. 'Attempt5
Debug.Print Chr(0), childRS2!Value.Value 'Attempt5
childRS2.MoveNext 'Attempt5
Loop 'Attempt5
Loop 'End of loop that checks if Assigned_To multi-value field is NULL
rs2.AddNew 'Attempt5
rs2!Task_ID = rs!ID.Value 'Attempt5
rs2!Task_Title = rs!Task_Title 'Attempt5
rs2!Tags = childRS1!Value.Value 'Attempt5
rs2!Priority = rs!Priority 'Attempt5
rs2!Assigned_To = childRS2!Value.Value 'Attempt5
rs2!Blockage_Reason = rs!Blockage_Reason 'Attempt5
rs2!Requirements = rs!Requirements 'Attempt5
rs2!Notes = rs!Notes 'Attempt5
rs2.Update 'Attempt5
Below is Attempt 7, referring to the Answer on 4/13 (edited again 4/23)
Me.Requery 'Refresh the table before running the query
Dim strSQL1 As String 'Declaring the Non-Multivalued fields to move to Corrections Log
strSQL1 = "INSERT INTO tbl_Task_List_Corrections (Task_ID, Task_Title, " & _
"Task_Start_Time, Task_End_Time, Break_Length_Minutes, Priority, " & _
"Blockage_Reason, Requirements, Notes, Task_Complete) " & _
"SELECT ID, Task_Title, Task_Start_Time, Task_End_Time, " & _
"Break_Length_Minutes, Priority, Blockage_Reason, Requirements, " & _
"Notes, Task_Complete FROM tbl_Task_List WHERE ID IN " & _
"(SELECT MAX(ID) FROM tbl_Task_List);"
DoCmd.RunSQL strSQL1
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT ID FROM tbl_Task_List") 'error 3061
Set rs = CurrentDb.OpenRecordset("SELECT MAX(ID) FROM tbl_Task_List") '3265
Do Until rs.EOF
CurrentDb.Execute "INSERT INTO " & _
" tbl_Task_List_Corrections(Tags.Value) SELECT Tags.Value FROM " & _
" (SELECT Tags.Value FROM tbl_Task_List WHERE ID = " & rs!ID & ") " & _
" AS T1 WHERE tbl_Task_List_Corrections.Task_ID = " & rs!ID
rs.MoveNext
Loop
MsgBox "You have successfully added this Task"
'DoCmd.SetWarnings False 'Turning the "You are about to Update 1 Row" warning back on
DoCmd.Close
End Sub
Turned out to be much simpler than I thought. Looks like original attempt was on right track.
Once new record with non-MVF data is created, run another INSERT SELECT action to populate the MVF. Example shows populating second table MVF in a loop of copied records. Tested and worked. Should be simple to execute the SQL action for a single record specified by record identifier on form instead of looping recordset.
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT RateID FROM CopyOfRates")
Do Until rs.EOF
CurrentDb.Execute "INSERT INTO CopyOfRates(MVTest.Value) SELECT MVTest.Value FROM " & _
"(SELECT MVTest.Value FROM Rates WHERE RateID = " & rs!RateID & ") AS Q1 " & _
"WHERE CopyOfRates.RateID = " & rs!RateID
rs.MoveNext
Loop
这篇关于Access 2016 VBA 无法设置按钮单击表单以将多值字段写入第二个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
