Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Feb 2010
    Location
    Essex, UK
    Posts
    44
    Thanks
    4
    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. #2
    Lounger
    Join Date
    Feb 2010
    Location
    Essex, UK
    Posts
    44
    Thanks
    4
    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?

  3. #3
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    10,363
    Thanks
    130
    Thanked 1,163 Times in 1,070 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.

  4. #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
  •