Insert Text After a Matching Phrase in Microsoft Access VBA
Question details
Insert meeting details immediately after a matching phrase in an Access memo field displayed on a form.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- A button on a Minutes form should add details, such as the current reporting time, after an agenda phrase in the current record's text box.
- Observed behavior
- The phrase can be located with InStr, but the user needs a way to insert text at that position.
Open the Minutes form in Design View and identify the names of the button and the text box bound to the memo or Long Text field. The example uses PreviousTaskDetails for the button and MinutesText for the text box; replace these names with your actual control names.
Insert the details with InStr, Left, and Mid
Use a button's Click event to insert text after the first matching phrase in the current form record.
InStr finds the phrase, and Left and Mid rebuild the original text around the insertion. The example uses a case-insensitive comparison and leaves the text unchanged if the phrase is not found.
In the form's Design View, select the PreviousTaskDetails button. Open its Property Sheet, choose the Event tab, set On Click to [Event Procedure], and select the build button to open the VBA editor.
In the button's Click procedure, replace the example control and procedure names as needed. Use this code: Private Sub PreviousTaskDetails_Click() Dim originalText As String Dim phrase As String Dim insertedText As String Dim matchPos As Long Dim insertPos As Long originalText = Nz(Me!MinutesText.Value, "") phrase = "Previous task report" insertedText = " - Reported at " & Format(Now(), "yyyy-mm-dd hh:nn") matchPos = InStr(1, originalText, phrase, vbTextCompare) If matchPos > 0 Then insertPos = matchPos + Len(phrase) Me!MinutesText.Value = Left(originalText, insertPos - 1) & insertedText & Mid(originalText, insertPos) Else MsgBox "The phrase was not found." End If End Sub
Return to Form View, open a record containing the phrase, and click PreviousTaskDetails. Confirm that the reporting time appears immediately after the phrase, then save the record using the form's normal save action or by moving to another record.

Store agenda details in separate fields or related records
Use a structured design when meeting items and their details need to be maintained or reported separately.
Manage meeting documents with WPS Office
WPS Office is a free, lightweight alternative for working with meeting documents and spreadsheets in a familiar interface. Open compatible Microsoft Office files to make migration easier. WPS Office does not replace Access databases or fix Access VBA code.

Frequently Asked Questions
How do I make the phrase search case-insensitive?
Pass vbTextCompare as the fourth argument to InStr, as in InStr(1, originalText, phrase, vbTextCompare).
What happens if the phrase is not in the text box?
InStr returns 0. The example's Else branch displays a message and leaves the text box unchanged.
Will this update the underlying memo field?
Yes, if MinutesText is bound to that field and the record can be edited. The assignment updates the form control; save the record using the form's normal save action or move to another record.
How does the code handle an empty memo field?
Nz(Me!MinutesText.Value, "") converts a Null value to an empty string. Because the phrase is absent, the example displays the not-found message.




