none
How To Import Into A Table with Multi-Value Fields RRS feed

  • Question

  • Hello:

    I have a table with a multi-value field that contains states in which a company does business.  It is multi-value because there can be more than one state.  I tried to import a text tab-delimited file in which the data was arranged as follows:

    Field1 Tab Field 2 Tab OR, WA, CA Tab

    The "State field contained the multiple entries separated by a comma (like they appear in a query of the multi-value field), but it won't accept it.  Does anyone know how to import into a multi-value field?

    Thanks,

    Rich Locus, Logicwurks, LLC

     

    Thursday, June 17, 2010 5:15 PM

Answers

  • Hello:

    After posting this question, I found out you CAN'T import into a multi-value field.  So, what I did was import the multi-value data into a new, different, text only field.  Then I had to parse it and use VBA to insert the multiple values one at a time into the recordset2 multi-value field.

    Rich Locus, Logicwurks, LLC

    • Marked as answer by RichLocus Thursday, June 17, 2010 6:33 PM
    Thursday, June 17, 2010 6:33 PM

All replies

  • Hello:

    After posting this question, I found out you CAN'T import into a multi-value field.  So, what I did was import the multi-value data into a new, different, text only field.  Then I had to parse it and use VBA to insert the multiple values one at a time into the recordset2 multi-value field.

    Rich Locus, Logicwurks, LLC

    • Marked as answer by RichLocus Thursday, June 17, 2010 6:33 PM
    Thursday, June 17, 2010 6:33 PM
  • Hi RichLocus,

    I am facing the exact problem you managed to over-come.

    Appreciate if you can post the code on how you manage to handle this.

    Thanks

    Wednesday, June 23, 2010 4:04 AM
  • I'd be very strongly inclined to do this without using Microsoft's non-standard, non-normalized multivalue field feature; it's not needed!
    You have a perfectly bog standard many (companies) to many (states) relationship. If you have a resolver table with CompanyID and State, you can add multiple records to this table: 314,WA; 314,OR; 314,ID; 315,ME; 315, NH; 315,VT and so on. And you can import into such a structure with no code at all, and display it on a form with a subform, a multiselect listbox, or whatever you like. In fact, the multivalue field has just such a structure, under the covers where you can't get at it. Why not just do it yourself, and avoid the hassle?

    John W. Vinson/MVP
    Wednesday, June 23, 2010 5:20 AM
  • I have the exact same problem, but I don't know much about VB to do that code you mention. Could you share it with us please?

    thanks,

     

    Joana

    Saturday, January 7, 2012 6:24 PM
  • Joana:

    I had this issue about 19 months ago  I will dig through my archives and attempt to locate the code.

    Regards,


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com


    • Edited by RichLocus Tuesday, January 10, 2012 11:05 PM
    Tuesday, January 10, 2012 9:23 PM
  • Joana:

    Here's the code I used to populate a multi-value field from a parsed field.  The parsing routine could be greatly improved by using the Split function, but at that time, I had not used it yet.   FYI... the field name of the multi-value field in the table was "DBAInStatesMultiValue", which you can see in the example below how it is integrated into the code.

     

    Option Compare Database
    Option Explicit
    Option Base 1
    Dim strInputString As String
    Dim intNumberOfArrayEntries As Integer
    Dim strStateArray(6) As String
    
    ' ***********************************************************************
    ' Loop Through A Table With A Multi-Value Field
    ' And Insert Values Into the Multi-Value Field From A
    ' Parsed Regular Text Field
    ' ***********************************************************************
    Public Function InsertIntoMultiValueField()
    
    Dim db As DAO.Database
    
    ' ***********************************************************************
    ' Main Recordset Contains a Multi-Value Field
    ' ***********************************************************************
    Dim rsBusiness As DAO.Recordset2
    ' ***********************************************************************
    ' Now Define the Multi-Value Fields as a RecordSet
    ' ***********************************************************************
    Dim rsDBAInStatesMultiValue As DAO.Recordset2
    
    ' ***********************************************************************
    ' The Values of the Field Are Contained in a Field Object
    ' ***********************************************************************
    Dim fldDBAInStatesMultiValue As DAO.Field2
    Dim i As Integer
    
    ' ***********************************************************************
    ' Open the Parent File
    ' ***********************************************************************
    Set db = CurrentDb()
    Set rsBusiness = db.OpenRecordset("tblBusiness")
    
    ' ***********************************************************************
    ' Set The Multi-Value Field
    ' ***********************************************************************
    Set fldDBAInStatesMultiValue = rsBusiness("DBAInStatesMultiValue")
    
    ' ***********************************************************************
    ' Check to Make Sure it is Multi-Value
    ' ***********************************************************************
    If Not (fldDBAInStatesMultiValue.IsComplex) Then
        MsgBox ("Not A Multi-Value Field")
        rsBusiness.Close
        Set rsBusiness = Nothing
        Set fldDBAInStatesMultiValue = Nothing
        Exit Function
    End If
    
    On Error Resume Next
    ' ***********************************************************************
    ' Loop Through
    ' ***********************************************************************
    Do While Not rsBusiness.EOF
    
    ' ***********************************************************************
    ' Parse Regular Text Field into Array For Insertion into Multi-Value
    ' ***********************************************************************
        strInputString = rsBusiness!DBAInStatesText
        Call ParseInputString
        
    ' ***********************************************************************
    ' If Entries Are Present, Add Them To The Multi-Value Field
    ' ***********************************************************************
        If intNumberOfArrayEntries > 0 Then
            Set rsDBAInStatesMultiValue = fldDBAInStatesMultiValue.Value
            rsBusiness.Edit
            For i = 1 To intNumberOfArrayEntries
                rsDBAInStatesMultiValue.AddNew
                rsDBAInStatesMultiValue("Value") = strStateArray(i)
                rsDBAInStatesMultiValue.Update
            Next i
            rsDBAInStatesMultiValue.Close
            rsBusiness.Update
        End If
    rsBusiness.MoveNext
    Loop
    
    On Error GoTo 0
    
    rsBusiness.Close
    Set rsBusiness = Nothing
    Set rsDBAInStatesMultiValue = Nothing
    End Function
    
    Public Function ParseInputString()
    Dim intLength As Integer
    Dim intStartSearch As Integer
    Dim intNextComma As Integer
    Dim intStartOfItem As Integer
    Dim intLengthOfItem As Integer
    Dim strComma As String
    
    strComma = ","
    intNumberOfArrayEntries = 0
    strInputString = Trim(strInputString)
    intLength = Len(strInputString)
    
    ' ***********************************************************************
    ' Skip Zero Length Strings
    ' ***********************************************************************
    If intLength = 0 Then
        Exit Function
    End If
    
    ' ***********************************************************************
    ' Strip Any Leading Comma
    ' ***********************************************************************
    If Mid(strInputString, 1, 1) = "," Then
        Mid(strInputString, 1, 1) = " "
        strInputString = Trim(strInputString)
        intLength = Len(strInputString)
        If intLength = 0 Then
            Exit Function
        End If
    End If
    
    ' ***********************************************************************
    ' Strip Any Trailing Comma
    ' ***********************************************************************
    If Mid(strInputString, intLength, 1) = "," Then
        Mid(strInputString, intLength, 1) = " "
        strInputString = Trim(strInputString)
        intLength = Len(strInputString)
        If intLength = 0 Then
            Exit Function
        End If
    End If
    
    intStartSearch = 1
    ' ***********************************************************************
    ' Loop Through And Parse All the Items
    ' ***********************************************************************
    Do
        intNextComma = InStr(intStartSearch, strInputString, strComma)
        If intNextComma <> 0 Then
            intNumberOfArrayEntries = intNumberOfArrayEntries + 1
            intStartOfItem = intStartSearch
            intLengthOfItem = intNextComma - intStartOfItem
            strStateArray(intNumberOfArrayEntries) = Trim(Mid(strInputString, intStartOfItem, intLengthOfItem))
            intStartSearch = intNextComma + 1
        Else
            intNumberOfArrayEntries = intNumberOfArrayEntries + 1
            intStartOfItem = intStartSearch
            intLengthOfItem = intLength - intStartSearch + 1
            strStateArray(intNumberOfArrayEntries) = Trim(Mid(strInputString, intStartOfItem, intLengthOfItem))
        End If
        
    Loop Until intNextComma = 0
        
    End Function
    
    
    Regards,

     


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com


    • Edited by RichLocus Tuesday, January 10, 2012 11:07 PM
    • Proposed as answer by Jing2013 Tuesday, August 20, 2013 7:55 PM
    • Unproposed as answer by Jing2013 Tuesday, August 20, 2013 7:55 PM
    • Proposed as answer by ToastieLive Thursday, March 22, 2018 6:09 PM
    Tuesday, January 10, 2012 9:32 PM
  • Hello, everyone,

    I know I'm reviving a pretty dead thread, but I wanted to say thank you so much to RichLocus. I was having the exact same problem as above and your code entirely helped me out, Thank you! I had been looking for a solution to these pesky multi-value field importing issues forever, and always knew there must be a way!

    However, I was a little bit lost when trying to decipher exactly what the code was doing, so I added a bunch of comments and renamed things to make it more generic so that hopefully, people coming by in the future read this and find it easy to implement

    Option Compare Database
    Option Explicit
    Option Base 1
    Dim strInputString As String
    Dim intNumberOfArrayEntries As Integer
    Dim strStateArray(6) As String
    
    ' ***********************************************************************
    ' Loop Through A Table With A Multi-Value Field
    ' And Insert Values Into the Multi-Value Field From A
    ' Parsed Regular Text Field
    '
    ' Original code acquired from:
    ' http://social.msdn.microsoft.com/Forums/office/en-US/384f46cd-2a67-4efa-883e-83004700e144/how-to-import-into-a-table-with-multivalue-fields?forum=accessdev
    '
    ' This code will take data from a string field, parse it into an array
    ' for the multi-valued field, and append it to any existing data in the
    ' multi-valued field of the same record.
    '
    ' This code uses a single recordset to process data, therefore, if you
    ' wish to use this code, both your string field and your multi-value
    ' field must be in the same table. (Perhaps import a temporary field?)
    '
    ' If you wish to incorporate this into your database, note the following
    ' changes you should make to the code prior to using it:
    '   TABLENAME is the table you will be using and holds both your string
    '       which will be converted and your multi-valued field and the
    '       multi-valued field
    '       There are 13 instances of TABLENAME in this code (+ 2 for here/above)
    '   MULTIVALUEFIELD is the field which holds your multi-valued field and
    '       after using this code, the items converted to the multi-value
    '       type will be appended to the items, if any, that are currently
    '       stored in the given record.
    '       There are 13 instances of MULTIVALUEFIELD in this code (+ 2 for here/above)
    '   STRINGFIELD is the field which holds a string of the combined,
    '       comma-separated values. This field will be broken up into
    '       individual entries for use in the multi-value field
    '       There is 1 instances of STRINGFIELD in this code (+ 2 for here/above)
    '   If you use "Find and Replace function" replace each of the above names
    '   exactly with your appropriate database names and watch for the count
    '   of changes that it matches the "# of instances" for each
    '
    ' ***********************************************************************
    
    
    ' Call this function name to start action
    Public Function InsertIntoMultiField()
    
    Dim db As DAO.Database
    
    ' ***********************************************************************
    ' Main recordset containing both a multi-value field and string field
    ' ***********************************************************************
    Dim TABLENAME As DAO.Recordset2
    ' ***********************************************************************
    ' Now Define the Multi-Value Fields as a RecordSet
    ' ***********************************************************************
    Dim rsMULTIVALUEFIELD As DAO.Recordset2
    
    ' ***********************************************************************
    ' The Values of the Field Are Contained in a Field Object
    ' Dim fldMULTIVALUEFIELDTemp As DAO.Field2
    ' ***********************************************************************
    Dim fldMULTIVALUEFIELD As DAO.Field2
    Dim i As Integer
    
    ' ***********************************************************************
    ' Open the Parent File
    ' ***********************************************************************
    Set db = CurrentDb()
    Set TABLENAME = db.OpenRecordset("TABLENAME")
    
    ' ***********************************************************************
    ' Set The Multi-Value Field
    ' ***********************************************************************
    Set fldMULTIVALUEFIELD = TABLENAME("Applications")
    
    ' ***********************************************************************
    ' Check to Make Sure it is Multi-Value
    ' ***********************************************************************
    If Not (fldMULTIVALUEFIELD.IsComplex) Then
        MsgBox ("Not A Multi-Value Field")
        TABLENAME.Close
        Set TABLENAME = Nothing
        Set fldMULTIVALUEFIELD = Nothing
        Exit Function
    Else
        'MsgBox ("Selected field IS a multi-value field")
    End If
    
    On Error Resume Next
    ' ***********************************************************************
    ' Loop Through
    ' ***********************************************************************
    Do While Not TABLENAME.EOF
    
    ' ***********************************************************************
    ' Parse Regular Text Field into Array For Insertion into Multi-Value
    ' ***********************************************************************
        strInputString = TABLENAME!STRINGFIELD
        Call ParseInputString
        
    ' ***********************************************************************
    ' If Entries Are Present, Add Them To The Multi-Value Field
    ' ***********************************************************************
        If intNumberOfArrayEntries > 0 Then
            Set rsMULTIVALUEFIELD = fldMULTIVALUEFIELD.Value
            TABLENAME.Edit
            For i = 1 To intNumberOfArrayEntries
                rsMULTIVALUEFIELD.AddNew
                rsMULTIVALUEFIELD("Value") = strStateArray(i)
                rsMULTIVALUEFIELD.Update
            Next i
            rsMULTIVALUEFIELD.Close
            TABLENAME.Update
        End If
    TABLENAME.MoveNext
    Loop
    
    On Error GoTo 0
    
    TABLENAME.Close
    Set TABLENAME = Nothing
    Set rsMULTIVALUEFIELD = Nothing
    End Function
    
    Public Function ParseInputString()
    Dim intLength As Integer
    Dim intStartSearch As Integer
    Dim intNextComma As Integer
    Dim intStartOfItem As Integer
    Dim intLengthOfItem As Integer
    Dim strComma As String
    
    strComma = ","
    intNumberOfArrayEntries = 0
    strInputString = Trim(strInputString)
    intLength = Len(strInputString)
    
    ' ***********************************************************************
    ' Skip Zero Length Strings
    ' ***********************************************************************
    If intLength = 0 Then
        Exit Function
    End If
    
    ' ***********************************************************************
    ' Strip Any Leading Comma
    ' ***********************************************************************
    If Mid(strInputString, 1, 1) = "," Then
        Mid(strInputString, 1, 1) = " "
        strInputString = Trim(strInputString)
        intLength = Len(strInputString)
        If intLength = 0 Then
            Exit Function
        End If
    End If
    
    ' ***********************************************************************
    ' Strip Any Trailing Comma
    ' ***********************************************************************
    If Mid(strInputString, intLength, 1) = "," Then
        Mid(strInputString, intLength, 1) = " "
        strInputString = Trim(strInputString)
        intLength = Len(strInputString)
        If intLength = 0 Then
            Exit Function
        End If
    End If
    
    intStartSearch = 1
    ' ***********************************************************************
    ' Loop Through And Parse All the Items
    ' ***********************************************************************
    Do
        intNextComma = InStr(intStartSearch, strInputString, strComma)
        If intNextComma <> 0 Then
            intNumberOfArrayEntries = intNumberOfArrayEntries + 1
            intStartOfItem = intStartSearch
            intLengthOfItem = intNextComma - intStartOfItem
            strStateArray(intNumberOfArrayEntries) = Trim(Mid(strInputString, intStartOfItem, intLengthOfItem))
            intStartSearch = intNextComma + 1
        Else
            intNumberOfArrayEntries = intNumberOfArrayEntries + 1
            intStartOfItem = intStartSearch
            intLengthOfItem = intLength - intStartSearch + 1
            strStateArray(intNumberOfArrayEntries) = Trim(Mid(strInputString, intStartOfItem, intLengthOfItem))
        End If
        
    Loop Until intNextComma = 0
        
    End Function
    
    
    ' ***********************************************************************
    ' Original code credit to: Rich Locus, Logicwurks, LLC
    ' From: http://social.msdn.microsoft.com/Forums/office/en-US/384f46cd-2a67-4efa-883e-83004700e144/how-to-import-into-a-table-with-multivalue-fields?forum=accessdev
    ' Accessed: 23 September 2014
    ' Edited by: ghost0245 (user - social.msdn.microsoft.com)
    ' ***********************************************************************
    
    

    Wednesday, September 24, 2014 6:07 PM
  • thank you for your comments on the post ghost0245.

    I copied the code exactly as you have. I am getting an error on Ln 76 when running:

    Set fldMULTIVALUEFIELD = TABLENAME("Applications")

    Run-time error '3265':  Item not found in this collection.  Any advice on this resolution?


    • Edited by toya0324 Thursday, November 13, 2014 9:10 PM
    Thursday, November 13, 2014 9:10 PM
  • thank you for your comments on the post ghost0245.

    I copied the code exactly as you have. I am getting an error on Ln 76 when running:

    Set fldMULTIVALUEFIELD = TABLENAME("Applications")

    Run-time error '3265':  Item not found in this collection.  Any advice on this resolution?


    Make sure the Multivalue fieldname is correct, in above code it was named "Applications". How is it being named in your DB?


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Please vote an answer helpful if they helped. Please mark an answer(s) as an answer when your question is being answered.

    Thursday, November 13, 2014 9:19 PM
  • I wish I could vote 500 times... this code is amazing, thankyou ghost0245 and Rich!
    Friday, November 14, 2014 2:49 PM
  • silly question, (really new to access) but where specifically do I place this code to use it?
    Wednesday, April 22, 2015 5:22 PM
  • raceandco, I’m new to access Visual Basic as well and I was wondering the same thing. In Access 2013 I got it to work this way:

    In Access, go to Database tools -> Visual Basic. This will open a seperate window called Microsoft visual basic for applications.

    In the window called Project - Database1 (or something like that) right-click and choose insert -> module. (In my case there already was a project, I’m not sure if this is always the case)

    Open the new module and just copy the code in there. Don’t create a new function to put it in.

    Replace the placeholders with your table and field names.

    To run the code, first make sure to put the mouse cursor somewhere in the InsertIntoMultiField() code block. If you put it in the ParseInputString() code block, you’ll run only a part of the code and you’ll wonder why nothing happens (as I have done).

    Finally, press the run button in the task bar (it looks like a play button), or F5 and that’s it.

    Thursday, August 20, 2015 4:35 PM
  • To be clear, in the line

    Set fldMULTIVALUEFIELD = TABLENAME("Applications")

    you have to replace Applications by the name of the multi-value field.

    If your data uses a semicolon instead of a comma, change these lines:

    strComma = "," to strComma = ";"

    If Mid(strInputString, 1, 1) = "," Then to If Mid(strInputString, 1, 1) = strComma Then

    If Mid(strInputString, intLength, 1) = "," Then to If Mid(strInputString, intLength, 1) = strComma Then

    Many thanks to RichLocus and Mrkhiel! This is a really useful solution for something that should be a standard feature in Access.


    Thursday, August 20, 2015 4:49 PM
  • Rich and MrKhiel, thank you so much for your work. True time saver!
    Thursday, October 8, 2015 6:35 PM
  • I created an account to thank you ALL for this thread! I was working in a Sharepoint List with limited access rights (only Export to EXCEL was available) and I needed to do a porting of some EXCEL data. After serveral tests I was ready to connect my list using MS Access 2013: create items/update items/delete items. I tried several times to do a bulk upload using the EXCEL sheet in the access table linked to my Sharepoint List. ONLY the multiple values were empty. I tried several solutions even SQL statements, none of them allow an update of multiple value record (except if you use .value property, but only to 1 value is allow (the default) and you cannot add more values to the column)...

    I did the inimaginable workarounds (even trying using PowerShell but libraries must be downloaded and permission must be given to be able to connect the Sharepoint List) ... so I understood that the manipulation should be programmatical instead of SQL statement, i.e, by VBA macro... NO clue on how to do it using 'cursors' until I reached to this thread. COPY and PASTE + little modifications... and this work like magic!!

    I confirm that this solution is also applicable to Sharepoint List linked in MS Access 2013.

    Something to consider when running the VBA macro that is in the thread: there is an statemente: On Error Resume Next, it doesn't let you see if an error happened... which is a little blocking if you want to understand the error (like a reference error or constraint on the table), but just uncommenting it, it will display an error message with the description :) :)

    Tuesday, January 10, 2017 12:46 PM
  • Leandr, thanks. I don't know enough to comment on the code, but I am glad that this is working. I too am interested in a XL to Access to SP linkage with multi select columns. Could you explain how you used this code to make this connection work please? I have a SharePoint list connected to an Access database and some of the columns are multi select ones, Did you create the dummy string fields to hold the multi select values in sharepoint? Can you help with the mechanisms please. Thanks in advance

    Sunday, February 5, 2017 4:42 PM
  • Hello,

    I am using the code written in this thread, but for some reason it only works for the 5 or 6 first items of the entry where it should select lots more.

    I tried to find a solution, but I am not very familiar with VBA code so I cannot find the solution myself.

    Can somebody support me ?

    Thanks in advance,

    Mélanie

    Tuesday, May 2, 2017 8:15 AM
  • I finally found the solution myself.

    The issue was with the array lenght.

    Thanks very much for this code, it saves so much time !

    Mélanie

    Tuesday, May 9, 2017 3:02 PM
  • This entire thread is great, and I'm super appreciative to everyone who's contributed -- made my life easier mostly!

    However, I have a follow-on question:

    Just as everyone else, I imported a bunch of data from a spreadsheet to make a database out of some old data. However, the project I'm working on necessitates continued edits/additions to the newly-created database, and I'm wondering if there's some way to use a derivative of this solution to create an honest-to-goodness relationship between a main table that lists the the records I care about, and a secondary table that contains the possible values for this specific field (as opposed to using a multi-value field)? Or, is an MVF with self-contained values the way to do this?

    Apologies if the question is unclear, still learning Access. 

    Monday, May 29, 2017 6:43 PM

  • ................and I'm wondering if there's some way to use a derivative of this solution to create an honest-to-goodness relationship between a main table that lists the the records I care about, and a secondary table that contains the possible values for this specific field (as opposed to using a multi-value field)?

    To recast non-normalized data derived from a source such as a spreadsheet into a set of correctly related tables, in which a many-to-many relationship type is modelled in the time-honoured way by a table which resolves it into two or more one-to-many relationship types, rather than representing the many-to-many relationship type by a multi-values field, requires the execution of a series of INSERT INTO statements (i.e. 'append' queries) in a specific order.  The rule of thumb is that rows are first inserted in to the referenced table in a relationship (i.e. that on the 'one' side) before inserting rows into the referencing tables (i.e. those on the 'many' side).  To do otherwise would of course violate referential integrity.

    In the case of a binary (two-way) many-to-many relationship type this would mean inserting rows into two referenced tables, and then into the referencing table which models the many-to-many relationship type between them.  An example would be where rows are inserted into Orders and Products tables first, and then into an OrderDetails table which references the two tables on OrderID and ProductID respectively.

    You might like to take a look at DecomposerDemo.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    In this little demo file data is imported from a simple Excel worksheet into a non-normalized 'holding' table in Access, and from that it is decomposed into a set of normalized related tables which are related in one-to-many or many-to-many relationship types.  The demo takes you through the process step by step, with a brief explanation of the 'append' query being executed at each stage.


    Ken Sheridan, Stafford, England

    Monday, May 29, 2017 8:44 PM
  • Hi! You code helped me a lot!!
    I've modified it to work with a temporary table and to work with more then one multivalued field.

    Sub PopulateMultiValue()

        Dim db As DAO.Database
        Dim srcTbl As String: srcTbl = "ExtraLoad"  ' Source table name
        Dim srcRS As DAO.Recordset2
        Dim srcQry As String
        Dim srcQdf As DAO.QueryDef
        Dim csv() As String
        Dim csvString As String
        Dim csvSize As Integer
       
        Dim tgtTbl As String: tgtTbl = "inventory2" ' Target table name
        Dim tgtRS As DAO.Recordset2
       
        Dim idFld As String: idFld = "[Unique ID]"  ' Field to join the src with tgt table
        Dim id As String
        Dim flds() As String: flds = Split("[Used in Site],[Supplier Name],[Integrator Name],[Hosting Partner Name]", ",")
        Dim fld As Variant
            
        Dim mvfld As DAO.Field2                     ' Multi-value record set
        Dim mvrs As DAO.Recordset2                  ' Multi-value field
       
        ' Open source and target
       
        Debug.Print "Start "; Format(Now(), "yyyy-MM-dd hh:mm:ss")
        Set db = CurrentDb()
        Set srcRS = db.OpenRecordset(srcTbl)
        Set tgtRS = db.OpenRecordset(tgtTbl)
          
        For Each fld In flds
           
            Debug.Print "**********Begin with field: "; fld
            Set mvfld = tgtRS(fld)
          
                ' Loop through target table
                tgtRS.MoveFirst
                Do Until tgtRS.EOF
               
                   Debug.Print "tgt unique id: "; tgtRS(idFld)
                   Set mvrs = mvfld.Value
                   tgtRS.Edit
                  
                   id = tgtRS(idFld) ' Current ID in the target
                   ' Create source record set using temporary query defintion (name = "")
                   srcQry = "SELECT " & srcTbl & "." & fld & " FROM " & srcTbl & " LEFT JOIN " & tgtTbl & " ON " & srcTbl & "." & idFld & "=" & tgtTbl & "." & idFld & " WHERE " & srcTbl & "." & idFld & " = '" & id & "';"
                   Set srcQdf = db.CreateQueryDef("", srcQry)
                   Set srcRS = srcQdf.OpenRecordset
                  
                   If srcRS.EOF Then
                      Debug.Print " > no value in source"
                           
                   Else
                      'csvString = CStr(srcRS(fld).Value)
                      Debug.Print " > src value:"; csvString
                                       
                      If srcRS(fld).Value <> "" Then
                      'If Len(csvString) > 0 Then
                        
                         'Parse csv into array
                         csv = Split(srcRS(fld).Value, ",")
                         csvSize = (UBound(csv) - LBound(csv))
                  
                         'If Entries Are Present, Add Them To The Multi-Value Field
                         If csvSize > 0 Then
                            For i = 0 To csvSize
                               Debug.Print "  > Adding: "; csv(i)
                               mvrs.AddNew
                               mvrs("Value") = csv(i)
                               mvrs.Update
                            Next i
                         End If
                      End If
                   End If
                       
                   mvrs.Close
                   tgtRS.Update
                  
                tgtRS.MoveNext
                Loop
        Next
       
        ' Cleanup
        srcRS.Close
        tgtRS.Close
        db.Close
        Debug.Print "done"
    End Sub

    Thursday, March 22, 2018 6:09 PM
  • Thank you very much for the valuable code & also for its source.

    I want to mention that if the MVF contains more than 6 values change "6" to a higher value on the following line:

    Dim strStateArray(6) As String
    Tuesday, November 13, 2018 8:07 AM
  • After running this I noticed that it "ADDS" the MVF to the current field, it does not apply the "TRUE" selection value to the current values already in my list.

    Can you make the current MVF show current selections "TRUE" instead of adding them to the bottom of list? My MVF list is linked to an existing table. No duplicates are added to my table, just the MVF list.

    Thanks

    Tuesday, December 10, 2019 9:21 PM
  • Suggest you read my article MVFs and why you really shouldn't use them
    Thursday, December 12, 2019 9:44 AM
  • Since we're all sharing our code here's my disgusting version of this.

    When I was running it I had a new copy of all the fields that had the word _free appended to it. I need that text moved to the column next to them.

    I know I have so many disgusting database practices in here so no need to comment haha.

    These are a few of the columns of my data as examples

    Client_Phone_Type (this is a single select lookup)

    Null

    Null

    ______________

    Client_Phone_Type_Free

    1

    1

    ______________________

    Client_Transportation (this is a multi-select lookup)

    Null

    Null

    ______________

    Client_Transportation_Free

    1;2

    3


    Option Compare Database
    Option Explicit
    Private Sub ChangeFreeTextToMultiField()
        
      On Error GoTo Err_ChangeFreeTextToMultiField
        
    	'Declare all strings
        Dim strFreeField As String
        Dim strFree As String
        Dim strMulti As String
        Dim strFreeValue As String
        Dim strSQL As String
    	
    	'This is to make it easier to print but you don't need this
    	Dim strPrint As String
    	Dim strCurValue2 As String
    	
    	'Declare all variants(arrays)
        Dim varValues As Variant
    	
       'Declare All Integers for number stuff
        Dim intLength As Integer
        Dim intArrayLen As Integer
        Dim x As Integer
        Dim i As Integer
        Dim intClientId As Integer
    	
    	'Declare all that fancy SHTUFF
        Dim rs As DAO.Recordset2
    	Dim fldUpdateField As DAO.Field2
    	 Dim fldCurField2 As DAO.Field2
        Dim rsDBACurrentMultiValue As DAO.Recordset2
        
    	'Why not make sure i = 0?
        i = 0
    	
    	'I guess this is a thing people do, let's open that recordset
        Set rs = CurrentDb.OpenRecordset("Clients")
    
    	'We gonna use that whole damn recordset
        Do Until rs.EOF
    	
            'Now that we're in the record set we need to go through every field 1 by 1, I know its the worst. Don't forget to do Count - 1 because you know base 0 and whatnot
            For i = 0 To rs.Fields.Count - 1
    			
    			'You gotta set your field variable to the current field. If you don't know how rs.Fields works just google but it's a pain
                Set fldCurField2 = rs.Fields(i)
    			
    			'Is this field a multivalue field? Because if so it's going to BREAK EVERYTHING so let's just ignore it and go to our next field
                If fldCurField2.IsComplex Then
                    GoTo Nexti
                End If
    			
    			'Is this field null? Because if so we clearly don't care about the value in it so let's just move to the next field!
                If IsNull(rs.Fields(i).Value) Then
                    GoTo Nexti
                End If
    			
    			'Gotta be clear what the value and field names are
                strCurValue2 = rs.Fields(i).Value
                strFreeField = rs.Fields(i).Name
    
    			'When we are on a new record we want to make sure we capture their client ID (my primary key) so that we can reference it in some SQL shit later. Could I have just used rs.Fields(1)? Probably
                If InStr(1, strFreeField, "Client_ID", vbTextCompare) Then
                    intClientId = rs.Fields(i).Value
                End If
                
    			'Now is where my stupidity really kicks in I decided to do this by the name of the field so we gotta make sure we are in a "free text field" that needs to be moved to it's lookup (maybe multivalue) field
                If InStr(1, rs.Fields(i).Name, "free", vbTextCompare) <> 0 Then
                    'Why did I set this again you ask? And to a different variable!? Because I'm lazy af!
    				strFree = rs.Fields(i).Name
    				'Again my stroke of brilliance had me just remove the phrase _free and now it's the name of my lookup field that I will henceforth call a multivalue field even though thats a lie sometimes.
                    strMulti = Left(strFree, InStr(1, strFree, "_free", vbTextCompare) - 1)
                    
    				'Let's trim it down just in case
                    strCurValue2 = Trim(strCurValue2)
                    intLength = Len(strCurValue2)
                    If intLength = 0 Then
                        GoTo Nexti
                    End If
                    
                    'Now let's make sure to change any leading or trailing ;
    				If Mid(strCurValue2, 1, 1) = ";" Then
                        Mid(strCurValue2, 1, 1) = " "
                        strCurValue2 = Trim(strCurValue2)
                        intLength = Len(strCurValue2)
                        If intLength = 0 Then
                           GoTo Nexti
                        End If
                    End If
                    If Mid(strCurValue2, intLength, 1) = ";" Then
                        Mid(strCurValue2, intLength, 1) = " "
                        strCurValue2 = Trim(strCurValue2)
                        intLength = Len(strCurValue2)
                        If intLength = 0 Then
                            GoTo Nexti
                        End If
                    End If
    				
    				'My data is even grosser than my Database setup and VBA code, so we gotta make sure it starts with a number and doesn't contain someones life story instead of the freaking phone number type number thing we are looking for 
                    If Not strCurValue2 Like "#*" Then
                        GoTo Nexti
                    End If
    					
    					'Why do I set this you ask? IDK! Debug.Print Samuel L. Jackson saying "IDK Mother F-er"
                        strFreeValue = strCurValue2
                        
    					'Now is where it gets extra dumb, some of my fields are single values, some are multiple, so I gotta figure out which one we are dealing with then create an array on my delimiter which is ; you might have a , 
                        If InStr(1, strCurValue2, ";", vbTextCompare) <> 0 Then
                            varValues = Split(strFreeValue, ";")
    					
    					'If there is no delimiter that means we are dealing with 1 value so just make me an array of size 1 or 0 because computers man....
                        ElseIf InStr(1, strCurValue2, ";", vbTextCompare) = 0 Then
                            varValues = Split(strFreeValue)
                        End If
    					
    					'If you want to see some debugging stuff you can uncomment these 2 lines
                        'strPrint = varValues(0)
                        'Debug.Print strPrint
    					
    					'Gotta measure my array, size matters!
                        intArrayLen = UBound(varValues) - LBound(varValues) + 1
    
    					'set this field variable thing to my field that has the list
                        Set fldUpdateField = rs.Fields(strMulti)
                        
    					'Now we see if this is a multi select or just a single select lookup field
                        If Not fldUpdateField.IsComplex Then
    						'Ok cool so its a single select lookup then just use this dope SQL query to put the value in
                            strSQL = "Update Clients Set Clients." & strMulti & " = " & strFreeValue & " Where Clients.Client_ID = " & intClientId
                            'Debug.Print strSQL
                            DoCmd.SetWarnings False
                            DoCmd.RunSQL strSQL
                            DoCmd.SetWarnings True
    						'I also wanted to delete the other field value just cause
                            strSQL = "Update Clients Set Clients." & strFree & " = NULL Where Clients.Client_ID = " & intClientId
                            'Debug.Print strSQL
                            DoCmd.SetWarnings False
                            DoCmd.RunSQL strSQL
                            DoCmd.SetWarnings True
                            GoTo Nexti
                            
                        End If
    					
    					'Make sure there are still numbers in our array This is like the 4th time i have checked it though, but why not check again!?
                        If intArrayLen > 0 Then
                            
                            'Finally we can set the recordset variable to the field value, I struggled with this forever because I was previously trying to set my single choice lookup to this and it kept failing                        
                            Set rsDBACurrentMultiValue = fldUpdateField.Value
                            rs.Edit
                            For x = 0 To intArrayLen - 1
                                
                                rsDBACurrentMultiValue.AddNew
                                rsDBACurrentMultiValue("Value") = varValues(x)
                                rsDBACurrentMultiValue.Update
                                
                            Next x
                            rsDBACurrentMultiValue.Close
                            rs.Update
                        End If
                        
    					'Delete the old stuff
                        strSQL = "Update Clients Set Clients." & strFree & " = NULL Where Clients.Client_ID = " & intClientId
                        Debug.Print strSQL
                        DoCmd.SetWarnings False
                        DoCmd.RunSQL strSQL
                        DoCmd.SetWarnings True
                        GoTo Nexti
    
    
                End If
    'Why not make this a go to section?
    Nexti:
             Next i
            
           
        rs.MoveNext
        Loop
        
    Exit_ChangeFreeTextToMultiField:
        Exit Sub
    Err_ChangeFreeTextToMultiField:
        MsgBox Err.Number & " " & Err.Description
        Resume Exit_ChangeFreeTextToMultiField
    End Sub


    Friday, January 3, 2020 7:18 PM