Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    PrincessApril is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    42

    Export Multiselected Table Data to Word Bookmark

    Hi all,



    I am exporting Access table data to bookmarked locations in a Word document, and, after setting my recordset as a table, the approach below works fine for simple string data:


    Code:
    m_objDoc.Bookmarks("SomeWordBookmark").Select
    m_objWord.Selection.Text = rs!CorrespondingAccessTableData
    However, I get a type-mismatch error when trying to export from cells whose data were derived from multiselect listboxes, such as, for instance, racial background (say the user selects two races out of five, and the data are displayed in comma-separated format in the table).

    Before getting into specifics, my first question is about the general approach. I have a large form with about 10 subforms, and, ideally, the user could click one button at the end to export all of this data into the bookmarked locations in the Word doc. I was thinking I would be setting my recordset as a query of all the underlying tables, but given the problem with the multiselect items (and maybe even uniselect listboxes), I wonder if I should be exporting directly from the form. I'm obviously a noob, so maybe that doesn't even make sense, but the examples I have seen over the past couple hours on how to handle the multiselect issue all seem to refer to forms rather than setting a table recordset, so that's what I'm asking about.

    Any advice on general direction to get me started? I'm happy to do my own research and fiddle with it for a while before posting a more direct question, so I'm certainly not asking for anyone to generate a bunch of code, but I'd like that fiddling to be a bit more productive than I have been tonight, if that makes sense.

    Also for context, whatever approach I choose, ultimately, I only want to export the data for the current record, not all records (not sure if that changes the recommended approach in general).

    Thank you in advance for any ideas you might have, and have a nice day or night, depending on where you all are!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Sounds like you have a multi-value field in table. This means data is not comma-separated elements in spite of the appearance. A multi-value field actually holds links to records in a hidden dependent table. This is why you cannot Word merge this field. If it really were a comma-separated string there would not be an issue.

    Why Word merge? Is there really that much boiler plate text?
    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
    PrincessApril is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    42
    Thanks for your response June. I have created all of my tables and lookup tables for starters, and I have built the first page of my form (I'll probably need about 10 tabs to cover everything). The paper version of this is a 20-page form for a client intake in a social-services setting. We use it for intakes for new clients and for reassessing their case every 6 months.

    Not only do we not have an electronic database for the intakes/reassessments yet (yes, we write these on paper), we also have to manually type a free-flowing text version of the 20-page paper form to paste into a separate database where we keep our client notes.

    As an example, one of the first sections of the form covers client demographics. We fill out the paper form and stick it in the client chart. Then we have to begin typing the massive electronic note. Some example sentences would be "Ct's name is Homer Simpson, DOB is 01/01/1971, SSN is... Case Manager met with Ct on 02/10/2020, and the date of Ct's previous reassessment was. Ct is a heterosexual but questioning cisgender male whose HIV risk factors include intravenous drug use and a recent blood transfusion (and there are a bunch of list boxes right in that sentence). blah blah blah..." There are then sections for medical, dental, housing, food insecurity, interpersonal violence, etc. etc. etc. I wanted to make one big table in the beginning but quickly saw that I would be way past the 255 limit, so I started making one table per section. The lookup tables are I think what you are referring to when you say hidden tables, so I understand how the data are used to create the list boxes, but I didn't realize that the comma-separated presentation in the records table was just a visual.

    Someone in our office created a Word document with fields to facilitate typing up the massive note, which is okay, but I want to have electronic records of all my intakes/reassessments, and I want to be able to generate the massive note with a click (of course some editing will be needed, but I'm going to try to minimize that through the vba). And I would only be generating one massive note at a time, which would be based on one 20-page report, which is from one interaction with one Ct on one day (all of that means --> one record).

    So I'm wondering if I would set up some query to pull all of the data from my forms (which would perhaps share a common unique id, such as client initials and assessment date), and set that query as my record source. That would be fine for exporting the string data, but I'm still left with the issue of exporting data drawn from all these listboxes (both single-select and multiselect). I've been reading about a lot of things tonight, all of which are new to me (recordset clones, itemsSelected property, etc.). I guess I'm just looking for someone to point me in a good direction from the beginning. Then I can do some work and see how far I can get on my own until I have more specific questions.

    For now--my main question is how to best export list-box data to Word bookmarks for a single record at a time? I was hoping there would be something simple like CStr to convert the listbox-generated data to a string and simply export that, but I didn't get far with that.

    Thank you again for your time and assistance!

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    As June says, multi select boxes use a hidden table. What you see is

    ID...Name...MSField
    1....A.........X,Y

    what you actually have is

    ID...Name...MSField
    1....A.........X
    1....A.........Y

    To see your data this way instead of your query being

    SELECT, ID, Name, MSField
    FROM myTable

    you need to use

    SELECT, ID, Name, MSField.Value
    FROM myTable

    So you can get to the value - but you will have duplicates for ID and Name

    without knowing how your code is actually working, you will need to work from that situation.

    one option might be to use vba code to loop through your MSField to create your string - suggest take a look at the concatrelated function by allen browne http://allenbrowne.com/func-concat.html which handles multivalue fields

    or perhaps since your string might need to be split anyway, just substitute the looping code for the splitting code

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Lookup tables are not hidden tables. A lookup table is source for choices in combobox or listbox. Multi-value hidden tables are where your selected values are stored and those tables are not directly viewable. I never use MVF. Most experienced developers here advise to avoid and instead build normal dependent tables and form/subform for data entry.
    Really need a thorough understanding of MVF and how to deal with.
    https://support.office.com/en-us/art...c-6de9bebbec31
    https://support.office.com/en-us/art...3-b6624e1e323a
    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.

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    There is some confusion in parts of this thread

    At the risk of repetition,
    1. Lookup tables are NOT the same as lookup fields in a table
    Lookup tables are a vital part of Access development
    Lookup fields cause major issues and should be avoided - see The Evils of Lookup Fields in Tables

    2. Multiselect list boxes and multiselect combo boxes are very different things and work in totally different ways

    a) multiselect listboxes are based on separate records in a table/query (or in a value list)
    There is absolutely nothing wrong with using them

    b) multiselect combo boxes are based on multivalue fields and the details are stored in deep hidden system tables which cannot be accessed directly.
    Using MVFs will cause lots of issues and should be avoided - see Multivalued fields ... and why you shouldn't use them

    Hope that helps. Apologies for repeating any points made previously
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    PrincessApril is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    42
    Wow, thank you all for your detailed responses (and particularly for clarifying the point about the hidden stored values).

    Ajax, that function looks super useful. I'm excited to look through it in detail when I get home later today.

    And IslaDogs, as chance would have it, I am using multiselect listboxes rather than combo boxes, so it looks like that will avoid the issues you mentioned.

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    That's good to hear .
    As I was explaining in my reply, there are no hidden stored values associated with multiselect listboxes
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Comboboxes and listboxes act differently unless listbox is bound to multi-value field. Does listbox have checkboxes? I tested and that is what I observed.

    Is field "Allow Multiple Values" property set to Yes in table? If so, then field is multi-value regardless of type of control used for data entry. Issues mentioned will not be avoided.

    Since you said data in field is 'comma-separated' after selection from a multi-select listbox, along with Word merge fail, I concluded associated field is multi-value.


    If field is not multi-value, the only way data could be comma-separated after selection from multi-select listbox is with VBA building a CSV string - and Word merge should not fail because this is just simple text.
    Last edited by June7; 02-09-2020 at 02:11 PM.
    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.

  10. #10
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I stand corrected ...

    A multivalued combo can be changed to a multiselect listbox in which case it does indeed show checkboxes as for the combo
    Click image for larger version. 

