title: Access 2007 VBA SQL Select Error "Item not found in this collection"
body: <p>Returning after fixing errors and now a new one. I have created an SQL Statement using VBA in Access 2007 and I am getting the error "Item not found in this collection" The fields do exist in the tables and are spelled correctly. I even copied the SQL statement into a query and it worked. I'm assuming the error is with this part of the code</p>
  
  <pre class="lang-vbs prettyprint-override"><code>Dim strCMCID As Long  ' (it's a Key field AutoNumber) 
  strCMCID = Me!CMCID_Txt 
  </code></pre>
  
  <p>and </p>
  
  <pre class="lang-vbs prettyprint-override"><code>"WHERE Commitments_Tbl.CMCID = " &amp; strCMCID &amp; "" 
  </code></pre>
  
  <p>Full code posted below. This is my first time putting an SQL Statement in using VBA. What I am trying to do is get the SQL Statement to pull two email addresses from a specific record from the current Form.       </p>
  
  <pre class="lang-vbs prettyprint-override"><code>Public Sub SendConfirm()
  On Error GoTo Err_SendConfirm_Click
  
  Dim Borrower As String, LOEmail As String, ProcEmail As String, ClsEmail As String, Caution As String, LNumber As Long, TheFile As String, TheName As String
  
  'SQL Statement to get Processor and Closer email
  Dim dbs As DAO.Database
  Dim rst As DAO.Recordset
  Dim strSQL As String
  Dim strCMCID As Long  'AutoNumber
  Dim strMWS As String
  Dim strProcEM As String
  Dim StrClsEM As String
  
  strCMCID = Me!CMCID_Txt  'AutoNumber
  strSQL = "SELECT Commitments_Tbl.CMCID, Status_Tbl.MWStatus, DBUsers_Tbl.EMail, DBUsers_Tbl_1.EMail " &amp; _
  "FROM ((Commitments_Tbl LEFT JOIN Status_Tbl ON Commitments_Tbl.LoanNumber = Status_Tbl.LoanNumber) LEFT JOIN DBUsers_Tbl AS DBUsers_Tbl_1 ON Status_Tbl.Processor = DBUsers_Tbl_1.MWName) LEFT JOIN DBUsers_Tbl ON Status_Tbl.Closer = DBUsers_Tbl.MWName " &amp; _
  "WHERE Commitments_Tbl.CMCID = " &amp; strCMCID &amp; ""
  
  Set dbs = CurrentDb
  Set rst = CurrentDb.OpenRecordset(strSQL)
  strMWS = rst!MWStatus
  strProcEM = Nz(rst!DBUsers_Tbl.EMail, "John.Doe@CWork.com")
  StrClsEM = Nz(rst!DBUsers_Tbl_1.EMail, "John.Doe@Work.com")
  
  'Message Box
  Dim Msg, Style, Title, Response
  
  LOEmail = Me!OrigID_Cbo.Column(3)
  Borrower = Me!BorrNameL_Txt
  LNumber = Nz(Me!LoanNumber_Txt, 0)
  
  Msg = "Do you want to send an e-mail to Set_up?"
  Style = vbYesNo
  Title = "Cancel Set-Up E-Mail"
  Response = MsgBox(Msg, Style, Title)
  If Response = vbYes Then
      GoTo line3
  Else
      GoTo line4
  End If
  
  line3:
  TheName = "" &amp; Borrower &amp; " " &amp; LNumber &amp; ""
  TheFile = "P:\mortgage\prodcenters\LOAN ITEMS (SW)\_RateLocks_and_Changes\" &amp; TheName &amp; ".rtf"
  
  DoCmd.OutputTo acOutputReport, "Confirmation_Email2", acFormatRTF, TheFile, False
  
      If Nz(Me!InvestorID_Cbo, "Blank") = "Blank" Then
        DoCmd.SendObject , , , "CommerceMortgage@CommerceBank.com", , , "New Lock: " &amp; Borrower &amp; ": " &amp; LNumber, "A rate lock confirmation has been saved down to the server at P:\mortgage\prodcenters\LOAN ITEMS (SW)\_RateLocks_and_Changes as a word document with the same name and loan number as that is the subject line of this email. Please upload it into the GDR.", -1
      Else
        DoCmd.SendObject , , , "CommerceMortgage@CommerceBank.com", , , "Term Change" &amp; ": " &amp; Borrower &amp; ": " &amp; LNumber, "A rate lock confirmation has been saved down to the server at P:\mortgage\prodcenters\LOAN ITEMS (SW)\_RateLocks_and_Changes as a word document with the same name and loan number as that is the subject line of this email. Please upload it into the GDR.", True
      End If
  
  line4:
      ClsEmail = Nz(StrClsEM, "John.Doe@Work.com")
      ProcEmail = Nz(strProcEM, "John.Doe@Work.com")
  If Me!RateExpDate_Txt &lt;= Date + 8 Then
      Caution = "STOP Terms Finalized:"
  ElseIf strMWS = "Closing" And Me!RateExpDate_Txt &gt;= Date + 8 Then
      Caution = "STOP:"
  Else
      Caution = ""
  End If
  If Me!InvestorID_Cbo = "" Then
      DoCmd.SendObject acSendReport, "Confirmation_Email", "SnapshotFormat(*.snp)", LOEmail, ProcEmail &amp; ";" &amp; ClsEmail, , Caution &amp; "New Lock: " &amp; Borrower &amp; ": " &amp; LNumber, , True
  Else
      DoCmd.SendObject acSendReport, "Confirmation_Email", "SnapshotFormat(*.snp)", LOEmail, ProcEmail &amp; ";" &amp; ClsEmail, , Caution &amp; "  " &amp; "Term Change" &amp; ": " &amp; Borrower &amp; ": " &amp; LNumber, , True
  End If
  rst.Close
  Set rst = Nothing
  Set dbs = Nothing
  
  Exit_SendConfirm_Click:
      Exit Sub
  
  Err_SendConfirm_Click:
      MsgBox Err.Description
      Resume Exit_SendConfirm_Click
  
  End Sub
  </code></pre>