What you need to do is to build the passthrough query SQL statement on the fly.
In the morning, I will try to find the video in which Richard shows how to do that.

Thanks Sami for the response! I did find the video for making the passthrough query SQL on the fly but will that work if you are writing the query from sql server? I need to figure that out.

I did sorta get another way to work using recordsets and I keep getting an error "The object you entered is not a valid Recordset property". This is so frustrating because I ran a test using msgbox and I can get the results I want when I type a letter into the searchbox. UGH. I need to step away for a little while.

Yes it will.

Show the error massage

I stripped out a lot of detail but the part where it fails is setting the recordset. That is the part I'm having a hard time grasping.

Dim conn, rs, MySQL
Dim txtSearch As String
Dim strFilter As String
Dim divID

Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
conn.Open TempVars("ADOConnectString")

    ' Get the text entered in SearchBox
txtSearch = Me.SearchBox.Text
    
    ' Construct the dynamic filter
If txtSearch <> "" Then
   strFilter = " tblEntity.EntityCode LIKE '%" & txtSearch & "%' "
End If

' Build the SQL query incorporting the dynamic filter
MySQL = "SELECT tblEntity.EntityID, tblEntity.EntityCode, " & _
"CONCAT(tblEntity.FirstName, ' ', tblEntity.LastName) AS DisplayName " & _
"FROM tblEntity " & _
"WHERE " & strFilter & _
"ORDER BY tblEntity.EntityCode;"
            
rs.Open MySQL, conn, 1, 3 ' 1 = keyset cursor to allow recordcount, 3 = allow modifications
    
Set Me.Recordset = rs

show the error massage please

Also, properly dim all your variables. For example,
dim rs as RecordSet.

I started over and figured it out by creating a new query on the fly and searching on that. Working fine :)

Only thing I hate about it is the cursor jump that happens when the searchTextBox gets focus and you have to find the length of the string and place the cursor at the end. Any idea how to get past that?

Sorry Todd, but I can't envision what you're talking about regarding the search text box. Can you please elaborate?