Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report nodata error (Access 2003 on XP)

    Hi everybody:

    I have a custom report with a very complex query as record source. I change the SQL according to user selections on the form and then set the qdef.sql = to the sql in code. That way, the report's record source = the same qdef; only its sql changes.

    This has worked well for other reports, and even for the same report in the past, but now it is triggering a nodata event, even though the sql / qdef returns records. I've tested the sql, and it is fine.

    The odd thing about it, is that if after the report cancels I then preview it from the database window, it prints fine without the nodata event being triggered.

    I've tried pausing code execution to give Access time to do its under the hood stuff, but no go. I have also tried decompile / compact / compile / compact, but no fix . I'm guessing that either the report is damaged from being repeatedly edited or that Jet is taking too long to digest the qdef's new sql. I hate to have to re-build the report . . .

    Do I need a service pack? Any other ideas?

    Thanks!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report nodata error (Access 2003 on XP)

    What happens if you try the following (in a copy of the database):
    - In design view, clear the record source of the report.
    - Set the record source of the report to the appropriate SQL string in the On Open event of the report (you can pass it from the form to the report in the OpenArgs argument of DoCmd.OpenReport, or you can use a global variable).

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report nodata error (Access 2003 on XP)

    Thanks for the idea. I have found that OpenArgs works only on one report in the entire database. I tried making a backup copy of the report first, and got "Name conflicts with existing module, project or object library" (which it doesn't!). So, I am suspecting some sort of damage.

    When I tried setting the record source to the SQL, using a global variable, I got error 2491: "The action or method is invalid because the form or report isn't bound to a table or query." Setting the record source to the querydef on the report's open event resulted in the same error.

    Have you done this before?

    Thanks for your help.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report nodata error (Access 2003 on XP)

    The RecordSource property of a report is a string that can be the name of a table, the name of a query, or a SQL string, not a querydef. It is very well possible to set the record source in code in the On Open event of the report, I use that in several of my databases.

    I'd say that your database is at least partially corrupt. Things to try:
    1) Tools | Database Utilities | Compact and Repair Database.
    2) Create a new blank database and import as many database objects from the problem database as possible.
    You'll have to set the startup options and non-standard VBA references in the new database.

  5. #5
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report nodata error (Access 2003 on XP)

    Thanks. I already tried compact and repair. I'll try re-building the report, and if that doesn't work, re-building the app, as you suggest.

  6. #6
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report nodata error (Access 2003 on XP)

    Fixed it.
    First, re-wrote the SQL to simplify it by eliminating a complex subquery, instead incorporating its tables into the main query. Saved it as a new query.
    Then, used the Wizard to create a new report based on the new query. This eliminated whatever damage was in the original report.
    Works fine now.

    Thanks for your help!

Posting Permissions

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