Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2015
    Posts
    2

    Unanswered: Writing Pass Through Queries in VBA #2

    Greetings,

    I am working with a function written by moderator Sinndho, and posted in an older thread HERE

    The problem I am having is, I am getting an error from the function.
    Error 3265 Item not found in collection from this line of code

    "If Not IsNull(dbs.QueryDefs(QueryName).Name)"

    I am calling this function from the code example on post #7 of the previous thread, entering in the SQL string and connection information pertinent to my situation. The SQL string was tested in the server management studio.

    I am not sure how to properly paste code into this message , so I hesitate to post the whole function in plain text.

    Anyway, if someone would be kind enough to look at the previous post and help me understand why I am getting this error.

    MS Access 2007
    MSSQL Server 2005

    Thank you

    Greg

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The purpose of this line is to determine whether a Querydef object with the same name as the one provided by the this argument (Optional ByVal QueryName As String) already exists in the CurrentDb database object. An alternate method would consist in querying the MSysObjects table, like this:
    Code:
    If DCount("*", "MSysObjects", "Name='" & QueryName & "' AND Type=5") > 0 Then DoCmd.DeleteObject acQuery, QueryName
    Have a nice day!

  3. #3
    Join Date
    Jan 2015
    Posts
    2
    Thank you for your reply.
    I have no errors now but also have no data displayed.

    I am not really new to this but I am missing something.

    I see the record source is being set to the query, but how do I show the result of the query.

    Thank you

    Greg

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    A query with the name "QueryName" (if you pass any, of course) should be present in the database after calling the function. You can use this query like any other query in the database (open it, open a recordset from it, assign the RecordSource of a form to it, assign the RowSource of a ListBox or ComboBox to it, create a new query from it, etc...). Notice that sometimes, you need to use the F5 key in the database window to refresh the display.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •