Access 07 Rich Text - SelStart, SelLength (1 Viewer)

[MightyAC]

I have form where I allow users to create mail merge letter templates. The form is basically a memo field that contains regular text and some merge codes which I later replace with data.

Example:
Dear <<FirstName>> <<LastName>>

I also have a popup form with a list of all the available merge codes. Users simply double click on the merge code the want to insert and then using the selstart and sellength properties of the memo field I can insert the merge code in the appropriate position. That all works great.

Now, using Access 07, I want to use rich text. The problem I am having is the the selstart and sellength properties return numbers based on the plain text, ignoring the html code. Thus the merge codes are being inserted in the wrong position.

Dear <<FirstName>> <<LastName>> in rich text format is:

<div><font face=Verdana>Dear <<FirstName>> <<LastName>></font> </div>

I could change the application and force users to copy and paste the codes into the appropriate position but I'd rather find a way to make it work as it is. Any suggestions?

 

[EddyJawed]

I'm struggling with the same problem. The only thing i can think of is when they're changing the text, fire off an external browser of some sort, or even microsoft word extension. Can't find anything equivalent to Selstart that can read the formatted rich text and return the correct positions.
Am sure there is a way in access to embed a MS word previewer within your access form which you can edit text in, that could have Selstart working properly in too. Otherwise if anyone else has another better alternative then let us know.

 

[EddyJawed]

I'm struggling with the same problem. The only thing i can think of is when they're changing the text, fire off an external browser of some sort, or even microsoft word extension. Can't find anything equivalent to Selstart that can read the formatted rich text and return the correct positions.
Am sure there is a way in access to embed a MS word previewer within your access form which you can edit text in, that could have Selstart working properly in too. Otherwise if anyone else has another better alternative then let us know.

Another idea that springs to mind is using the web browser, and embedding it inside the ms access form. You could create a text editing html page and navigate your browser to this. Use a Javascript version of Selstart to insert the << text >> values in your text over there, then use Sendkeys to Select All + Copy, to help carry the text over and paste in your textbox object.

 

[spikepl]

You have string functions like InStr and Replace - what prevents you from using those?

 

[EddyJawed]

Hello SpikePL,

The problem I have is working out the position of the cursor in the RTF textbox. How would Instr or replace help me work out the position of the cursor?

Thanks

Eddy

 

[spikepl]

Ok I think I did not understand the problem.

You want to be able to insert text at the cursor position, but Selstart seemingly returns a position excluding all the markup, so you cannot insert your text by adding the string in code because it winds up in a wrong spot.

Have you tried some copy paste? DoCmd.RunCommand has some parameters for copying pasting to/from clipboard - perhaps that would work at current cursor position?
I would grab the selected merge code, put it in a textbox, give that textbox focus, copy to clipboard, go to the rich text box, put the cursor at the desired position, and then paste. If that works, perhaps the intermediate textbox could be made invisible and still work, not sure there.

 

[EddyJawed]

Hi Spike, yeah I was also thinking along those lines. I don't think we have another choice...but i was reading an article today where someone used another technique,

On SelChange or Change event,
1. Get current line number by GetLineFromChar method.
2. Now get start of current line by using EM_LINEINDEX. It would be our SelStart.
3. Now use EM_LINELENGTH message to get total number of character of our line. This is our SelLength.
4. We have our SelRTF.

which shows another way to do it. So I might try this as 'PLAN A' first, if not then I'll go for your suggestion. Cheers

 

[EddyJawed]

OK had another hour this morning to look into this again and came up with something that may help me get the cursor position (.Selstart property) of a Rich Text Format RTF textbox within MS Access. Time to call 'Major' TOM a.k.a TOMDEC api...

Here's my code (with credits to DEV ASHISH and MOEUR whose code I amalgamated to create this) check it out:

First call API's Functions and place in Access Module:-

Public Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long

Public Declare Function apiGetFocus Lib "user32" Alias "GetFocus" () As Long

Private Const EM_GETLINECOUNT = &HBA
Private Const EM_LINEINDEX = &HBB
Private Const WM_USER = &H400&
Private Const EM_GETOLEINTERFACE = (WM_USER + 60)

