Results 1 to 13 of 13

Access VBA: Copy all records with a multivalue fields from one table to another table

  1. #1
    Gopineesh is offline Novice
    Windows 10 Access 2007
    Join Date
    May 2019
    Posts
    6

    Access VBA: Copy all records with a multivalue fields from one table to another table

    Hi Team,



    Can someone help me, how to copy all records including a multivalue fields from Customer table to Employee table in Access VBA

    Example:
    Table1 Name: Employee
    Table2 Name: Customer

    Employee Table - Emp_ID Number(Primary Key), Emp_Name Text, Emp_Desg Text
    Customer Table - Cust_ID Number(Primary Key), Cust_Name Text, Cust_Desg Lookup wizard

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,436
    You want to copy multi-value field to another multi-value field? This is a fairly common topic.
    https://www.tek-tips.com/viewthread.cfm?qid=1742052
    http://www.utteraccess.com/forum/lof.../t1880531.html
    https://stackoverflow.com/questions/...60537#55660537
    https://stackoverflow.com/questions/...38748#52938748

    Why would you copy customer data to employee table?
    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
    Gopineesh is offline Novice
    Windows 10 Access 2007
    Join Date
    May 2019
    Posts
    6
    You want to copy multi-value field to another multi-value field?
    No, we want to copy multi-value field from one table (Customer) to another table (Employee) which does'nt have multi-value field (Both tables has recordset).

    Why would you copy customer data to employee table?
    Employee Table - Linked Table
    Customer Table - Normal table which has same recordsets as in Employee table but the column Cust_Desg as Multi-value field

    Our intention is to change Emp_Desg column in Employee table from datatype Text as multi-value field. Since the Emp_Desg column already had recorsets, if we manually change the datatype in Design View from Text to Lookup wizard the Emp_Desg column becomes empty. So on copying Cust_Desg column as multivalue field in Customer table to Emp_Desg column as multi-value field in Employee table will change the design for Emp_Desg column with datatype as Text to multi-value field in Employee table. Is this possible?

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,436
    Sorry, still confused. In one statement you say Employee table does not have multi-value field then you say you want to change Emp_Desg column in Employee table from multi-value.

    Still don't understand why Customer data would be saved into Employee.

    Perhaps you need to show sample tables.
    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
    Gopineesh is offline Novice
    Windows 10 Access 2007
    Join Date
    May 2019
    Posts
    6
    Leave the above context. See, we have one table with column Fruits and datatype as Short Text


    We have a defined set list of values that they will populate in the column Fruits. To avoid human errors during the input process, we would like this field to be a selection drop down, if possible. The dropdown selection options should show the list of 15 values below and the user should be able to select more than 1 value. Multiple values would be listed separated by semicolon ;


    Value1
    Value2
    ...
    Value15


    Hope this make sense

  6. #6
    Gopineesh is offline Novice
    Windows 10 Access 2007
    Join Date
    May 2019
    Posts
    6
    Also that table is a linked table

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,436
    I NEVER use multi-value fields.

    If you insist, then set up multi-value properties of field in table design.

    But I still do not understand the original question.
    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.

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    5,870
    In a query, you can get every record associated with the values in a multi value field by adding the .Value property in the query:
    https://support.office.com/en-us/art...3-b6624e1e323a

    You should be able to use that query as a table in an append query to append each record to some other table. Why you'd want to append to a table that by your description is not related to the other table is a mystery.
    To avoid human errors during the input process, we would like this field to be a selection drop down,
    This reads like you're working in tables, which you should not be doing. Stop doing that and the multi value problem goes away.
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    Sometimes I talk to myself - like when I need expert advice.

  9. #9
    Gopineesh is offline Novice
    Windows 10 Access 2007
    Join Date
    May 2019
    Posts
    6
    Since the existing table is a Linked Table without multi-value field, we can't change the Fruits column datatype from ShortText to Lookup wizard in Design View.

    Tried using query to change the datatype from ShortText to lookup/multi-value, getting error message which is attached in the message.
    -> ALTER TABLE Employee ALTER COLUMN Fruits LOOKUP

    Is there any way using macro to change the datatype of Fruits column from shorttext to lookup/multivalue for a linked table?
    Attached Thumbnails Attached Thumbnails Error.PNG  

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,436
    Would have to set a connection to the backend and modify the table, probably using TableDefs not SQL. Open the backend and do the modification directly.
    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.

  11. #11
    Gopineesh is offline Novice
    Windows 10 Access 2007
    Join Date
    May 2019
    Posts
    6
    Ok. We dont have access for backend, using macro can we update?
    What is TableDefs?

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,436
    A macro can run UPDATE action SQL.

    I will let you do research on TableDefs and QueryDefs.
    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.

  13. #13
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,247
    Quote Originally Posted by Gopineesh View Post
    We dont have access for backend, ....
    If you are not using Access as the BE, what engine is the BE? SQL Server? MySQL? Oracle? DB2?

    As far as I know, only Access allows the use of multi-value fields in tables......
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

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

Similar Threads

  1. Replies: 3
    Last Post: 04-18-2018, 02:49 PM
  2. Converting Multivalue Fields to Records
    By rjgriffin46 in forum Access
    Replies: 12
    Last Post: 05-02-2016, 12:01 PM
  3. Getting table records to copy to another table
    By sarahc25 in forum Programming
    Replies: 11
    Last Post: 01-02-2016, 03:33 PM
  4. Copy fields to another table.
    By projectpupil7 in forum Access
    Replies: 11
    Last Post: 11-13-2014, 12:50 PM
  5. Replies: 2
    Last Post: 04-02-2010, 07:42 AM

Posting Permissions

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