item not in collecion ms/access 2013 VBA

Posted on 2015-02-04
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:jirdeaid
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 34

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 50

Accepted Solution

Gustav Brock earned 500 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.

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  


Author Comment

ID: 40595929
is this NOT closed?
LVL 50

Expert Comment

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


Author Closing Comment

ID: 40596455
Thanks! Ithought it was closed...
LVL 50

Expert Comment

by:Gustav Brock
ID: 40596580
You are welcome!


Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

726 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