Solved

item not in collecion ms/access 2013 VBA

Posted on 2015-02-04
7
205 Views
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)
    Me.Recalc
    Me.Refresh
   
 
    ' fechar o userID
    DoCmd.Close acForm, "F11_Utilizadores", acSaveYes
   
End Sub

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

Screen-Shot-2015-02-04-at-18.59.48.png

Screen-Shot-2015-02-04-at-18.59.58.png

can anyone tell what am I wrongdoing?
0
Comment
Question by:jirdeaid
[X]
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
7 Comments
 
LVL 34

Expert Comment

by:ste5an
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

0
 
LVL 74

Expert Comment

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

Accepted Solution

by:
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.

/gustav
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:jirdeaid
ID: 40595929
is this NOT closed?
0
 
LVL 50

Expert Comment

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

/gustav
0
 

Author Closing Comment

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

Expert Comment

by:Gustav Brock
ID: 40596580
You are welcome!

/gustav
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

738 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