How do I use Access VBA to update a multivalue field

Question

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.

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

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?

EDIT: cut down how much code was posted to minimize the example.

Answer 1

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.

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.

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