Access Compile error: Type mismatch on SQL Recordset Query

Question

I have an SQL query for a record set.
If I comment the query out, I do not receive the type mismatch error, so I'm relatively confident it is in this query.
The query works in the Query window in Access (without the quotes for getting it into VBA).

I get a Compile error: Type mismatch on this. It always goes to the last '&'.

I intend to move this to a string variable in the end, but I want to get it working first.

I can address the type mismatch by making the change noted below. However, now I get a 424 object required error.
If I output this to a debug window and paste the output into an SQL query window in Access, I get the result I expect.

Dim rs As DAO.Recordset

Dim FileNumber As String

FileNumber = Me.txtFileNumber

Set rs = "SELECT tblParentRecord.FileNumber, tblChildRecord.CombinedName " _

& " FROM tblParentRecord INNER JOIN tblChildRecord ON tblParentRecord.FileNumber = tblChildRecord.FileNumber " _

Added: & " WHERE (((tblParentRecord.FileNumber)= " & """" & Me.FileNumber & """" & ")) "

Removed:& " WHERE (((tblParentRecord.FileNumber) LIKE'" & FileNumber & "*" & "'))')

Answer 1

Try,

Dim rs As Recordset
Dim db As Database
Dim FileNumber As String
Set db = CurrentDb



FileNumber = Me.txtFileNumber

strSql = "SELECT tblParentRecord.FileNumber, tblChildRecord.CombinedName " _
  & " FROM tblParentRecord INNER JOIN tblChildRecord ON tblParentRecord.FileNumber = tblChildRecord.FileNumber " _
  & " WHERE tblParentRecord.FileNumber Like '" & FileNumber & "' "

Set rs = db.OpenRecordset(strSql)