*NOTE: This code has been updated to work much better since original publication*
When developing simple plain text "Email Merge" capabilities in a few databases, I recognized the need for the user to be able to choose available merge fields from a combo box to place into the message text. This is actually very easy to achieve.
This is a simple example, though I'm sure you will be able to adapt it easily to accommodate multiple "insert" options, etc. The main thing to keep in mind is that after any action that will change either the content of the textbox or the cursor position, you should call the "CheckCursorPos" function.
To see this in action...
1. Create a form (frmInsert).
2. Place the following controls on the form:
-----TextBox (txtMessage)- draw it large, to accomodate multiple lines, set the "Enter Key Behavior" property ("Other" tab) to "New Line in Field"
-----Combo Box (cmbInsert) - set the "Rowsource" property to a list of the values you want the user to have the option of inserting. This could be anything from your database or a custom list. For the current example just put in the following:
Code:
"<FirstName>";"<LastName>";"<SomeText>"
3. Open up the form's module and paste in this code:
Code:
Option Compare Database
Option Explicit
Dim lngCursorPos As Long
Dim strText1 As String
Dim strInsert As String
Dim strText2 As String
Function CheckCursorPos()
lngCursorPos = Me.txtMessage.SelStart
'Debug.Print "lngCursorPos: " & lngCursorPos
End Function
Private Sub cmbInsert_AfterUpdate()
Dim lngNewCursorPos As Long
strInsert = Me.cmbInsert
lngNewCursorPos = Len(strText1 & " " & strInsert & " ")
Me.txtMessage = strText1 & " " & strInsert & " " & strText2
Me.txtMessage.SetFocus
Me.txtMessage.SelStart = lngNewCursorPos
Call CheckCursorPos
End Sub
Private Sub txtMessage_Click()
Call CheckCursorPos
End Sub
Private Sub txtMessage_Exit(Cancel As Integer)
Dim lngLenAllText As Long
Dim strCurrentText As String
strCurrentText = Me.txtMessage
lngLenAllText = Len(strCurrentText)
If lngCursorPos > lngLenAllText Then
'add space to end to make up difference
strCurrentText = strCurrentText & " "
lngLenAllText = Len(strCurrentText)
End If
strText1 = Left(strCurrentText, lngCursorPos)
strText2 = Right(strCurrentText, (lngLenAllText - lngCursorPos))
End Sub
Private Sub txtMessage_KeyUp(KeyCode As Integer, Shift As Integer)
Call CheckCursorPos
End Sub
4. Try it out:
---1. Open the form in form view.
---2. Type some text into the "txtMessage" box.
---3. Place the cursor (ie click) anywhere in the middle of the text you typed.
---4. Chose an item from the combo box
The text from the combo box should be inserted into the text box at the cursor location.
