Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Feb 2010
    Location
    Essex, UK
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question Concatenating multiple records into a single text box

    I have been modifying the Access 2007 downloadable "incident database" for use as a logging tool. Within this, each incident has a "Comment" field, which is a Memo box set to "Append After", and the details form then uses the ColumnHistory() function to show the history of the comments added. This works just fine.

    Now, I have had feedback from users. They want a space between each piece of history, to make it more readable, and they would like to see the most recent history item first. In the context in which they are using it, this makes more sense. However, I have satisfied myself that neither of these are possible with the ColumnHistory() function.

    The better way to do this is to therefore to create a table with comments and to write a query to return all of the comments related to a particular issue, sorted with the most recent first. This is quite straightforward.

    What doesn't appear to be straightforward, though, is displaying this in the form. I would rather put the data into a text box than into a datasheet, simply because the comments will be of variable width and it will be more readable that way. But I cannot see how to get multiple records to concatenate into a single text box. How should I do this?

    Thanks.

    Stuart

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Lounger
    Join Date
    Feb 2010
    Location
    Essex, UK
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I may have thought of a solution, since this box only needs to be filled once on Form Open. Would this work:

    In Form Open event:

    Set a new recordset to be equal to the results from the query
    For each record in recordset
    Add comment to a string, with suitable padding
    Next
    Set textbox value to string

    My only worry would be whether I would hit a character limit for the text box? I think that you are only allowed 255 characters, aren't you?

  4. #3
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    10,178
    Thanks
    129
    Thanked 1,139 Times in 1,050 Posts
    The text box itself has no size limit. If you choose Memo for the field that will hold the data, you will be able to store up to 65,536 characters.

  5. #4
    Lounger
    Join Date
    Feb 2011
    Posts
    43
    Thanks
    0
    Thanked 6 Times in 6 Posts
    And don't use padding. Use vbCrLf to start the next comment on a new line. Or two of them to have a separator line.

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
  •