VBA & Macro Problems

Insert Text After a Matching Phrase in Microsoft Access VBA

John WilsonJohn Wilson Nov 20, 2026 869 views

Question details

Insert meeting details immediately after a matching phrase in an Access memo field displayed on a form.

Insert Text After a Matching Phrase in Microsoft Access VBA
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the button event

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.

2
Add the insertion code

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

3
Test the button

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.

Insert the details with InStr, Left, and Mid
First match only: The code inserts text after the first occurrence. Clicking the button again inserts another copy after that same occurrence.
Free Microsoft Office alternative

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.

Create and edit meeting minutes in WPS Writer.Open and edit compatible Microsoft Word, Excel, and PowerPoint formats, including DOCX, XLSX, and PPTX.Use a familiar interface for everyday document and spreadsheet tasks.Work with a lightweight, free office suite.
microsoft office alternative - wps office

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.