Now since access controls don't have 'Hwnd' properties, as they paint the controls at runtime, you'll have to artificially get them using the following function

Function fhWnd(ctl As Control) As Long
On Error Resume Next
ctl.SetFocus
If Err Then
fhWnd = 0
Else
fhWnd = apiGetFocus
End If
On Error GoTo 0
End Function

Now to get the cursor position use this procedure:-

Private Sub GetCursorPos(iLine As Integer, iPos As Integer)
Dim lCount As Long
Dim i As Long
Dim LN As Long
Dim Ctrl As Control
Set Ctrl = Me.Controls.Item(Me.'Controlname'.Name)

RTBHwnd = fhWnd(Ctrl)

lCount = SendMessage(RTBHwnd, EM_GETLINECOUNT, 0&, 0&)
LN = SendMessage(RTBHwnd, EM_LINEINDEX, -1&, 0&)
For i = 1 To lCount
If LN = SendMessage(RTBHwnd, EM_LINEINDEX, i - 1, 0) Then Exit For
Next i
iLine = i
iPos = Ctrl.SelStart - LN + 1

Debug.Print iLine & "/" & iPos

End Sub

Fire Getcursorpos in an event and you should get the line number/column of your RTF textbox....now unfortunately theres one problem 'EM_LINEINDEX' in my code is not retrieving the line number of the text box. If I can get this, then this code is complete and can be used on any control with RTF text in it.

 

[spikepl]

It seems to me like a lot of code, predicated on your need to obtain correct position in the string, that is displayed in the rich-text text box, because you wish to manipulate that string in code.

My personal preference is to do the least coding possible to do the job, so that's why I was thinking about using the built-in copy/paste facilities, which would obviate the need for knowing where in the markup you are, since the selstart seems to return the correct position in the display - enough for pasting.

 

[EddyJawed]

Yeah the only worry I have with your suggested method (and this is before testing) what i can think of is, what you would do if the user placed something else in the clipboard half-way in between their CTRL +V action into the textbox. For the work that I am doing and the kind of dummy's that I am building the software for, that situation is entirely plausible

 

[spikepl]

Then invert the process. Show where to put it first (record SelStart) and then select what to put in there. It's a bit opposite normal windows operation but dummies won't notice

 

[spikepl]

And the user is not supposed to do CTRL V . That why I referred to the RUnCOmmand.

 

[jet46]

