Solved

item not in collecion ms/access 2013 VBA

Posted on 2015-02-04
7
197 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
7 Comments
 
LVL 33

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 49

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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

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

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 49

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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.

770 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