访问 2007 VBA SQL 选择错误"Item not found in this collection"

Question

修复错误后返回，现在是一个新错误。我已经在Access 2007中使用VBA创建了一个SQL语句，并且我会收到错误"在此集合中找到的项目"，该字段确实存在于表格中，并且拼写正确。我什至将SQL语句复制到一个查询中，并且起作用。我假设错误是代码的这一部分

Dim strCMCID As Long  ' (it's a Key field AutoNumber) 
strCMCID = Me!CMCID_Txt 

和

"WHERE Commitments_Tbl.CMCID = " & strCMCID & "" 

完整的代码下面发布。这是我第一次在使用VBA中使用SQL语句。我要做的是获取SQL语句，以从当前表格中从特定记录中获取两个电子邮件地址。

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 " & _
"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 " & _
"WHERE Commitments_Tbl.CMCID = " & strCMCID & ""
Set dbs = CurrentDb
Set rst = CurrentDb.OpenRecordset(strSQL)
strMWS = rst!MWStatus
strProcEM = Nz(rst!DBUsers_Tbl.EMail, "[e-mail]")
StrClsEM = Nz(rst!DBUsers_Tbl_1.EMail, "[e-mail]")
'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 = "" & Borrower & " " & LNumber & ""
TheFile = "P:mortgageprodcentersLOAN ITEMS (SW)_RateLocks_and_Changes" & TheName & ".rtf"
DoCmd.OutputTo acOutputReport, "Confirmation_Email2", acFormatRTF, TheFile, False
    If Nz(Me!InvestorID_Cbo, "Blank") = "Blank" Then
      DoCmd.SendObject , , , "[e-mail]", , , "New Lock: " & Borrower & ": " & LNumber, "A rate lock confirmation has been saved down to the server at P:mortgageprodcentersLOAN 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 , , , "[e-mail]", , , "Term Change" & ": " & Borrower & ": " & LNumber, "A rate lock confirmation has been saved down to the server at P:mortgageprodcentersLOAN 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, "[e-mail]")
    ProcEmail = Nz(strProcEM, "[e-mail]")
If Me!RateExpDate_Txt <= Date + 8 Then
    Caution = "STOP Terms Finalized:"
ElseIf strMWS = "Closing" And Me!RateExpDate_Txt >= Date + 8 Then
    Caution = "STOP:"
Else
    Caution = ""
End If
If Me!InvestorID_Cbo = "" Then
    DoCmd.SendObject acSendReport, "Confirmation_Email", "SnapshotFormat(*.snp)", LOEmail, ProcEmail & ";" & ClsEmail, , Caution & "New Lock: " & Borrower & ": " & LNumber, , True
Else
    DoCmd.SendObject acSendReport, "Confirmation_Email", "SnapshotFormat(*.snp)", LOEmail, ProcEmail & ";" & ClsEmail, , Caution & "  " & "Term Change" & ": " & Borrower & ": " & 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

Answer 1

如果我们在访问中创建一个查询，从两个不同表中提取两个具有相同名称的字段，则访问将命名结果列Table1.Field和Table2.Field以删除基因。当使用" BANG（!）符号"中的记录集中参考这些字段时，您必须在整个字段名称周围放置方括号。例如，在您的情况下，您需要使用

rst![DBUsers_Tbl.EMail]

而不是

rst!DBUsers_Tbl.EMail