Results 1 to 3 of 3
  1. #1
    wubbit is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2012
    Posts
    24

    Copy values from table1 to table2

    I have a multivalue field (I know they suck, but it is the only way I know how to make life easy for the client) which I need to copy values from one table to another multivalue field in a different table. Basically I am trying to setup a "template" of field choices for the record to be updated with.
    I have tried the following code (it is the only way to update a multivalue field I hear):

    Private Sub templ_but_Click() 'this is the button that executes the following

    Set rstFrom = dbs.OpenRecordset("SELECT [Templates].* FROM [Templates] " & _
    "WHERE ((([Templates].ID)= '" & Forms!Proposals!template & "'))" & _
    "ORDER BY [Templates].[ID]", dbOpenSnapshot) ' this is the table1 multivalue field data that I want to put into table2

    Set rstTo = dbs.OpenRecordset("Proposals", dbOpenDynaset) ' this is the table2 multivalue field table name

    rstFrom.MoveFirst


    Do Until rstFrom.EOF

    rstTo.AddNew
    For iFor = 1 To rstFrom.Fields.Count - 1 ' use 1 not 0 as has ID field is Autonumber

    If rstFrom.Fields(iFor).Type > 100 Then ' field type 100 or higher are mulitivalue fields

    If rstTo.Fields(iFor).Name = "macros" Then ' the name of the muiltivalue field in table1 and table2 is macros with values stored in macros.value
    rstTo.Fields(iFor).Value = rstFrom.Fields(iFor).Value ' copy the values from table1 to table2 here
    End If

    Else
    Set rstMVFrom = rstFrom.Fields(iFor).Value

    If rstMVFrom.RecordCount > 0 Then
    Set rstMVTo = rstTo.Fields(iFor).Value

    Do While rstMVFrom.EOF = False
    rstMVTo.AddNew
    rstMVTo.Fields(0).Value = rstMVFrom.Fields(0).Value
    rstMVTo.Update
    rstMVFrom.MoveNext
    Loop
    rstMVFrom.Close
    rstMVTo.Close
    End If ' rstFrom

    End If ' iFor
    Next iFor

    rstTo.Update

    rstFrom.MoveNext
    Loop ' rstFrom

    rst2.Close
    Set rst2 = Nothing
    End Sub


    But it just gives me errors. I am sure this problem is a bit hard and maybe something that many think is unneeded, but I have no other way to make these "template" recordsets that will make life easy for the client. I have tried an update query, but with the difficulty of using the multivalue fields it has proven beyond my skill-set.

    I may just rewrite this whole database in MySQL and PHP... it is what I know best.

    Any info on how to do this easier, or even a push in the right direction for some other way of accomplishing the goal of generating "templates" for recordsets would be greatly appreciated.

    Thanks in advance!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,356
    You do understand that an attachment field is really a link to an underlying hidden table that holds the values as multiple records? Because of this, can't use an UPDATE query or code that just copies the individual values to another table multi-value field. Review http://office.microsoft.com/en-us/ac...A001233722#BM7

    You have the right idea about opening and looping the MV recordset, just not the correct action. The above code gives an example using an Access query object with known parameters. I am not sure this can be translated to VBA with variables.
    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
    wubbit is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2012
    Posts
    24
    I think I will give the append query a shot, I moved the templates into the same table as the records being used. It might help, I will keep you posted.

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

Similar Threads

  1. Replies: 4
    Last Post: 08-04-2011, 02:57 PM
  2. Replies: 5
    Last Post: 08-20-2010, 06:40 AM
  3. Replies: 3
    Last Post: 04-04-2010, 05:26 PM
  4. Replies: 1
    Last Post: 03-27-2010, 06:13 AM
  5. Use Table1 to update Table2? Urg Help needed
    By munkifisht in forum Queries
    Replies: 1
    Last Post: 07-24-2009, 08:00 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