如何使用 Access VBA 更新多值字段 [英] How do I use Access VBA to update a multivalue field
问题描述
我正在编写一个脚本，该脚本从另一个软件产品中获取打印输出，解析数据，然后导入训练事件.这是我正在处理的数据示例:
I'm writing a script that takes printouts from another software product, parses the data and then imports the training event. Here's a sample bit of data that I'm working with:
RANDOMLY GENERATED TEST DATA – PRIVACY ACT NOT APPLICABLE
TRAINING EVENT INQUIRY AS OF DATE 08 MAR 18
COURSE NARRATIVE DUR TYPE MIL START START STOP STOP
CODE INT IND DATE TIME DATE TIME
000555 MANDATORY FUN TRAINING 008 A 08MAR18 0800 08MAR18 1600
BUILDING NO. ROOM NO. EVENT ID MIN SIZE MAX SIZE #SCHED REPEAT IND
578 CONF 0550001 005 012 012 N
EMP NR NAME GRD W-C W-C EVT LABOR STAT
00001 WONG CLIFTON L 005 BCTK 001
00002 BROCK CANDICE K 006 AICN 001
00003 GIBBS AMOS L 004 CODA 009
00004 BROOKS JAN A 004 CODA 009
00005 ROBINSON BERNADETTE M 004 AXAM 008
00006 WILKERSON MICHELE D 005 BDCK 007
00007 HARMON ERVIN E 004 BAVI 006
00008 ALEXANDER TOMMIE S 006 AIXB 005
00009 FRANK MALCOLM T 005 AIEE 004
00010 ABBOTT HECTOR O 002 AIEN 003
00011 HENDERSON KIMBERLY S 004 AXWT 002
00012 BENNETT MELISSA P 006 DMGR 001
B1Q 18067 16:34:14 PROCESSED. VERSION DATE: 041817
这里的目标是，单击一下，解析数据，提取所有相关信息，找出属于我的员工，然后将事件数据转储到可用于生成通知和其他报告.
The goal here is to, with a single click, parse the data, extract all of the pertinent information, figure out which employees belong to me, then dump the event data into a table that can then be used to generate notifications and other reports.
到目前为止我所拥有的将成功读取上述数据，提取所有相关的班级信息(何时和何地)以及属于我的所有员工(在这种情况下，每个人的 WC 都以一种").然后将 EMP NR 列和员工工作中心 W-C 列中的员工编号转储到 tblImport 中.我遇到的问题是下面的一小段代码，它应该在多值字段AssignedPersonnel"中标记所有员工，该字段包含我拥有的所有员工的行源.
What I've got so far will successfully read that above data, pull out all of the relevant class information (when & where) and all employees that belong to me (in this case everyone with a WC that starts with "A"). It then dumps the employee number from the EMP NR column and the employee workcenter W-C column into tblImport. What I'm running into issues with is the bit of code below, where it's supposed to be flagging all employees in the multivalue field "AssignedPersonnel", which has a row source of all employees that I own.
Private Sub problem_code()
Dim i, empnv As Integer
Dim cdb As DAO.Database
Dim imt As DAO.Recordset
Dim rst As DAO.Recordset2
Dim asp As DAO.Field2
Set cdb = CurrentDb
Set rst = cdb.OpenRecordset("tblEvents", dbOpenTable)
Set asp = rst("AssignedPersonnel")
Set imt = cdb.OpenRecordset("tblImport", dbOpenTable)
rst.MoveFirst
rst.AddNew
imt.MoveFirst
Do While Not imt.EOF
empnv = imt!EmpID
asp(empnv) = True
imt.MoveNext
Loop
rst.Update
End Sub
我知道我可以手动更新该字段并使用 condition = true 查询它以拉出分配给该培训的人员，但是尝试让 VBA 代码写入该字段让我完全离开难倒.最终发生的是，当我尝试设置 asp(empnv) = True 时，我收到错误 3265，此集合中未找到该项目.我在这里缺少什么?或者，更深层次的问题是我是否应该重新构建整个数据库以尽量避免完全使用 MVF?
I know I can manually update that field and query it using condition = true to pull who is assigned to that training, but trying to get the VBA code to write to that field is leaving me completely stumped. What ends up happening is that I get error 3265, item not found in this collection, when I try to set asp(empnv) = True. What am I missing here? Or, a deeper question would be should I just restructure my entire database to try to avoid using a MVF altogether?
减少发布的代码量以最小化示例.
cut down how much code was posted to minimize the example.
推荐答案
我知道这已经一年多了，你可能已经找到了你的解决方案，但对于那些来到这里寻找类似问题的潜在解决方案的人来说，根据您提供的信息，我有一种解决方案应该有效.
I know this is over a year old and you may have already found your solution, but for those who came here looking for a potential solution to a similar problem, I have one solution that should work based on the information you provided.
在我发布代码之前，我们需要了解的第一件事是，在 VBA 中处理多值字段时，我们需要将其视为子记录集，表记录是其父记录.这意味着它需要在我们导航到适当的记录后声明.在你的情况下:
Before I post the code the first thing we need to understand is that when dealing with a Multi Value Field in VBA we need to treat it as a Child Recordset with the Table Record being its Parent. This means it needs to be declared after we navigate to the appropriate record. In your case:
Dim rst As DAO.Recordset2
Dim asp As DAO.Recordset
...
rst.AddNew
Set asp = rst.AssignedPersonnel.Value
...
第二，一个常见的误解是，由于在表中我们将记录源分配给多值字段，因此我们只需要像手动操作那样切换应用到该字段的记录.遗憾的是，情况并非如此，因为记录源只是一个数据验证工具，并不会固有地用数据填充多值字段.在您的情况下，当您创建新记录时，此字段为空.
Second, a common misconception is that since in the Table we assign a Record Source to the Multi Value Field we just need to toggle what records apply to the field as we would do manually. Sadly, this is not the case as the record source is only a Data Validation Tool and does not inherently populate the Multi Value Field with data. In your case, as you are creating a new record, this field is empty.
第三，我们要明白，一个被设置为Multi Value Field值的Recordset只是一个Single Field Recordset，据我所知，它没有提供字段名，必须被引用字段 ID 编号为 0.
Third, we need to understand that a Recordset that is set to the value of a Multi Value Field is only a Single Field Recordset which, as far as I know, is not provided with a field name and must be referenced by the Field ID number which is 0.
最后，当向多值字段记录集添加数据时，我们需要将其作为记录集进行.
Finally, when adding data to the Multi Value Field Recordset, we need to do so as a Recordset.
所以不用多说，这里有一个可能对我有用的解决方案:
So without further ado, here is a possible solution which works for me:
Public Sub Solution()
Dim cdb As DAO.Database ' Database
Dim imt As DAO.Recordset ' Import Table
Dim rst As DAO.Recordset2 ' Events Table
Dim asp As DAO.Recordset ' Multi Value Field
Set cdb = CurrentDb
Set rst = cdb.OpenRecordset("tblEvents", dbOpenTable)
Set imt = cdb.OpenRecordset("tblImport", dbOpenTable)
rst.AddNew 'Add new record to Events Table
Set asp = rst!AssignedPersonnel.Value ' Set asp to the AssignedPersonnel Field Value for the new Events Table Record.
Do While Not imt.EOF ' Cycle through the Import Table Records
asp.AddNew ' Add a new record to the asp Recordset
asp.Fields(0) = imt!EmpID
asp.Update ' Commit the changes to the asp Recordset
imt.MoveNext
Loop
rst.Update ' Commit the changes to the Events Table Recordset
End Sub
这篇关于如何使用 Access VBA 更新多值字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
