Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    hammer32 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2018
    Posts
    13

    Question Using Buttons to Generate Text for a (Long) Text Box - Hitting Character Count Limitations

    Hello,



    I'm completely new to MS Access. I'm trying to use Access to generate text reports in a standardized format.

    I have one table with a text field formatted as Long Text.

    I have a form that displays the text field. That form also has buttons that the user presses to generate text that gets added to the end of any existing text (if there is any) already in the text field.

    For instance, the user is conducting a fire inspection on a building. One button will enter the preamble to the report:

    Code:
    Private Sub b_Conclude_Click()
    Text_ROI.SetFocus
    Me!Text_ROI.SelStart = Me!Text_ROI.SelLength + 1
    Text_ROI.Text = Text_ROI + Chr(13) + Chr(10) + Chr(13) + Chr(10) + "FIRE INSPECION 20XX-XXX" + Chr(13) + Chr(10) + Chr(13) + Chr(10) + "A fire inspection was conducted on facility XXX of zone XXX."
    End Sub
    The next button adds information about the sprinkler system (and the user doesn't press that button if there isn't a sprinkler system):

    Code:
    Private Sub b_Conclude_Click()
    Text_ROI.SetFocus
    Me!Text_ROI.SelStart = Me!Text_ROI.SelLength + 1
    Text_ROI.Text = Text_ROI + Chr(13) + Chr(10) + Chr(13) + Chr(10) + "SPRINKLER SYSTEM" + Chr(13) + Chr(10) + Chr(13) + Chr(10) + "The facility contains a XXX type sprinkler system.  All elements of the sprinkler system conform to NFPA XXXX."
    End Sub
    And so on. There are about 50 buttons, but the point is to allow the inspector to generate a multipage report in a certain format with just the details to be filled in manually. The text field can then be copied into email or a Word document.

    The problem I'm having is that while the text field can lots of text, apparently the code I'm using to append the new text onto the end of the existing text is hitting a limit and after entering a certain amounts throws the following error:

    Run-time error '2176':
    The setting for this property is too long

    What is a better way for me to insert the next chunk of text?

    Thanks!!

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Try it without this line:
    Code:
    Me!Text_ROI.SelStart = Me!Text_ROI.SelLength + 1
    And change the + to & when concatonating strings: The .text is not needed.
    Code:
    Text_ROI = Text_ROI & Chr(13) & Chr(10) & Chr(13) & Chr(10) & "FIRE INSPECION 20XX-XXX" & Chr(13) & Chr(10) & Chr(13) & Chr(10) & "A fire inspection was conducted on facility XXX of zone XXX."
    You can also use the vb constant vbcrlf to insert a carriage return line feed.
    Code:
    Text_ROI = Text_ROI & vbcrlf  & vbcrlf & "FIRE INSPECION 20XX-XXX" & vbcrlf & vbcrlf & "A fire inspection was conducted on facility XXX of zone XXX."

  3. #3
    hammer32 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2018
    Posts
    13
    That's awesome, thank you very much!!!

  4. #4
    hammer32 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2018
    Posts
    13
    After using this method for a while, we've decided that many of the buttons should have something like sub-buttons/options/pop-up buttons which would provide the user with three options which would vary the text slightly.

    For instance, one button might be labeled Smoke Detectors. Click on that button and the user would be presented with three buttons that would popup, labeled New Reported Issue, New Not Reported Issue, Repeat Issue. Depending on which of those popup buttons the user pressed, the report template text would have different verbiage. Is something like this possible in Access 2013?

    Thank you very much!

  5. #5
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    When first reading that you were using 50 command buttons to do this my first thought was to use a list box.
    By doing so you'd be able to add additional info without having to add more buttons. You could also have a second list box to do any sub-info (cascading list boxes)

  6. #6
    hammer32 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2018
    Posts
    13
    Quote Originally Posted by moke123 View Post
    When first reading that you were using 50 command buttons to do this my first thought was to use a list box.
    By doing so you'd be able to add additional info without having to add more buttons. You could also have a second list box to do any sub-info (cascading list boxes)
    While there are a lot of buttons, they're laid out in the same pattern as our inspection sheets, so it's fairly quick to locate the one you want. If I could have a left click on a button result in a list box appearing over that button, that could possibly work. Can a list box selection run VB code to append a block of text to a text field?

  7. #7
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    I think Moke is on the right track.
    If I could have a left click on a button result in a list box appearing over that button
    You wouldn't need a listbox for each button, a common listbox could work.

  8. #8
    hammer32 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2018
    Posts
    13
    I'm not sure I'm picturing the right thing. I'm afraid it would look like a single list with 150 options the user would have to scroll through multiple times to build out a report.

  9. #9
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    I'm afraid it would look like a single list with 150 options the user would have to scroll through multiple times to build out a report.
    No, the list would only show what was appropriate for the button just clicked.

  10. #10
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    I'm kinda stuck on this part of your question.
    to generate a multipage report in a certain format with just the details to be filled in manually.
    noting your code
    Code:
    Text_ROI.Text = Text_ROI + Chr(13) + Chr(10) + Chr(13) + Chr(10) + "FIRE INSPECION 20XX-XXX" + Chr(13) + Chr(10) + Chr(13) + Chr(10) + "A fire inspection was conducted on facility XXX of zone XXX."
    why wouldn't you also automate filling in the details or XXX's without doing it manually?

    not sure what your workflow is or what your specific data is so its difficult to be specific with suggestions.

  11. #11
    hammer32 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2018
    Posts
    13
    I appreciate the help! I've got invisible combo boxes over each button. When the button is pressed, the combo box over that button becomes visible, allowing the user to choose a value. That value is then used in a Dlookup along with the button name to selected the required boilerplate text from another table. That boilerplate is then appended to the bottom of the text field and the combo box becomes invisible again.

  12. #12
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Sounds workable indeed. Happy you found a solution.

  13. #13
    hammer32 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2018
    Posts
    13
    Quote Originally Posted by moke123 View Post
    I'm kinda stuck on this part of your question.

    noting your code
    Code:
    Text_ROI.Text = Text_ROI + Chr(13) + Chr(10) + Chr(13) + Chr(10) + "FIRE INSPECION 20XX-XXX" + Chr(13) + Chr(10) + Chr(13) + Chr(10) + "A fire inspection was conducted on facility XXX of zone XXX."
    why wouldn't you also automate filling in the details or XXX's without doing it manually?

    not sure what your workflow is or what your specific data is so its difficult to be specific with suggestions.
    The boiler plate includes things like mandatory discussion topics the inspector should have with the resource owner and then XXX's for the resource owners responses. Once the entire boiler plate has been built, the inspector pastes it into MS Word and fills out all the XXX's with the details.

  14. #14
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Are you not storing results of the inspections? Are the values of the xxx'x being stored in the database?
    I would concatenate the values right into the string rather than have to tab through a word doc to replace the xxx's.
    Code:
    "A fire inspection was conducted on facility " & txtFacility & "of zone " & txtZone & "."

  15. #15
    hammer32 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2018
    Posts
    13
    Ha! I would like to, but progress is slow. Currently the results are kept hardcopy only, which explains why an audit discovered facilities that hadn't had an annual inspection in 10+ years. I'll build out capability such as this as my free time allows and hope I make progress convincing leadership to allow for the electronic storage of records. It's only 2019, right?!

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

Similar Threads

  1. Replies: 4
    Last Post: 01-03-2018, 11:12 PM
  2. Replies: 18
    Last Post: 10-11-2017, 03:07 PM
  3. Replies: 3
    Last Post: 08-21-2014, 09:48 AM
  4. Replies: 3
    Last Post: 12-22-2012, 05:33 PM
  5. Automated Character Count for Text Box
    By gopherking in forum Forms
    Replies: 3
    Last Post: 09-23-2011, 12:21 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
  •  
Other Forums: Microsoft Office Forums