Results 1 to 7 of 7
  1. #1
    gopherking is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2011
    Posts
    71

    How to update Multi Value Field via VBA

    I am currently working on a form to generate a Child Record from an existing record on a Sharepoint List Table in Access 2007. There are a handful of Multi Value Fields that need to be copied from the Parent to the Child record. I am using VBA to pre-load the form with the Parent information for the majority of the fields that should be the same, but I am encountering errors when the Multi Value Fields are attempted.



    I am able to use a Field Recordset against the Parent record to list out the different values stored in the Parent Multi Value Field, but now I need to know if there is a way to apply these values to the Multi Value Field on the Form? NOTE: this form is not submitted or saved, so referencing the record to update it is not an option since a user may need to update fields prior to the initial save.

    PS - I am now finding out how difficult the Multi Value Fields are to use in Access and VBA, but I must use this as the lists are from Sharepoint and I don't have any other options.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Check this out http://www.tek-tips.com/viewthread.cfm?qid=1654446

    Also, this link has procedure that will read multi-value field entries and concatenate to a string. http://allenbrowne.com/ser-42.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    gopherking is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2011
    Posts
    71
    Thanks for the Reply June, however both of those posts I had already found and they didn't help me except in reading the values that I am trying to copy. The post from tek-tips is a great post, but unfortunately doesn't work for me since I am trying to get the values from an existing record and place them in a form without saving it. It would help if I was simply copying the one record to another and then having the user update the new record as needed, but I am trying to allow them to make changes to the record before it is saved since the Child records will differ from the Parent in some form or another.

    On a side note, I did also try to setup one of the Multi-Value fields in my form as a recordset to import the values to, but that didn't work (I didn't think it would since it isn't an actual table/query, but trying to think outside of the box on this one because you never know what may work!!!)

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Never used Sharepoint and never used multi-value but as I understand them, can't have a multi-select combobox unless it is bound and that means need a record in table. Sounds like would have to allow record creation and then cancel/delete if not wanted.

    I wrote a procedure to create new record and it runs without triggering error but new record is not saved. I followed the code from http://www.access-programmers.co.uk/...d.php?t=204915
    Code:
    Public Sub copyFromMV()
      Dim rsOld As DAO.Recordset
      Dim rsMVold As DAO.Recordset
      Dim rsMVnew As DAO.Recordset
      Dim rsNew As DAO.Recordset
      Set rsOld = CurrentDb.OpenRecordset("SELECT MV FROM Table1 WHERE ID = 1;")
      Set rsNew = CurrentDb.OpenRecordset("Table2")
     
      Do While Not rsOld.EOF
        'the value property returns another recordset
        Set rsMVold = rsOld.Fields("MV").Value
        rsNew.AddNew
        Set rsMVnew = rsNew.Fields("MV").Value
        Do While Not rsMVold.EOF
            rsMVnew.AddNew
            rsMVnew.Fields(0) = rsMVold.Fields(0)
            rsMVnew.Update
            rsMVold.MoveNext
        Loop
        rsNew.Update
        rsOld.MoveNext
      Loop
    End Sub
    A multi-select listbox might be an alternative to combobox. Can set the items selected in an unbound listbox. Still leaves issue of how to populate the multi-value field with selected items.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    gopherking is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2011
    Posts
    71
    Thanks for the link and your time on this. Fortunately for me, I was able to dodge a bullet on this one and simply provide a snapshot of the Parent record Multi-Value Fields on the form and force the users to update the Child record Multi-Value Fields as they see fit. Since we are using Access and Sharepoint as a Request Tracking tool, the Parent to Child records will differ slightly and the Multi-Value fields will most likely not be the same, so my users understood the complexity of this and let me get by with this simple solution. I will keep in mind your code above and the link you provided incase I need to revisit this issue in the future though!

    As for working with Sharepoint, it can be a very powerful tool especially when you link it to an Access database for greater reporting / tracking of items. Multi-Value Fields are also great in that you don't need to do any of the backend lookups and they are easily setup in Sharepoint. The downfall is that if you need to link up an Access database to a Sharepoint site and you are expecting to run queries or copy records, be ready for some headaches. I found it the hard way as this has been my first Sharepoint/Access experience and had to develop workflows in Sharepoint to copy the Multi-Value Field entries to Multi-Line Text Boxes in the Sharepoint Lists for each of Querying, but that's another story for another time!

    Thanks again!

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Still wish I could get the code to work. If someone said it worked for them, should be repeatable.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Revisited this issue. Modified code slightly. This worked:
    Code:
    Public Sub copyFromMV()
    Dim rsOld As DAO.Recordset
    Dim rsMVold As DAO.Recordset
    Dim rsMVnew As DAO.Recordset
    Dim rsNew As DAO.Recordset
    Set rsOld = CurrentDb.OpenRecordset("SELECT * FROM Table1;")
    Set rsNew = CurrentDb.OpenRecordset("Table2")
    Do While Not rsOld.EOF
        rsNew.AddNew
        rsNew!ID = rsOld!ID
        'the value property returns another recordset
        Set rsMVold = rsOld.Fields("Test").Value
        Set rsMVnew = rsNew.Fields("Test").Value
        Do While Not rsMVold.EOF
            rsMVnew.AddNew
            rsMVnew.Fields(0) = rsMVold.Fields(0)
            rsMVnew.Update
            rsMVold.MoveNext
        Loop
        rsNew.Update
        rsOld.MoveNext
    Loop
    End Sub
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 11-24-2014, 02:11 PM
  2. Replies: 1
    Last Post: 12-16-2010, 10:32 AM
  3. Multi Update By Checkbox
    By Nsquared in forum Forms
    Replies: 3
    Last Post: 02-10-2010, 03:08 PM
  4. Multi value field - Combobox help
    By stougch in forum Forms
    Replies: 0
    Last Post: 10-19-2009, 10:47 PM
  5. Multi-select listbox and update flag in table
    By Suresh in forum Programming
    Replies: 0
    Last Post: 12-19-2007, 01:04 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums