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

Question

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 ``` 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. ``` 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…

Accepted Answer

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.Field` and `Table2.Field` to 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

```
rst![DBUsers_Tbl.EMail]
```

instead of

```
rst!DBUsers_Tbl.EMail
```

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

Solution

Problem