Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    mainerain is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Location
    Maine, USA
    Posts
    146

    vba, to insert a character at cursor position for Long Text field

    I would like to click a button which would insert the "*" character at the cursor position of a field with long text. Essentially do the keyboard strokes of SHIFT + 8 to insert the character. This is for uniformity with multiple users and to be able to remove said character anywhere in field with another button (I've got that vba)

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    This requires breaking the string then recombining. Not sure how to accomplish that without specific text to search for.
    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
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I don't know of any inherent functions in Access/vba to get current cursor position. However, you may be able to adapt this API info to do what you need.

    You may be able to adapt this SelText example
    Let us know how things go.
    Last edited by orange; 10-23-2021 at 10:31 AM. Reason: updated new link

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You can get the cursor position by getting the SelStart property but you will not be able to click anywhere else to run any code. Double click at the desired position then get all text to that position in one variable and all after in another. Then add * to end of 1st variable and concatenate the 2nd. Windows selection of text will take over and highlight all contiguous characters at the double click point. I don't know how to avoid that or if it even matters. Probably could just work around it or look for an API if it's an issue.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    mainerain is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Location
    Maine, USA
    Posts
    146
    Thanks for the reply's, Unfortunate that mimicking keystrokes doesn't work.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by mainerain View Post
    Thanks for the reply's, Unfortunate that mimicking keystrokes doesn't work.
    Not sure what that means. You want to trigger this based on some sort of keyboard input/shortcut? I tested and got it to work with a double click.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    mainerain is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Location
    Maine, USA
    Posts
    146
    Quote Originally Posted by Micron View Post
    Not sure what that means. You want to trigger this based on some sort of keyboard input/shortcut? I tested and got it to work with a double click.
    I've seen solutions for fields with Short Text but when I tried to apply it to Long Text that has highlights and other type formatting it didn't go so well. Was your solution with a Long Text field and various formatting?

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    It was short text. It will work on long text but not with rtf formatting.
    You didn't mention that the field was rtf - very important. Might still be possible, but would require a way to include all the rtf tags in a string. I don't know if there is a way to get everything in the rtf line. If you can forego the rtf formatting you already have a solution I'd say. If not, perhaps someone already has code to build a string with rtf tags.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Update: can work with memo/long text field with formatting but you would lose the formatting. I see no way around that because the location of the cursor in a string as short as test could, in reality, be as few as 14 (<div>test<div>) or as many as 100 or more. This is 129: test

    Sure, you probably don't have anything like that, which really wouldn't be the point. My point is that the position to insert the character could be anything, making it virtually impossible (or at least extremely impractical) to do anything about it. Sample code:
    Code:
    Private Sub Text6_DblClick(Cancel As Integer)
    Dim lngStart As Long
    Dim strLeft As String, strRight As String, strNoRTF As String
    Dim db As DAO.Database
    
    Set db = CurrentDb
    lngStart = Me.Text6.SelStart 'test|test results in 4
    strNoRTF = Application.PlainText(Me.Text6) 'convert to plain text
    strLeft = Left(strNoRTF, lngStart) & "*"
    strRight = Mid(strNoRTF, lngStart + 1)
    db.Execute "UPDATE tblRtf SET rtfText = '" & strLeft & strRight & "' WHERE ID =" & Me.Text9 '< ID field for record
    Me.Dirty = False
    Me.Refresh
    Me.Text6.SelLength = 0
    
    End Sub
    As noted, formatting will be lost. Also, * character is highlighted in spite of SelLen = 0 regardless of which side of Refresh that I put it on, which I guess is a Windows thing. Should be solvable but I'm guessing the code isn't going to help you as it is anyway.
    Last edited by Micron; 10-23-2021 at 09:20 PM. Reason: added comment
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    mainerain is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Location
    Maine, USA
    Posts
    146
    Quote Originally Posted by Micron View Post
    Update: can work with memo/long text field with formatting but you would lose the formatting. I see no way around that because the location of the cursor in a string as short as test could, in reality, be as few as 14 (<div>test<div>) or as many as 100 or more. This is 129: test
    Thanks for your information and effort. I really wish to keep formatting so I will have to make necessary adjustments.

  11. #11
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I have been dabbling with this problem on and off for a couple of days, between chores and the World Series
    The more I dwell on it, the more I discover so it is intriguing from that standpoint.

    When you really get into the job at hand, it's intriguing as to what arises. F'rinstance, a RTF field in a table might not have any rtf encoding in a record, so the field content is actually plain text. I have accounted for that in my code tests. Then there is the fact that with rtf fields, "<" is not recorded as "<". Rather, it is "&lt;", which presents another level of difficulty when deciding where to insert a particular character - IF your field can contain such characters. For that, the thought is that you might need a table of rtf/html entity names (as they're called); e.g. "&lt" because there must be 200 or so of these codes. How many might you encounter is the question.

    So far, my self propelled dalliance has involved arrays, collections, and at one point I considered the Dictionary object (because I was reminded that I could store a collection object but cannot retrieve it based on its key - it works the other way around). I mention this because as June7 noted, you might need a way to separate the rtf/text portions, so my thought was to store each portion in a collection and then re-assemble them. Not that simple.

    I think there might be a way to do this if code parses out whatever comes between < and > tags as long as that isn't &lt; and &gt; (that's how rtf characters are literally renderd) so that's where a table of entity names would come in to play. Code would examine every character outside of < > one by one to decide whether or not to add that character to the plain text collection. While stepping through the characters in the plain text portion, once you reach the SelStart value you add the desired character (*) and keep going, adding either to the plain text collection or the rtf collection. In the end, you have to piece it all back together.

    That's a bit of rambling that you may or may not be able to follow. My intent is to impart some level of appreciation for the difficulty of what you asked for in the beginning. The crux of the matter is that while you might see "testtest" in a control and think that is 8 characters, it is at least 18 in a rtf field (<div>testtest<div>), and perhaps dozens more, depending on font color, bold, italic, underline, etc. tags. That's where the difficulty lies.

    If your intent is to abandon this, I hope you'll let me/us know so that I don't waste any more beer or Scotch in trying to come up with something that works.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  12. #12
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I'll take up the reins for a couple of posts. First a question

    I would like to click a button which would insert the "*" character at the cursor position of a field with long text.
    rather than clicking a button, why not just insert the * character. You can include code on the keypress event to ignore any other character.

    But to do it your way, this is where some examples would come in handy as this suggestion may or may not work - all depends if there are duplicates

    1. in your form module create a common variable

    Code:
    dim sstart as integer
    in your richtext control lost focus event put
    Code:
        sstart = Text0.SelStart
    in your button click event put

    Code:
    Dim ptxt As String
    Dim stxt As String
    
    
        ptxt = PlainText(Text0)
        stxt = Mid(ptxt, sstart+1, 5) 'adjust the 5 to suit - the expectation is that the 5 (plaintext) characters following the cursor position is unique. If the cursor position is within 5 chars of the end, only the remaining chars will be used
    'edited to include +1
    
        myRTControl = Replace(myRTControl, stxt, "*" & stxt, , 1) ' the 1 tries to mitigate duplicates
    Edit: just done a bit more testing - if there is formatting code within the following 5 chars of the richtext string, the code will fail because the replace won't insert the "*" as an exact match on the plaintext string is not found
    Edit#2: selstart is an integer, so a maximum value of 32767. Long text can be considerably longer than that so this code will also fail if this is the case

  13. #13
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    rather than clicking a button, why not just insert the * character.
    I woke up this morning and that question popped into my head. Thought there must be more than what has been revealed because it's what anybody with a keyboard would do.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  14. #14
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    My first question was WHY? But as micron says - there must be more.

    @mainerain Can you tell us more about the requirement?

  15. #15
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    sstart = Text0.SelStart
    Had to try that because I figured that since a control has to have the focus to use the SelStart property, how could it work if focus has been lost? Remarkably, it works even though I thought the right place would be exit event since that occurs before lost focus event. Seems counter intuitive to me.

    In the absence of OP responses to questions or the issue, I'm going to close the db with my current code for this.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 15
    Last Post: 02-17-2019, 06:19 PM
  2. Replies: 9
    Last Post: 02-06-2019, 05:49 PM
  3. Replies: 6
    Last Post: 01-13-2017, 08:54 AM
  4. Replies: 2
    Last Post: 01-04-2016, 04:44 AM
  5. Bold Field by Cursor Position
    By Rawb in forum Forms
    Replies: 4
    Last Post: 02-08-2011, 08:03 AM

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