How to Use VBA to Find Specific Text in an Access Text Box
Question details
The user needs to locate a specific text phrase within a large text field on a Microsoft Access form using VBA.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Searching for a specific string such as "CASE LINE UP" inside a form's text box programmatically.
- Observed behavior
- Requires a VBA solution to determine if the phrase exists and to navigate to the matching record if found.
Ensure you have a basic understanding of writing VBA code in Microsoft Access, and verify the exact name of the target text box field on your form.
Use a DAO Recordset Clone and FindFirst Method
Utilize a DAO recordset clone to search the underlying data for the specific text and move the form's bookmark to the matching record.
This method searches the form's underlying recordset without affecting the current view until a match is found. Using the Like operator allows for flexible substring matching.
Press Alt + F11 to open the VBA Editor and locate the module for your specific Access form.
Inside your search button's click event, declare a recordset clone variable by typing: Dim rs As DAO.Recordset.
Assign the clone by typing: Set rs = Me.RecordsetClone. This creates a hidden copy of the data currently on the form.
Use the FindFirst method with the Like operator to search the field: rs.FindFirst "[YourFieldName] Like '*CASE LINE UP*'" (replace YourFieldName with your actual text box control source).
Add an If statement: If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark Else MsgBox "Phrase not found.". This moves the form to the record if a match is found, or alerts the user if it isn't.
Use the Built-in Find and Replace Feature
If a programmed VBA solution is not strictly required, Access provides a built-in search tool to find phrases in any part of a field.
Looking for a Lightweight and Free Office Suite?
While WPS Office does not currently offer a direct database alternative to Microsoft Access, it provides powerful, free tools for Word, Excel, and PowerPoint files. If you frequently handle data manipulation, searching, and filtering, WPS Spreadsheet is an excellent, lightweight option to process large datasets seamlessly.
Frequently Asked Questions
Can I use a variable or another text box for the search phrase in VBA?
Yes. Instead of hardcoding the search phrase, you can concatenate a text box value into the FindFirst string. For example: rs.FindFirst "[MyField] Like '*" & Me.SearchBoxName & "*'".
How do I find exact complete words instead of substrings in Access?
Using the Like operator with asterisks (*) matches any substring. To find exact whole words and avoid unintended partial matches, you will need to implement a custom VBA function that parses the text string or utilizes regular expressions to respect word boundaries.
What happens if the Access text box is empty or null?
If the text box or the underlying field might be null, you should include a check in your VBA code. Using the Nz() function or verifying IsNull(Me.MyField) before executing the search can prevent unexpected runtime errors.
