Access 2007 VBA SQL 选择错误“在此集合中找不到项目”

Access 2007 VBA SQL Select Error "Item not found in this collection"

提问by user3258917

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

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

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

and

和

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

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.

完整代码发布在下面。这是我第一次在 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, "[email protected]")
StrClsEM = Nz(rst!DBUsers_Tbl_1.EMail, "[email protected]")

'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:\mortgage\prodcenters\LOAN 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 , , , "[email protected]", , , "New Lock: " & Borrower & ": " & 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 , , , "[email protected]", , , "Term Change" & ": " & Borrower & ": " & 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, "[email protected]")
    ProcEmail = Nz(strProcEM, "[email protected]")
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

回答by Gord Thompson

If we create a query in Access that pulls two fields with the same name from two different tables then Access will name the resulting columns Table1.Fieldand Table2.Fieldto disambiguate. When referring to those fields in the Recordset using "bang (!) notation" you must put square brackets around the entire field name. In your case, for example, you would need to use

如果我们在 Access 中创建一个查询，从两个不同的表中提取具有相同名称的两个字段，则 Access 将命名结果列Table1.Field并Table2.Field消除歧义。当使用“bang ( !) 表示法”引用 Recordset 中的那些字段时，您必须在整个字段名称周围放置方括号。例如，在您的情况下，您需要使用

rst![DBUsers_Tbl.EMail]

instead of

代替

rst!DBUsers_Tbl.EMail