Highlight the Searching text using Access VBA

Highlight the data on Microsoft Access Form.

This article is to enabling the searching on the textbox based on the selecting field on dropdown. This article basically demonstrates the searching criterion to highlight the string data from continues Form.

For implementation of this we need a database from where we have to find the record. So firstly we have to make table as shown in Fig 1.1.

Highlight text in Access textbox using VBA  Fig-1.1

Fig-1.1

Next is we have to create the Form with given control like combo box and textbox in header part and also bound with table filelds.Now we have to code on boths after update property. In detail part we have to take two textbox and bound with appropriate bound fileds according to requirement. And take another textbox for highlight and make it unbound. And set it to in Continues from as shown in Fig 1.2.

Highlight text in Access textbox using VBA  Fig-1.2

Fig-1.2

After bound all the control we have to view in Form view as shown in Fig 1.3.

Highlight text in Access textbox using VBA  Fig-1.3

Fig-1.3

After coding part we have to test this searching Form. Type some alphabet in search textbox. The matching fields will come with highlighted text as shown in Fig 1.4.

Highlight text in Access textbox using VBA  Fig-1.3

Fig-1.4

VBA CODE

Option Compare Database
Option Explicit
Private Const conMod = "Form_Highlight"
Private Sub Form_Load()
Me.txtSearchText = Null
Call SElECTION_AfterUpdate
Exit_Handler:
Exit Sub
End Sub
Private Sub SElECTION_AfterUpdate()
Dim ctl As Control
If Not IsNull(Me.SELECTION) Then
Set ctl = Me.SELECTION
With Me.txtSearchDisplay
ctl.Top = ctl.Top
ctl.Left = ctl.Left
End With
End If
Call txtSearchText_AfterUpdate
Exit_Handler:
Set ctl = Nothing
Exit Sub
End Sub
Private Sub txtSearchText_AfterUpdate()
Dim strField As String
Const TAGSTRING = ""
Const ENDTAG = "
"
Dim SEARCHSTRING As String
Dim CONTROLSTRING As String
Const WILDCARDSTRING = "*"
If IsNull(Me.SELECTION) Or IsNull(Me.txtSearchText) Then
If Me.FilterOn Then
Me.FilterOn = False
End If
Else
strField = "[" & Me.SELECTION & "]"
SEARCHSTRING = Me.txtSearchText
Me.Filter = strField & " Like """ & WILDCARDSTRING & SEARCHSTRING & WILDCARDSTRING & """"
Me.FilterOn = True
CONTROLSTRING = "=IIf(" & strField & " Is Null, Null, " & "Replace(" & strField & ", """ & SEARCHSTRING & """, """ &TAGSTRING & SEARCHSTRING & ENDTAG & """))"
With Me.txtSearchDisplay
.ControlSource = CONTROLSTRING
.Visible = True
End With
End If
Exit_Handler:
Exit Sub
End Sub
End Sub


DISCLAIMER

It is advised that the information provided in the article should not be used for any kind formal or production programming purposes as content of the article may not be complete or well tested. Access Guru will not be responsible for any kind of damage (monetary, time, personal or any other type) which may take place because of the usage of the content in the article.