Results 1 to 8 of 8
  1. #1
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051

    Error "item not found in this collection"

    I have a form where the user selects class and month from combo boxes. I was hoping the code below would create and run a query in VBA then show the results as a datasheet.



    It does this sometimes but more often produces the error "item not found in this collection" at

    Set oQuery = oDB.QueryDefs("Query1")

    I was wondering whether the query isn't actually there? Can anyone help and maybe explain how this process works as I have to run several of these queries.





    Code:
    strSQL = SQLSTR(Me.Combo_class.Value, Me.Combo_month.Value)
    Dim oDB As Database
    Dim oQuery As QueryDef
    Set oDB = CurrentDb
    
    Set oQuery = oDB.QueryDefs("Query1")
    oQuery.SQL = strSQL
    Set oQuery = Nothing
    Set oDB = Nothing
    
    DoCmd.OpenQuery "Query1", 0, acEdit
    Many thanks

  2. #2
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    What is the SQL of the query? It needs the full statement in order to run. "SELECT ...... FROM...."

  3. #3
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051

    Full SQL

    Quote Originally Posted by aytee111 View Post
    What is the SQL of the query? It needs the full statement in order to run. "SELECT ...... FROM...."
    Code:
    Public Function SQLSTR(gp As String, _
                            mnth As String) As String
     Yr = Left(gp, 1)
     mnth = Left(mnth, 3)
     Debug.Print (gp & " " & Yr & " " & mnth)
     
    string1 = "[Lower_School_Students].[First name], [Lower_School_Students].[Last name], [Lower_School_Students].[Mathematics :Group(s)], "
    string2 = "results.[" & Yr & "p1" & mnth & "], results.[" & Yr & "p2" & mnth & "], results.[" & Yr & "MA" & mnth & "], results.[" & Yr & "" & mnth & "RAW]  "
    SQLSTR = "SELECT " & string1 & string2 & " FROM [Lower_School_Students] LEFT JOIN results ON [Lower_School_Students].UPN = results.upn " _
            & "WHERE ((([Lower_School_Students].[Mathematics :Group(s)]) = " & Chr(34) & gp & Chr(34) & ")) ORDER BY [Lower_School_Students].[Mathematics :Group(s)],[Lower_School_Students].[Last name];"
        Debug.Print (SQLSTR)
        
    End Function
    I've tested this and it returns

    Code:
    SELECT [Lower_School_Students].[First name], [Lower_School_Students].[Last name], [Lower_School_Students].[Mathematics :Group(s)], results.[7p1Nov], results.[7p2Nov], results.[7MANov], results.[7NovRAW]   FROM [Lower_School_Students] LEFT JOIN results ON [Lower_School_Students].UPN = results.upn WHERE ((([Lower_School_Students].[Mathematics :Group(s)]) = "7jk/Mm4")) ORDER BY [Lower_School_Students].[Mathematics :Group(s)],[Lower_School_Students].[Last name];
    which produces the datasheet I need when I put it directly into an SQL

    My apologies if it reads a bit bad but this is still a bit new to me

  4. #4
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    The number one thing in naming conventions is never to use special characters in field names, Access likes to reserve those for itself to use. Your ":" and "()" may be the cause. Remove those and see if that helps. The error you are getting is saying that a field you are referencing is not on the table/query.

  5. #5
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Thanks aytee

    I've made the changes but still get the same error.

    Code:
    string1 = "[Lower_School_Students].[First name], [Lower_School_Students].[Last name], [Lower_School_Students].[Mathematics_Group], "
    string2 = "Nov_2016_results.[" & "p1" & "], nov_2016_results.[" & "p2" & "], nov_2016_results.[" & "MA" & "], nov_2016_results.[" & "" & "RAW]  "
    SQLSTR = "SELECT " & string1 & string2 & " FROM [Lower_School_Students] LEFT JOIN nov_2016_results ON [Lower_School_Students].UPN = nov_2016_results.upn " _
            & "WHERE ((([Lower_School_Students].[Mathematics_Group]) = " & Chr(34) & gp & Chr(34) & ")) ORDER BY [Lower_School_Students].[Mathematics_Group],[Lower_School_Students].[Last name];"

    The returned SQL seems fine.

    Code:
    SELECT [Lower_School_Students].[First name], [Lower_School_Students].[Last name], [Lower_School_Students].[Mathematics_Group], Nov_2016_results.[p1], nov_2016_results.[p2], nov_2016_results.[MA], nov_2016_results.[RAW]   FROM [Lower_School_Students] LEFT JOIN nov_2016_results ON [Lower_School_Students].UPN = nov_2016_results.upn WHERE ((([Lower_School_Students].[Mathematics_Group]) = "8jk/Mm3")) ORDER BY [Lower_School_Students].[Mathematics_Group],[Lower_School_Students].[Last name];

  6. #6
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    No argument on the naming thing, but I don't think that's the issue if the error is raised on this line
    Set oQuery = oDB.QueryDefs("Query1")

    If Query1 doesn't exist in the nav pane, you can't set it to the object you dim'd. Are you trying to create a query object using the sql returned from the function? If so, that is not how it's done.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Spot on micron. I found createquerydef and the whole thing fell into place perfectly. Thanks to you both for the advice. I'll post later on.

  8. #8
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Glad you were able to find the solution. Was too lazy to ramble on about how to create a qdef in case I was wrong about the intent.
    Your method is likely going to be to create and save a query def in code. In that case, you might want to know that you can create a temporary qdef by supplying an empty string for the name. Pretty sure this is destroyed when the procedure terminates (I know you will not find it in the nav pane using that technique). It allows you to recreate on subsequent executions of the procedure without having to deal with code to over-write an existing instance of the already created and saved query.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 10
    Last Post: 11-26-2018, 02:25 PM
  2. Run-Time Error '3265': Item Not Found In This Collection
    By Voodeux2014 in forum Programming
    Replies: 3
    Last Post: 01-29-2016, 09:04 AM
  3. item not found in this collection
    By rockell333 in forum Queries
    Replies: 1
    Last Post: 09-24-2015, 03:20 PM
  4. Replies: 4
    Last Post: 09-10-2015, 08:22 AM
  5. Replies: 2
    Last Post: 11-12-2013, 07:06 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums