When I open the recordset using Like '%[']%' the error reads: syntax error
in query expression. I am actually trying to search for a single quote not a
double, I can search for a double no problem. I think the problem exists
that the single quote is being read as the end of the string even though it
is in the middle of the braces or anything else I have tried. My original
plan was to be able to enter a character or set of characters into a textbox
and search a field returning all the results to a table. I am opening an ADO
recordset based on my SQL statement then inserting one line at a time the
recordset information. (if I insert all at once my process doesn't work for
date or number fields, a different issue though).
So far these suggestions are directed as I had hoped, however no success. I
looked in Access and AccessVBA help. Then google searched. Then used
CHARINDEX returning an undefined function error. It seems it is only for SQL
server or at least not for AccessVBA although I could be mistaken as I am a
rookie(obvious).

Here is the meat of my code.

Sub test()
Dim cnn As ADODB.Connection
Dim rstTbls As ADODB.Recordset, rstCurTbl As ADODB.Recordset
Dim strSQL As String, strSQL2 As String, strCombo As String, strBox As
String
Dim strTbl As String, strFld As String

strBox = [Forms]![Table Search]![SearchBox]
strCombo = [Forms]![Table Search]![ComboField]

Set cnn = CurrentProject.Connection
Set rstTbls = New ADODB.Recordset
Set rstCurTbl = New ADODB.Recordset

strSQL = "SELECT [Tbl Fields].* FROM [Tbl Fields] WHERE [Tbl Fields].[Table]
Like 'source_' & '%' AND [Field] like '" & strCombo & "' ORDER BY [Table]"

rstTbls.Open strSQL, cnn, 0
Do Until rstTbls.EOF
   strTbl = rstTbls!Table
   strFld = rstTbls!Field
   strSQL2 = "SELECT " & strFld & " FROM " & strTbl & " WHERE strFld & "
Like '%' & '" & strBox & "' & '%'"

   rstCurTbl.Open strSQL2, cnn, 0
   Do Until rstCurTbl.EOF
       DoCmd.RunSQL "INSERT INTO [Tbl Search Report]([Table], [Field],
[Data]) SELECT '" & strTbl & "', '" & strFld & "', '" & rstCurTbl(0) & "'"

       rstCurTbl.MoveNext
   Loop
   rstCurTbl.Close
   rstTbls.MoveNext
Loop
Set rstCurTbl = Nothing
Set rstTbls = Nothing
Set cnn = Nothing
End Sub

If data in the field contains a single quote this is the error:
syntax error (missing operator) in query expression "da'ta"

I have fixed this by inserting all records at once without a second
recordset. The single quote search still eludes me.
Any more thought on this and/or any new suggestions would be greatly
appreciated.

Also what is the purpose of N ?

SELECT Eggs FROM Chickens
WHERE CHARINDEX(N'"', Eggs)>0