title: Filtering Records in a Form with an Inner Join Query in Access
body: <p>My Access 2013 database has two main tables; Customers and Sites. The Sites table has a foreign key pointing to a Customer record. I am building a search box into a Sites Form with an unbound text box and a command button. Once the button is pressed, it should filter the records based on the search criteria and VBA code. I need to be able to search by the Customer name, located in a separate table linked through a foreign key. So far, this is the code I have for the button:</p>
  
  <pre><code>Private Sub cmdSearch_Click()
  
  Dim strSQL As String
  
  strSQL = "SELECT * FROM tblMainSites " &amp; _
              "INNER JOIN tblMainCustomers " &amp; _
              "ON tblMainSites.Customer=tblMainCustomers.CustomerID " &amp; _
              "WHERE tblMainCustomers.Customer LIKE '*" &amp; Me.txtSearch &amp; "*'"
  
  If IsNull(Me.txtSearch) Then
      Me.Filter = ""
      Me.FilterOn = False
  Else
      Debug.Print strSQL
      Me.Filter = strSQL
      Me.FilterOn = True
  End If
  
  End Sub
  </code></pre>
  
  <p>The problem is that every time I try to filter the records, I get the following:</p>
  
  <p>"Run Time Error 3075, Syntax Error in Query Expression"</p>
  
  <p>Using the Debug Print, it looks like the query expression I wrote is fine, and looks just like a working query I built within Access:</p>
  
  <pre><code>SELECT * FROM tblMainSites INNER JOIN tblMainCustomers ON tblMainSites.Customer=tblMainCustomers.CustomerID WHERE tblMainCustomers.Customer LIKE '*Affinity*'
  </code></pre>
  
  <p>My question is this: Am I using the Me.Filter method correctly? I cannot find any examples using a full SQL string. I've also tried using the DoCmd.OpenForm method with OpenArgs as the string, also to no avail. </p>
  
  <p>Asking another way, is there a way to filter records in an Access form using a JOIN query?</p>
  
  <p>My goal is to be able to search for a record in the Sites table while referencing the Customer name in the Customers table, since searching for the customer name in the Sites table fails due to it being the foreign key pointing to the Customers table.</p>
  
  <p>Thanks in advance for your assistance!</p>