Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2005
    Posts
    4

    Unhappy Unanswered: Error ingenerating the report in access

    I am using this following simple code to generate report in Access(In the line between * signs), bu im getting error Saying :: ITEM NOT FOUND IN COLLECTION


    Private Sub cmd_report_Click()
    Dim sql As String
    sql = "select * from [Job_Flow]"

    ******* CurrentDb.QueryDefs.Delete "Job_Flow_query" *******
    CurrentDb.CreateQueryDef "Job_Flow_query", sql DoCmd.OpenReport "Job Report", acViewPreview


    whr "Job_Flow_query" is name of the query used for gfenerating the report
    "job Report" is name of the report

  2. #2
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Quote Originally Posted by syam1919
    I am using this following simple code to generate report in Access(In the line between * signs), bu im getting error Saying :: ITEM NOT FOUND IN COLLECTION


    Private Sub cmd_report_Click()
    Dim sql As String
    sql = "select * from [Job_Flow]"

    ******* CurrentDb.QueryDefs.Delete "Job_Flow_query" *******
    CurrentDb.CreateQueryDef "Job_Flow_query", sql DoCmd.OpenReport "Job Report", acViewPreview


    whr "Job_Flow_query" is name of the query used for gfenerating the report
    "job Report" is name of the report
    Some corrections:
    1. sql = "select * from " & [Job_Flow]

    Why don't you use DoCmd.OpenReport "Job_Flow_query",acViewPreview
    to open the report.

  3. #3
    Join Date
    Feb 2005
    Posts
    4
    I am getting error in the following line

    CurrentDb.QueryDefs.Delete "Job_Flow_query"

    the error I am getting is Item not found in this collection.

    I tried with using folliowing
    Dim qdf As QueryDefs

    eveni am facing the same problem

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    The proper way to delete an object in VBA is:

    DoCmd.DeleteObject acQuery, "QueryName"

    Although there are other ways to skin the cat, this is the most reliable.

    Also, a possible reason the object doesn't exist is because your syntax needs polishing. Try this:

    CurrentDb.CreateQueryDef ("QueryDefName","SQLString")

    without using the "sql" keyword. The word "sql" is a "reserved word," and might be confusing the program. Try "strSQL" instead as your string variable.

    Sam
    Last edited by Sam Landy; 02-22-05 at 13:34.

Posting Permissions

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