I finally got it sorted out and it works for me well.
Thanks for helping out.
Private Sub txtEntry_Change()
On Error GoTo Err_ErrorHandler
Dim strStartSQL As String
Dim strWhereSql As String
Dim strMobileSql As String
Dim strWhereSqln As String
Dim strWorkPhoneSql As String
Dim strEmailSql As String
Dim vSearchString As String
PlaySound
'Create a string (text) variable
vSearchString = txtEntry.Text
txtSearchExpression.Value = vSearchString
' build sql string for form's RecordSource
strStartSQL = "SELECT [QryFinanceStatementAll].[FinanceID], [QryFinanceStatementAll].[ParentID]," _
& "[QryFinanceStatementAll].[InvoiceNumber], [QryFinanceStatementAll].[OrderDate]," _
& "[QryFinanceStatementAll].[DueDate], [QryFinanceStatementAll].[ChildName]," _
& "[QryFinanceStatementAll].[FreightCharge], [QryFinanceStatementAll].[SalesTaxRate]," _
& "[QryFinanceStatementAll].[LineTotal], [QryFinanceStatementAll].[ShipDate]," _
& "[QryFinanceStatementAll].[Total Payments], [QryFinanceStatementAll].[Total Sale]," _
& "[QryFinanceStatementAll].[Amount Due], [QryFinanceStatementAll].[Family Name]," _
& "[QryFinanceStatementAll].[InvoiceStatus] FROM [QryFinanceStatementAll]"
'This works fine but will not work for names like O'Jay
'strWhereSql = " WHERE QryFinanceStatementAll.InvoiceNumber Like ""*"" & '" & Me.txtSearchExpression.Value & "' & ""*"";"
'strWhereSqln = " WHERE QryFinanceStatementAll.InvoiceNumber Like ""*"" & '" & Me.txtSearchExpression.Value & "' & ""*"""
'strEmailSql = " OR QryFinanceStatementAll.ChildName Like ""*"" & '" & Me.txtSearchExpression.Value & "' & ""*"""
'strWorkPhoneSql = " OR QryFinanceStatementAll.InvoiceStatus Like ""*"" & '" & Me.txtSearchExpression.Value & "' & ""*"""
'strMobileSql = " OR QryFinanceStatementAll.DueDate Like ""*"" & '" & Me.txtSearchExpression.Value & "' & ""*"";"
'This is the solution. Works for names with apostrophes.
strWhereSql = " WHERE QryFinanceStatementAll.InvoiceNumber Like ""*" & Me.txtSearchExpression.Value & "*"" "
strWhereSqln = " WHERE QryFinanceStatementAll.InvoiceNumber Like ""*" & Me.txtSearchExpression.Value & "*"" "
strEmailSql = " OR QryFinanceStatementAll.ChildName Like ""*" & Me.txtSearchExpression.Value & "*"" "
strWorkPhoneSql = " OR QryFinanceStatementAll.InvoiceStatus Like ""*" & Me.txtSearchExpression.Value & "*"" "
strMobileSql = " OR QryFinanceStatementAll.DueDate Like ""*" & Me.txtSearchExpression.Value & "*"" "
Select Case fraSearchOpt
Case 1
strSQL = strStartSQL & strWhereSql
Case 2
strSQL = strStartSQL & strWhereSqln & strWorkPhoneSql & strEmailSql & strMobileSql
End Select
Forms!frmMainMenu!SubMenu!MiniMenu.Form.RecordSource = strSQL
Forms!frmMainMenu!SubMenu!txtRecordSource = strSQL
Forms!frmMainMenu!SubMenu!MiniMenu.Form.Requery
RemoveFilter
If Me.RecordsetClone.RecordCount = 0 Then
Forms!frmMainMenu!SubMenu!txtFocus.SetFocus
Forms!frmMainMenu!SubMenu!lblSubTitle.Visible = True
Forms!frmMainMenu!SubMenu!imgForm.Visible = True
Forms!frmMainMenu!SubMenu!lblSubTitle.BackColor = 3937500
Forms!frmMainMenu!SubMenu!MiniMenu.Visible = False
'Remove last 1 digit from MyErrorMessage.
MyErrorMessage = Left(lblTitleCap, Len(lblTitleCap) - 1)
Forms!frmMainMenu!SubMenu!lblStatus.Caption = "Your Search for '" & Me.txtEntry & "' in the " & MyErrorMessage & " is Empty." _
& vbNewLine _
& "Please click HERE to return back to your Dashboard."
Forms!frmMainMenu!SubMenu!lblStatus.ForeColor = 16711680
Forms!frmMainMenu!SubMenu!lblStatus.FontUnderline = True
Forms!frmMainMenu!SubMenu!lblStatus.Visible = True
Else
Me.txtEntry.SetFocus
If Not IsNull(Len(Me.txtEntry)) Then
Forms!frmMainMenu!SubMenu!lblTitle.Caption = lblTitleCap & " - Search results for... '" & Me.txtEntry & "'"
Me.txtEntry.SetFocus
'CountFinanceRecords
Me.txtEntry.SelStart = Len(Me.txtEntry)
'
Else
Forms!frmMainMenu!SubMenu!lblTitle.Caption = lblTitleCap & "."
End If
End If
Exit_ErrorHandler:
Exit Sub
Err_ErrorHandler:
MsgBox err.Description, vbOKOnly + vbInformation, "TestDB"
Resume Exit_ErrorHandler
End Sub
