title: MS Access syntax error when updating on join
body: <p>I am receiving </p>
  
  <blockquote>
    <p>Run-time error '3075' - "Syntax error (missing operator) in query expression 'ct.ContainerID FROM tblCrossarm AS mt INNER JOIN tblContainerTemp AS ct ON mt.SerialNumber = ct.SerialNumber</p>
  </blockquote>
  
  <p>At the time of the failure, the strSQL variable looks like: </p>
  
  <pre><code>UPDATE mt SET mt.ContainerID = ct.ContainerID FROM tblCrossarm AS mt INNER JOIN tblContainerTemp AS ct ON mt.SerialNumber = ct.SerialNumber
  </code></pre>
  
  <p><strong>Code</strong></p>
  
  <pre><code>Private Sub btnSave_Click()
      Dim dbs As DAO.Database: Set dbs = CurrentDb
      Dim i As Integer
      Dim strSQL As String
      Dim arrMaterialType() As String
      Dim rstContainerTemp As DAO.Recordset
      Dim rstMaterialType As DAO.Recordset
      Dim strContainerID As Variant: strContainerID = Null
  
      If IsNull(cboContainerID.Column(0)) Or cboContainerID.Column(0) = "" Then
          If MsgBox("You have not selected a Container ID." &amp; vbCr &amp; vbLf &amp; "Press OK to Delete the Container IDs of all materials in the Container Table or press cancel to abort.", vbOKCancel, "Delete Container IDs?") = vbCancel Then
              Exit Sub
          End If
      Else
          strContainerID = Chr$(39) &amp; cboContainerID.Column(0) &amp; Chr$(39)
      End If
  
  
      Set rstMaterialType = dbs.OpenRecordset("qryDistinctMaterialType")
      Set rstContainerTemp = dbs.OpenRecordset("tblContainerTemp")
  
      If Not (rstContainerTemp.EOF And rstContainerTemp.BOF) And Not (rstMaterialType.EOF And rstMaterialType.EOF) Then
  
          rstContainerTemp.MoveFirst
          rstMaterialType.MoveFirst
  
          Do Until rstMaterialType.EOF = True
              Do Until rstContainerTemp.EOF = True
  
                  If rstMaterialType.Fields(0) = rstContainerTemp!MaterialType Then
  '                    rstContainerTemp.Edit
  '                    rstContainerTemp!ContainerID = strContainerID
  '                    rstContainerTemp.Update
                      strSQL = "UPDATE tblContainerTemp AS ct SET ct.ContainerID = " &amp; strContainerID &amp; " WHERE ct.SerialNumber = " &amp; Chr$(39) &amp; rstContainerTemp!SerialNumber &amp; Chr$(39) &amp; ";"
                      dbs.Execute strSQL, dbFailOnError
  
                  End If
  
                  rstContainerTemp.MoveNext
  
              Loop
  
              rstMaterialType.MoveNext
          Loop
  
      End If
  
  
      strSQL = "UPDATE mt SET mt.ContainerID = ct.ContainerID FROM " &amp; cboMaterialType.Column(0) &amp; " AS mt INNER JOIN tblContainerTemp AS ct ON mt.SerialNumber = ct.SerialNumber;"
      dbs.Execute strSQL, dbFailOnError
  
      dbs.Close
      Set dbs = Nothing
  
  End Sub
  </code></pre>