Results 1 to 8 of 8

Import/Export table with field using multiselect combo box

  1. #1
    celiowin is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Jul 2011
    Posts
    6

    Import/Export table with field using multiselect combo box

    Hi,
    I have an .accdb file and 1 of the table has a field that uses multi-select combo box. When i export to excel it looks like "1;2;3;4" in a cell but when i import the worksheet back into Access it returns an error. Is there a way to import those data back into the combox field?

    Field data type: Text
    Lookup:
    Display Control: Combo Box
    Row Source Type: Table\Query

  2. #2
    June7's Avatar
    June7 is offline Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    26,805
    No (at least not without code). Multi-value fields are problematic. I refuse to use. Review:
    http://office.microsoft.com/en-us/ac...001233722.aspx
    http://social.msdn.microsoft.com/For...e-83004700e144
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  3. #3
    celiowin is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jul 2011
    Posts
    6
    I've been searching through the rest of the forum and came across a lot of your other replies regarding multi-value fields. I've also tried to use VBA to loop through each row in the table and loop through the items inside the multi-value field to deselect and select different values but i keep getting a a single text value output in watch even it displays in the table multi-values have been selected.

  4. #4
    June7's Avatar
    June7 is offline Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    26,805
    Sorry, don't understand your post. Is there still a question to resolve?
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  5. #5
    celiowin is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jul 2011
    Posts
    6
    Quote Originally Posted by June7 View Post
    Sorry, don't understand your post. Is there still a question to resolve?
    I've tried the VBA approach in changing the selection in the multi-value field but have been unsuccessful.

    My question is
    Is it possible to loop through the items in the multi-value field and change selection?

  6. #6
    June7's Avatar
    June7 is offline Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    26,805
    Review this article (I got to it through the second url in my earlier post) http://office.microsoft.com/en-us/ac...A001233722#BM8
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  7. #7
    celiowin is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jul 2011
    Posts
    6
    I've read the article and i'm executing SQL through VBA code but i'm having a problem with using INSERT.

    The column is a multi-value field and it is a text field. Its been going well with string values of 1 or 1.01

    strValue = "1.01.1.1"
    strSQL = "INSERT INTO [TABLE NAME] ([COLUMN NAME].[VALUE]) VALUES (" & strValue & ") WHERE ID = 1"

    returns a dbFailOnError = 128

  8. #8
    June7's Avatar
    June7 is offline Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    26,805
    Strings need apostrophe delimiters, without delimiters the SQL will expect a number, which is why 1 and 1.01 values were accepted.

    strSQL = "INSERT INTO [TABLE NAME] ([COLUMN NAME].[VALUE]) VALUES ('" & strValue & "') WHERE ID = 1"


    BTW, date values need # delimiter.
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

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

Similar Threads

  1. Replies: 11
    Last Post: 03-23-2012, 06:30 AM
  2. export/import by email
    By sue tang in forum Import/Export Data
    Replies: 5
    Last Post: 03-14-2012, 03:41 PM
  3. Replies: 1
    Last Post: 09-12-2011, 09:15 AM
  4. how to export data field to field in a table
    By farhanahmed in forum Database Design
    Replies: 1
    Last Post: 04-05-2011, 09:04 AM
  5. Export / Import situation
    By svcghost in forum Import/Export Data
    Replies: 7
    Last Post: 10-14-2010, 03:23 PM

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