Name:	MVF1.PNG 
Views:	12 
Size:	28.7 KB 
ID:	40849

    To my mind its now just a combo which is permanently dropped down

    Or a listbox can be created based on a multivalued field in which case it looks like this
    Click image for larger version. 

Name:	MVF2.PNG 
Views:	12 
Size:	8.7 KB 
ID:	40850

    In either case, trying to code changes to this would I think be fairly difficult.
    In both cases, best avoided IMHO

    However, as previously stated, a standard multiselect listbox is not based on MVFs and has no linked deep hidden system tables
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  11. #11
    PrincessApril is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    42
    Hi all,

    Yes, my listboxes have the "allow multiple values" property selected. Judging from the discussion, I would rather reconsider how I am handling the fields than try to navigate the coding issues.

    So, considering my end goal of wanting to export the data to Word bookmarks, what approach would be best for fields that do need to allow the user to select multiple values? I suppose I could simply make a separate single field for every possible response. So instead of a Race listbox I could just have dichotomous yes/no fields for Race: African American, Race: Asian, etc. Or is there a more expedient way?

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If you want to avoid VBA then I expect de-normalization (a yes/no field for each race) may be most expedient.

    It is a balancing act between normalization and ease of data entry/output. "Normalize until it hurts, denormalize until it works."

    I am working for U.S. Census 2020 for which racial/ethnic data is collected. I wonder how their program handles this.
    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
    PrincessApril is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    42
    I actually like playing with the code. I don't have a background in it, but I search for resources and then try to modify to fit what I'm doing and learn along the way. I just meant trying to avoid the overly complicated situations of handling the multiselect listboxes as mentioned in the thread.

    So what would the general approach be, using VBA, to handle, for instance, an item on racial background, where multiple responses are possible? I'd like to investigate that first.

    (Also, the Census is an interesting discussion--their racial/ethnic classifications have limited empirical efforts for decades, and my wife never knows what to put. We were just told by a local Census presenter that sex is still binary, despite intersex being so common. I believe they have a gender item but also probably binary--we were told transgender and other categories are coming earliest in 2030. Large data sets often follow Census classifications, so it's a shame they still haven't been able to better capture the increasingly diverse US public yet, because that ripples into academia and professional work, which ultimately informs policy.).

  14. #14
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I see no reason to use MVFs for this purpose.
    Use a lookup table tblRace and a multiselect listbox.
    Anyone who chooses more than one ethnicity value will have a record for each.
    Easy to understand and code.

    Similarly for gender. You can display multiple checkboxes if you want, but only where a checkbox is ticked will a record be added
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  15. #15
    PrincessApril is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    42
    So that brings me back to the original question of getting that data into the Word file. I'm going to play with that Allen Browne function a bit to see what I can do with it.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. How to export access data to word
    By Wolphe in forum Programming
    Replies: 19
    Last Post: 02-02-2018, 10:58 AM
  2. Export Data from Access to Word
    By Byrkster in forum Access
    Replies: 4
    Last Post: 07-22-2016, 08:00 AM
  3. Export to Word Table
    By gstylianou in forum Access
    Replies: 39
    Last Post: 11-03-2014, 11:22 AM
  4. Replies: 0
    Last Post: 02-12-2013, 11:39 AM
  5. Replies: 7
    Last Post: 02-01-2013, 02:58 PM

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