[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

item not in collecion ms/access 2013 VBA

Posted on 2015-02-04
7
Medium Priority
?
211 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:João serras-pereira
[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 35

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 52

Accepted Solution

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

/gustav
0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 

Author Comment

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

Expert Comment

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

/gustav
0
 

Author Closing Comment

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

Expert Comment

by:Gustav Brock
ID: 40596580
You are welcome!

/gustav
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
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 …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

656 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