I know this is a really old thread but I spent all evening search for a solution to a similar problem without any success but I just came up with a solution that seems to work and I wanted to post it just in case anyone else is having the same problem. You would think Microsoft would have addressed this in Access 2010 considering there is no straight forward way to use SelStart on a memo field with rich text.... :-(

The problem with using the SelStart and SelLength functions on rich textbox memo fields is that they use the position of the character as displayed, not as saved in the database with the formatting tags(which of course you already knew).

The problem with InStr is that it returns the actual character position INCLUDING formatting tags so it doesn't play nice with SelStart.

The solution I came up with is to use InStr in conjunction with the PlainText function (which converts rich text to plain text). However, PlainText seems to add extra characters for carriage returns which screws up the position of the selstart adding an extra character for each carriage return so I just removed the carriage returns with a Replace function. And it works!

Dim x As Long
Dim MemoPlainText As String
MemoPlainText = PlainText(RTFField) 'Where RTFField is a memo field with rich text
MemoPlainText = Replace(MemoPlainText, Chr(13), "") 'Remove carriage returns
x = InStr(1, MemoPlainText, TextToSearchFor) 'Gets the position for the SelStart
RTFField.SelStart = x
RTFField.SelLength = SomeLengthGoesHere

 

[Zakraket]

Still an old topic but I'm trying to tackle this problem a.t.m. and this post is the only discussion I could find on the internet regarding this exact functionality:

a RTF-memofield in which a user can type and format text and then replace certain parts of it with a "code" that allows for datamerging, by selecting that part of the text and choosing a "datafield" somewhere to replace the selection.


I tried the above (last) try to a solution, but that doesn't seem to do exactly what's needed if I'm not mistaken.
The problem is that in order to replace the proper part of the text I need to know at which exact position in the source-text (text including HTML-tags) the part of the text is that I want to replace, f.e., because a Replace() from the wrong position could replace the wrong part of the text.
The last offered solution doens't give you this position in the source-text, so while it will replace the selection you made it could replace the wrong part

123456789012345678901234567890
Text:
A piece of text, text is what I want to replace
 
Sourcetext incl. HTML-tags:
<div>A <b>piece of text, text</b> is what I want to replace</div>

I would like to replace the second "text", which has a startposition of 18 based on memo.selStart ánd a Instr() in the PlainText(memo) like in abovce sample, but when I use a Replace(memo, "text", "[field]", 18, 1) it will replace the first "text", because the HTML-tags "shift" the position in the source-text, because that "text" shifts to position 20 which will be the first occurance Replace() will find


I think I did find a solution I'd like to share

Controls of the form: Memo (RTF memofield) and cmdInsert()

Form-code:

'Form public variables
Private intSelStart As Integer
Private intSelLength As Integer
 
Private Sub Memo_MouseUp(Button As Integer, Shift As Integer, x As Single, Y As Single)
'set variables after making selection and releasing mousebutton
intSelStart = me.Memo.SelStart
intSelLength = me.Memo.SelLength
End Sub
 
Private Sub cmdInsert_Click()
're-set selection based on variables
'replace selection by text of choice 
Me.Memo.SetFocus
Me.Memo.SelStart = intSelStart
Me.Memo.SelLength = intSelLength
Me.Memo.SelText = "[databasefield]"
 
'[databasefield] should be determind by f.e. a listbox showing available fields

This replaces the EXACT selection with the string provided and will not accidentally replace some other part of the text.

It seems necessary to re-set the selection because pressing the cmdInsert removes the focus from the Memo-field and loses the selection. Without the setfocus and re-setting the selection I get an error.
Maybe there is a better way to do this, but this really works

 

[jet46]

Hi Zakraket,

Good work. I think this is definitely useful however I would point out that it serves the opposite purpose of my solution (which given your needs is obviously what you need to do).

If I understand correct, in your solution the USER highlights a particular area of text and then you want to replace the text highlighted by the user.

In my solution, the COMPUTER seeks out and highlights a particular pre-defined string. The string which you are attempting to highlight is already known (and not something selected by the user) and it specifically seeks out the 1st instance of that text.

For example, I may have a letter to a customer in a memo field and I want to highlight each instance (one at a time) of the string <<Type Info Here>> so that when I am writing a letter to a customer, I can pull up a template of the letter and there would be a button on the form labeled “Next Custom Input Field” and each time the button is pushed, it would find the 1st instance of <<Type Info Here>> and highlight it allowing the user to enter custom text there.

IE:
Dear <<First Name>>,

Your insurance will cancel on <<Type Info Here>>.

The reason for this cancellation is <<Type Info Here>>.

So the first time you push the button, it finds the first instance of <<Type Info Here>> and the user can just start typing away. When they are done with that sentence, they click the button again and it automatically moves to the next instance.

 

[Zakraket]

Well...maybe I misread some parts of this topic I think the topicstarter wants to do what I'm trying to do, that's what pulled me to this topic. I now see you write about a "similar" problem, so I could have know the subject had slightly changed Didn't realise you were trying to solve a slightly different problem so no surprise you solution didn't work for me

Anyway, now a solution for both problems in this topic:
1: user creates mailmergetext in RTF-field and replaces parts of it for databasefields by selecting text in the field
2: have the computer highlight parts of the text in the RTF-field (and replace them or have the user replace them)

I might just need your solution since AFTER the user has created a mailmergetext with databasefields I still need to replace them with the proper data... Have not looked into that next step of my proces, but I think a simple replace() should be sufficient for that (the HTML wont be in my way I guess). But maybe it is

Lots of opportunity to make custom mailmergelike functionality easily accessed by users and without a lot of code!

 

[NOTNOTJON]

Hello

Been struggling with this for the last while as well. My situation is slightly different, but the struggle is the same:

On a form, I'm trying to build an interface to edit the RTF attributes of a textbox.

Problem is, I have an Access database that must act like a tool. It mimics a regular .exe by hiding the main Access window and taskbar icon via hwnd functions so all of Access' nice built-in functionality is lost.

My thought process for what I need to do is

Like Zakraket, I track the SelStart and SelLength of textbox1.
* I need to copy just the selected portion of text to be edited to textbox2
I use code to strip out all tags to get plain text (code below).
Font dialog appears and the text is marked up the way the user wants
I select the entirety of textbox2
* I then need to delete the selected text and replace with new text.

I would think that the solution to my problem and your problem go hand in hand.

Here is the code that strips out all the marker points in RTF'd text:

Function RTFtoPlain(rtfString) As String
Dim i As Long
Dim copy As Boolean
Dim nextcopy As String

copy = False
RTFtoPlain = ""

For i = 2 To Len(rtfString)
 
 If nextcopy = "Y" Then
  copy = True
  nextcopy = ""
 End If
 
 If nextcopy = "N" Then
  copy = False
  nextcopy = ""
 End If
 
 Select Case Mid(rtfString, i, 1)
  Case "<": copy = False
  Case ">": copy = False
            nextcopy = "Y"
  Case "&": copy = False
  Case ";": copy = False
            nextcopy = "Y"
 End Select

 If copy = True Then
  RTFtoPlain = RTFtoPlain & Mid(rtfString, i, 1)
 End If

Next i

End Function

I'm thinking at the moment the best way to solve your problem is to put in special character (eg. vba.chrW(5555) ) markers in the place of your << and >> tags. This would effectively allow you to use the InStr function to find exactly where you want to replace text. You would use different markers for the start and stop tags to identify SelStart and SelLength.

 

[SmallTime]

Just stumbled on this a little late. Why not just compose the document in word, just set up a 'Master' blank dot or dotx with the all the available fields and let the users create their templates from that.

If you want to create a preview in your app. just copy and past the text from Word (perhaps though vba if you wish) It'll take all the sweat out of trying to place the fields like «First Name» «Last Name» in the right place through code. You could then maybe populate the preview fields with Replace statements

 

[SmallTime]

Just stumbled on this a little late. Why not just compose the document in word, just set up a 'Master' blank dot or dotx with the all the available fields and let the users create their templates from that. If you want to create a preview in your app. just copy and past the text (perhaps though vba if you wish) It'll take all the sweat out of trying to place the fields like «First Name» «Last Name» in the right place through code. You could then maybe populate the fields with Replace statements

 

[Vladimir]

I know it's very late to answer, but I hope some will be happy to find my post.
I was trying to resolve the same problem only a few day ago. May my solution help somebody.
The trick is to track SelStart on MouseUp & KeyUp events. My RTF TextBox is an unbound control and it fills up on a subform's Form_Current event. I need to insert text strings into RTF TextBox after a double-click on a seperate ListBox .

Sample app: sorry, I can't post the link due to forum limitation.
Video: sorry, I can't post the link due to forum limitation.

Note 1:
Check the frmTextyVzoroveTisk.pomTxt (TextBox) which helps to store RTF TextBox contents into the subform's recordset.
txPoz & txPoz1 TextBoxes help to debug. You may remove them; don't forget to remove an appropriate code in frmTextyVzoroveTisk.fncSelStart.

Note 2:
I'm sure some will find an easier solution. I'm happy with the one I have at the moment.

Note 3:
Also see fMain in sample app. There are a few procedures to simulate SelStart bug.
See the following:
sorry, I can't post the link due to forum limitation.
sorry, I can't post the link due to forum limitation.
sorry, I can't post the link due to forum limitation.
sorry, I can't post the link due to forum limitation.

Note 4:
Sorry for unwanted fields in some tables and for unused code; sorry for Czech language in data fields. I was in a hurry to create the sample app.

NOTE 5 - Links:
See my post below.