item not in collecion ms/access 2013 VBA

Posted on 2015-02-04
Medium Priority
Last Modified: 2016-02-10
Error "item is not in this collection"

I am trying to open a query and show on the screen using the following code:

Private Sub Form_Load()
    Dim coreDB As DAO.Database

    Dim T00_ControlTableSet As DAO.Recordset, _
        Q29Bx_PedidosTecnicoFiltroSet As DAO.Recordset
    Dim Q29Bx_PedidosTecnicoFiltroDef As QueryDef

    Dim NumeroPA As Integer, _
        NumeroTarefaPA As Integer
    Dim strUserID As String, _
        strCoreID As String, _
        strSetSQL As String
    Set coreDB = CurrentDb
    Set T00_ControlTableSet = coreDB.OpenRecordset("T00_ControlTable", dbOpenDynaset)
    ' tem que se fazer um filtro aos técnicos do COrE
    strCoreID = Forms![F91_LoggedUser]![CoreID]
    strUserID = Forms![F91_LoggedUser]![CurrentUserID]

    'Me.Fld_F12_UserID = Forms![F91_LoggedUser]![CurrentUserID]
    Call LogMe("F41a_PAdeTecnico all\ Load \ ", strCoreID & "\" & strUserID)
    strSetSQL = "SELECT Q29x_PedidosTecnico.Q29_PedidosTecnico, "
    strSetSQL = strSetSQL & "Q29x_PedidosTecnico.Q29_Tecnico , "
    strSetSQL = strSetSQL & "Q29x_PedidosTecnico.Q29_NomeEmpreendedor , "
    strSetSQL = strSetSQL & "Q29x_PedidosTecnico.Q29_Titulo, "
    strSetSQL = strSetSQL & "Q29x_PedidosTecnico.Q29_NomeEmpresa , "
    strSetSQL = strSetSQL & "Q29x_PedidosTecnico.Q29_Fechado, "
    strSetSQL = strSetSQL & "Q29x_PedidosTecnico.Q29_Cor , "
    strSetSQL = strSetSQL & "Q29x_PedidosTecnico.Q29_TotAlertas , "
    strSetSQL = strSetSQL & "Q29x_PedidosTecnico.Q29_PrevFecho, "
    strSetSQL = strSetSQL & "Q29x_PedidosTecnico.Q29_CorPrevFecho "
    strSetSQL = strSetSQL & "FROM Q29x_PedidosTecnico "
    strSetSQL = strSetSQL & "WHERE (((Q29x_PedidosTecnico.Q29_Tecnico)= " & "'" & strUserID & "' " & ")); "
    Set Q29x_PedidosTecnicoFiltroDef = coreDB.QueryDefs("Q29x_PedidosTecnicoFiltro")
    Q29Bx_PedidosTecnicoFiltroDef.SQL = strSetSQL
    Set Q29Bx_PedidosTecnicoFiltroSet = coreDB.OpenRecordset("Q29Bx_PedidosTecnicoFiltro", dbOpenDynaset)
    ' fechar o userID
    DoCmd.Close acForm, "F11_Utilizadores", acSaveYes
End Sub

However, I am consistently getting the above error, as shown on the screenshots:



can anyone tell what am I wrongdoing?
Question by:João serras-pereira
LVL 37

Expert Comment

ID: 40589099
Please use Option Explicit as default:

VBA IDE- Tools menu
Require Variable Declaration
This will result in an automatically added line code at the top of each VBA module or class. When it's missing, then add this line by hand. So that each file starts with it:

Option Compare Database
Option Explicit

Private Sub Form_Load()
     Dim coreDB As DAO.Database

Open in new window

Then you would get an error when compiling your code, cause this is an unkown variable: Q29x_PedidosTecnicoFiltroDef, because you defined it with an B as Dim Q29Bx_PedidosTecnicoFiltroDef As QueryDef.

btw, as you're already using line breaks, use it consequently and use table alias names:

strSetSQL = _
   "SELECT Q.Q29_PedidosTecnico, " & _
       "Q.Q29_Tecnico , "  & _
       "Q.Q29_NomeEmpreendedor , " & _
       "Q.Q29_Titulo, " & _
       "Q.Q29_NomeEmpresa , " & _
       "Q.Q29_Fechado, " & _
       "Q.Q29_Cor , " & _
       "Q.Q29_TotAlertas , " & _
       "Q.Q29_PrevFecho, " & _
       "Q.Q29_CorPrevFecho " & _
       "FROM Q29x_PedidosTecnico Q " & _
       "WHERE Q.Q29_Tecnico = '" & strUserID & "';"

Open in new window

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40589224
Quick Guess...
Make sure:
Itemactually exists and is spelled correctly...
LVL 53

Accepted Solution

Gustav Brock earned 2000 total points
ID: 40590522
You are mixing up names in a very confusing way with Qs for recordsets and interchanging x and Bx in:

Dim T00_ControlTableSet As DAO.Recordset, _
         Q29Bx_PedidosTecnicoFiltroSet As DAO.Recordset
Dim Q29Bx_PedidosTecnicoFiltroDef As QueryDef

so it is hard to follow what is new and what is existing objects.

You probably need these adjustments:

Set Q29Bx_PedidosTecnicoFiltroDef = coreDB.QueryDefs("Q29x_PedidosTecnicoFiltro")
Q29Bx_PedidosTecnicoFiltroDef.SQL = strSetSQL
Set Q29Bx_PedidosTecnicoFiltroSet = coreDB.OpenRecordset("Q29Bx_PedidosTecnicoFiltroDef", dbOpenDynaset)

And, of course, double-check that Q29x_PedidosTecnicoFiltro exists.

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.


Author Comment

by:João serras-pereira
ID: 40595929
is this NOT closed?
LVL 53

Expert Comment

by:Gustav Brock
ID: 40596078
That's up to you to do.


Author Closing Comment

by:João serras-pereira
ID: 40596455
Thanks! Ithought it was closed...
LVL 53

Expert Comment

by:Gustav Brock
ID: 40596580
You are welcome!


Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…

597 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question